Skip site navigation (1) Skip section navigation (2)

Re: Strange duplicate key violation error

From: Richard Huxton <dev(at)archonet(dot)com>
To: Casey Duncan <casey(at)pandora(dot)com>
Cc: PostgreSQL-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Strange duplicate key violation error
Date: 2007-06-29 06:49:04
Message-ID: 4684AB60.5090201@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
Casey Duncan wrote:
> 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).

> 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"

> 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.

OK, so a single slony process is updating a single row and causing a 
pkey violation. Has this happened only the once?

> 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.

Well, 8.1.9 is the latest release, but I don't see anything concerning this.

Two things occur to me:
1. Slony adds its own triggers to assorted tables. I don't see how it 
could make a difference, but it might be worth keeping in mind.
2. Primary keys are enforced by unique btree indexes and you could have 
come across a strange corner-case where it's not updating correctly. If 
it's only happened the once, that will make it very hard to track down.

If it doesn't do it again in the next day or so, I'd be inclined to 
REINDEX the table, in case it is in a slightly odd state.

-- 
   Richard Huxton
   Archonet Ltd

In response to

pgsql-general by date

Next:From: Vincenzo RomanoDate: 2007-06-29 07:12:59
Subject: Re: Execution variability
Previous:From: Bjorn BoulderDate: 2007-06-29 05:32:10
Subject: [pgsql] Simple backup utility like mysqldump?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group