topic Bad INSERT in Alfresco with PostgreSQL in Alfresco Archive

topic Bad INSERT in Alfresco with PostgreSQL in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281370#M234500 <HTML><HEAD></HEAD><BODY><SPAN>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.</SPAN><BR /><BR /><SPAN> I just reviewed very quickly one of the logs, and I saw many messages like the following:</SPAN><BR /><BR /><SPAN>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"</SPAN><BR /><BR /><SPAN>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 <img id="smileysad" class="emoticon emoticon-smileysad" src="https://connect.hyland.com/i/smilies/16x16_smiley-sad.png" alt="Smiley Sad" title="Smiley Sad" />(</SPAN><BR /><BR /><SPAN>insert into alf_node_properties</SPAN><BR /><SPAN>                 |         (</SPAN><BR /><SPAN>                 |             node_id, qname_id, locale_id, list_index,</SPAN><BR /><SPAN>                 |             actual_type_n, persisted_type_n,</SPAN><BR /><SPAN>                 |             boolean_value, long_value, float_value, double_value, string_value, serializable_value</SPAN><BR /><SPAN>                 |         )</SPAN><BR /><SPAN>                 |         values</SPAN><BR /><SPAN>                 |         (</SPAN><BR /><SPAN>                 |             $1, $2, $3, $4,</SPAN><BR /><SPAN>                 |             $5, $6,</SPAN><BR /><SPAN>                 |             $7, $8, $9, $10, $11, $12</SPAN><BR /><SPAN>                 |         )</SPAN><BR /><BR /><SPAN>Please help. </SPAN><BR /></BODY></HTML> Fri, 22 Jan 2016 13:43:07 GMT akuasleep 2016-01-22T13:43:07Z Bad INSERT in Alfresco with PostgreSQL https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281370#M234500 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 i Fri, 22 Jan 2016 13:43:07 GMT https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281370#M234500 akuasleep 2016-01-22T13:43:07Z Re: Bad INSERT in Alfresco with PostgreSQL https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281371#M234501 <HTML><HEAD></HEAD><BODY><SPAN>The retrying stuff is normal for alfresco.   </SPAN><BR /><BR /><SPAN>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.</SPAN></BODY></HTML> Fri, 22 Jan 2016 14:55:07 GMT https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281371#M234501 mrogers 2016-01-22T14:55:07Z Re: Bad INSERT in Alfresco with PostgreSQL https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281372#M234502 <HTML><HEAD></HEAD><BODY><SPAN>Seven INSTERT sesssions do operation in the same time. This is INSERT:</SPAN><BR /><BR /><SPAN>insert into alf_node_properties</SPAN><BR /><SPAN>                 |         (</SPAN><BR /><SPAN>                 |             node_id, qname_id, locale_id, list_index,</SPAN><BR /><SPAN>                 |             actual_type_n, persisted_type_n,</SPAN><BR /><SPAN>                 |             boolean_value, long_value, float_value, double_value, string_value, serializable_value</SPAN><BR /><SPAN>                 |         )</SPAN><BR /><SPAN>                 |         values</SPAN><BR /><SPAN>                 |         (</SPAN><BR /><SPAN>                 |             $1, $2, $3, $4,</SPAN><BR /><SPAN>                 |             $5, $6,</SPAN><BR /><SPAN>                 |             $7, $8, $9, $10, $11, $12</SPAN><BR /><SPAN>                 |         )</SPAN><BR /><BR /><SPAN>In db its look like:</SPAN><BR /><BR /><SPAN>-[ RECORD 43 ] </SPAN><BR /><SPAN>datid            | 16432</SPAN><BR /><BR /><SPAN>datname          | alfresco</SPAN><BR /><BR /><SPAN>pid              | 24128</SPAN><BR /><BR /><SPAN>usesysid         | 27270</SPAN><BR /><BR /><SPAN>usename          | alfrescousr</SPAN><BR /><BR /><SPAN>application_name |</SPAN><BR /><BR /><SPAN>client_addr      | 126.177.150.12</SPAN><BR /><BR /><SPAN>client_hostname  |</SPAN><BR /><BR /><SPAN>client_port      | 42312</SPAN><BR /><BR /><SPAN>backend_start    | 2016-01-22 11:10:59.98938+01</SPAN><BR /><BR /><SPAN>xact_start       | 2016-01-22 11:11:12.086708+01</SPAN><BR /><BR /><SPAN>query_start      | 2016-01-22 11:11:12.930515+01</SPAN><BR /><BR /><SPAN>state_change     | 2016-01-22 11:11:12.931278+01</SPAN><BR /><BR /><SPAN>waiting          | f</SPAN><BR /><BR /><SPAN>state            | idle in transaction</SPAN><BR /><BR /><SPAN>query            | insert into alf_node_properties</SPAN><BR /><BR /><SPAN>                 |         (</SPAN><BR /><BR /><SPAN>                 |             node_id, qname_id, locale_id, list_index,</SPAN><BR /><BR /><SPAN>                 |             actual_type_n, persisted_type_n,</SPAN><BR /><BR /><SPAN>                 |             boolean_value, long_value, float_value, double_value, string_value, serializable_value</SPAN><BR /><BR /><SPAN>                 |         )</SPAN><BR /><BR /><SPAN>                 |         values</SPAN><BR /><BR /><SPAN>                 |         (</SPAN><BR /><BR /><SPAN>                 |             $1, $2, $3, $4,</SPAN><BR /><BR /><SPAN>                 |             $5, $6,</SPAN><BR /><BR /><SPAN>                 |             $7, $8, $9, $10, $11, $12 </SPAN><BR /><BR /><SPAN>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.  </SPAN><BR /><SPAN>So what do You need to know to help me? </SPAN><BR /></BODY></HTML> Mon, 25 Jan 2016 09:04:57 GMT https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281372#M234502 akuasleep 2016-01-25T09:04:57Z Re: Bad INSERT in Alfresco with PostgreSQL https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281373#M234503 <HTML><HEAD></HEAD><BODY><SPAN>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.</SPAN><BR /><BR /><SPAN>Is it possible to resolved this problem if we add: db.pool.evict.idle.min parameter in Alfresco configuration file? Is this safe?</SPAN><BR /></BODY></HTML> Thu, 28 Jan 2016 09:47:29 GMT https://connect.hyland.com/t5/alfresco-archive/bad-insert-in-alfresco-with-postgresql/m-p/281373#M234503 akuasleep 2016-01-28T09:47:29Z