Bad INSERT in Alfresco with PostgreSQL

[akuasleep]

When Alfresco finds a database error like that, it will keep retrying the transaction many times, until it succeeds. That occurs when a thread gets a new id for a piece of information, then tries to insert it in a table, and, if it fails, then it tries again with the next id sequence. I think that it may be possible that we are observing multiple threads getting the same id at the same time, and when trying to update the table, and failing, and keep attempting again.

I just reviewed very quickly one of the logs, and I saw many messages like the following:

2015-11-10 17:10:04 CET [unknown] alfrescousr 126.177.150.12 25925 ERROR: duplicate key value violates unique constraint "idx_alf_conturl_cr"

Our DB administrator noticed that some kind of INSERT make trouble. INSERT ends but is still IDLE IN TRANSACTIONS. And numbers of this INSERTS are growing (

insert into alf_node_properties
                 |         (
                 |             node_id, qname_id, locale_id, list_index,
                 |             actual_type_n, persisted_type_n,
                 |             boolean_value, long_value, float_value, double_value, string_value, serializable_value
                 |         )
                 |         values
                 |         (
                 |             $1, $2, $3, $4,
                 |             $5, $6,
                 |             $7, $8, $9, $10, $11, $12
                 |         )

Please help.

[mrogers]

The retrying stuff is normal for alfresco.  

You will need to provide more details to tie down whether there is a specific problem.    That said the content URLs should be unique UUIDs.

[akuasleep]

Seven INSTERT sesssions do operation in the same time. This is INSERT:

insert into alf_node_properties
                 |         (
                 |             node_id, qname_id, locale_id, list_index,
                 |             actual_type_n, persisted_type_n,
                 |             boolean_value, long_value, float_value, double_value, string_value, serializable_value
                 |         )
                 |         values
                 |         (
                 |             $1, $2, $3, $4,
                 |             $5, $6,
                 |             $7, $8, $9, $10, $11, $12
                 |         )

In db its look like:

-[ RECORD 43 ]
datid            | 16432

datname          | alfresco

pid              | 24128

usesysid         | 27270

usename          | alfrescousr

application_name |

client_addr      | 126.177.150.12

client_hostname  |

client_port      | 42312

backend_start    | 2016-01-22 11:10:59.98938+01

xact_start       | 2016-01-22 11:11:12.086708+01

query_start      | 2016-01-22 11:11:12.930515+01

state_change     | 2016-01-22 11:11:12.931278+01

waiting          | f

state            | idle in transaction

query            | insert into alf_node_properties

                 |         (

                 |             node_id, qname_id, locale_id, list_index,

                 |             actual_type_n, persisted_type_n,

                 |             boolean_value, long_value, float_value, double_value, string_value, serializable_value

                 |         )

                 |         values

                 |         (

                 |             $1, $2, $3, $4,

                 |             $5, $6,

                 |             $7, $8, $9, $10, $11, $12

I know that  retrying stuff is normal for alfresco and in DB configuration file I can disable to record this error but… when it happens the DB connections are grow fast. 
So what do You need to know to help me?

[akuasleep]

Some SELECT in DB start on: 12:50 and go to query on: 12:53 and thats it. This SELECT ends but session where IDLE IN TRANSACTION. This situation caused snowball effect and number of connections to DB has started to grow causing LOCKs in DB.

Is it possible to resolved this problem if we add: db.pool.evict.idle.min parameter in Alfresco configuration file? Is this safe?