[Tickets #13876] horde_histories_modseq reset to 1 by code

[noreply at bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: https://bugs.horde.org/ticket/13876
------------------------------------------------------------------------------
  Ticket             | 13876
  Created By         | horde at albasoft.com
  Summary            | horde_histories_modseq reset to 1 by code
  Queue              | Synchronization
  Version            | Git master
  Type               | Bug
  State              | Unconfirmed
  Priority           | 2. Medium
  Milestone          |
  Patch              |
  Owners             |
------------------------------------------------------------------------------


horde at albasoft.com (2015-02-20 18:20) wrote:

Hi,

This week I noticed my devices were not syncing data, and I've found  
this in my logs:
2015-02-16T11:50:35+01:00 ERR: HORDE [kronolith] SQL QUERY FAILED:  
SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key violates  
unique constraint "horde_histories_modseq_pkey"
         INSERT INTO horde_histories_modseq (history_modseqempty)  
VALUES(0) [pid 30578 on line 553 of  
"/var/www/html/php/horde5-pear/pear/php/Horde/Db/Adapter/Base.php"]
2015-02-16T11:50:35+01:00 ERR: HORDE [kronolith] SQLSTATE[23505]:  
Unique violation: 7 ERROR:  duplicate key violates unique constraint  
"horde_histories_modseq_pkey" [pid 30578 on line 354 of  
"/var/www/html/php/horde5-pear/pear/php/Horde/History/Sql.php"]

Searching in my database (postgresql), I've found at "horde_histories"  
table that "history_modseq" was reset to 1 precisely at that  
timestamp. By that time, my server was heavy loaded.
So that "INSERT" error reset "history_modseq" sequence and my devices  
requests are no longer able to find new events, contacts, etc.

I took a look at code, trying to find out why that sequence was reset.  
I have no answer yet, as I can't reproduce that event.
But I've found something rather odd in "Db/Adapter/Pdo/Pgsql.php" at  
"insert" function: after every insert at database, primary keys are  
recalculated calling resetPkSequence. I can't understand why you need  
to do that.
And the problem is that resetPkSequence operates in a way that will  
lead to "duplicate key violation" errors with sequences, as pointed by  
the example in this post:  
http://www.postgresql.org/message-id/4E5D8369.9090205@ringerc.id.au
Calling 'setval' that way while other processes may be calling  
'nextval' will generate the above errors.

Getting a modseq reset is really something to avoid, also when heavy loaded.

Thank you.