Thread: 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