Обсуждение: duplicate key violated errors

Hi All:

I have a table, which has over 3 million rows. We have both a unique
constraint ( which creates implicit index) as well as a regular unique
index on the same column. We had to put the unique constraint because
duplicate values
were getting inserted to the table in spite of the regular unique index
on the column. In our applications we always check to make sure  the
existence of the value before we insert, in another word we do not
insert if the value exist there already.

 Do you know of any known issues where postgres throws duplicate key
violated error in a situation like mine where I have two indexes on the
same column. I know it does sound redundant, but I am skeptical about
changing this any other way since it is a production
system. I did rebuilt the regular index but we still kept on getting the
same error, so index corruption is  out of question.

We run  postgresql 8.2.5

Thanks.
Dinesh

dinesh <dinesh@mongonet.net> writes:
> I have a table, which has over 3 million rows. We have both a unique
> constraint ( which creates implicit index) as well as a regular unique
> index on the same column. We had to put the unique constraint because
> duplicate values
> were getting inserted to the table in spite of the regular unique index
> on the column.

That sounds awfully fishy.  We have not heard of unique indexes failing
to enforce the constraint in quite some time.

What's the data type of the column?  A possible explanation for this
kind of issue is a broken comparison function for the particular data
type.

> We run  postgresql 8.2.5

That's two years out of date, and the list of bugs fixed since then is
quite extensive.  A quick scan through the release notes doesn't show
any obviously-related bugs, but it would still be worth your while to
update to 8.2.latest and then reindex to see if the problem goes away.

            regards, tom lane

Hello,

I experienced a similar issue with 8.2.x having a duplicate value in a
primary key !
In our case it definitively happened as the plates got out of space, so
I did put the fault on us, not on Postgres :-)

regards,

Marc Mamin




-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of dinesh
Sent: Monday, October 26, 2009 10:14 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] duplicate key violated errors

Hi All:

I have a table, which has over 3 million rows. We have both a unique
constraint ( which creates implicit index) as well as a regular unique
index on the same column. We had to put the unique constraint because
duplicate values were getting inserted to the table in spite of the
regular unique index on the column. In our applications we always check
to make sure  the existence of the value before we insert, in another
word we do not insert if the value exist there already.

 Do you know of any known issues where postgres throws duplicate key
violated error in a situation like mine where I have two indexes on the
same column. I know it does sound redundant, but I am skeptical about
changing this any other way since it is a production system. I did
rebuilt the regular index but we still kept on getting the same error,
so index corruption is  out of question.

We run  postgresql 8.2.5

Thanks.
Dinesh

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin