Re: index corruption on composite primary key indexes

This could quite possibly be a correct behaviour. "duplicate key value
violates unique constraint" usually happens when you try to insert a
row to a table already containing a row with the same value(s) for the
key column(s). If you have two connections both trying to insert a new
record with the same key, one of them will succeed and the other one
will fail with "duplicate key value violates unique constraint". The
first transaction to commit wins here so it may cause the
unpredictable behaviour you're seeing due to timing variations.

Probably you should catch this error and decide what the proper action
to take is; it might be ignoring the second insert, it might be
updating the already existing row with that key or something else
entirely, depending on your application.

Best Regards

Mikael Krantz

On Tue, Dec 14, 2010 at 3:18 AM, Ng, Stan <sng(at)automotive(dot)com> wrote:
> I’ve noticed what appears to be index corruption on composite primary key
> indexes during my testing. Data deletes, updates, and inserts are applied
> from delta data that is loaded into temporary tables. The duplicate key
> error occurs at different points in time and isn’t isolated to any single
> table, although all affected tables have a composite primary key index. For
> example, if machine1 and machine2 both start off from the same starting
> state, then machine1 might fail on delta 100 while machine2 won’t fail until
> delta 125. The error in the log is “duplicate key value violates unique
> constraint” with a reference to the composite primary key index. The issue
> occurs on different machines, so that would seemingly rule out hardware
> failure. The fact that it only happens on tables with composite primary keys
> is highly suspicious. It occurs reproducibly enough that it seems to be a
> pgsql bug, or maybe a pgsql running on a certain set of hardware + software.
>
>
>
> Some info on the platform I’m using:
>
> PostgreSQL 8.4.4
>
> Red Hat 4.1.2-44
>
> Linux 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64
> x86_64 GNU/Linux
>
> Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
>
>
>
> Does anyone know if this a known issue? Any help would be much appreciated.