Strange duplicate key violation error

Поиск
Список
Период
Сортировка
Искать
От
Casey Duncan
Тема
Strange duplicate key violation error
Дата
Msg-id
6ADC6743-280A-4791-8893-51A178BBD519@pandora.com
Список
Дерево обсуждения
Strange duplicate key violation error Casey Duncan <casey@pandora.com>
Re: Strange duplicate key violation error Richard Huxton <dev@archonet.com>
I have this table "replica_sync_log" which is updated thousands of  
times a day to reflect the state of various schemas in a database  
which acts as an offline secondary to various other databases (each  
of the source databases is mapped to its own schema in the  
secondary). The table has the following definition:

         Table "radio_prod_default.replica_sync_log"
       Column      |            Type             | Modifiers
------------------+-----------------------------+-----------
db_host          | text                        | not null
db_port          | text                        | not null
db_name          | text                        | not null
last_sync_id     | integer                     |
last_sync_time   | timestamp without time zone |
last_commit_time | timestamp without time zone |
Indexes:
     "replica_sync_log_pkey" PRIMARY KEY, btree (db_host, db_port,  
db_name), tablespace "data1"
Tablespace: "data1"

There are in fact one of these tables for each schema, each one  
contains exactly one row (the "log" in the name is a bit misleading,  
these just contain the current replica state, not a running log).

Here is an error that I got this morning that doesn't make much sense  
to me:

2007-06-28 08:53:54.937 PDT [d:radio_reports_new u:slony s:4683d86f. 
3681 3] ERROR:  duplicate key violates unique constraint  
"replica_sync_log_pkey"
2007-06-28 08:53:54.937 PDT [d:radio_reports_new u:slony s:4683d86f. 
3681 4] STATEMENT:  UPDATE replica_sync_log SET last_sync_id =  
7147892, last_sync_time = '2007-06-27 23:28:04.586846'::timestamp,  
last_commit_time = 'Thu Jun 28 08:53:54 2007'::timestamp WHERE  
db_name = 'radio_prod_default' AND db_host = 'radiodb-default-1' AND  
db_port = '5432';

I've never seen this error before in millions of updates to this  
table. It confuses me because the columns indexed by the primary key  
*are not* being modified, so how could the update violate the  
constraint? Furthermore there is only one record in the table anyway.

The updates are performed by daemons that are in charge of  
replicating the changes for one database each. So there is no  
concurrent updating on this table, only one process updates it.

The error caused the daemon to pause, so I restarted it. The error  
did not recur the second time.

fwiw this is postgres 8.1.8 on Linux.

Thanks for any insights.

-Casey

В списке pgsql-general по дате отправления
От: Björn Lundin
Дата:
От: Mario Jose Canto Barea
Дата:
FAQ