Thread: Problems with unique restrictions

Hi all

I have a PL/PGSQL conversion procedure that reads a "source table" and then inserts tuples into several related tables.
RecentlyI upgraded from 8.1 to 8.2.0, then to 8.2.1. 

With 8.1 everything worked fine.

Now since I upgraded to 8.2 I have problems inserting data into tables that have unique indexes. Ugly enough, I get the
message'duplicate key violates unique constraint' when inserting the very first record into a table. This happens
everytimewhen the new tuple references another tuple that has been inserted just before this one in the same
transaction.

Putting a "SET CONSTRAINTS ALL DEFERRED" in my procedure does not help.

To me it looks that something with referential integrity checking goes wrong, but in this case the error message would
bemisleading. 

Is there any known change/problem in this area? If not, I will try to strip down my procedure to something simple that
documentsmy problems. 

Regards
--Marcel

"Marcel Gsteiger" <Marcel.Gsteiger@milprog.ch> writes:
> Now since I upgraded to 8.2 I have problems inserting data into tables that have unique indexes. Ugly enough, I get
themessage 'duplicate key violates unique constraint' when inserting the very first record into a table. This happens
everytimewhen the new tuple references another tuple that has been inserted just before this one in the same
transaction.

> Putting a "SET CONSTRAINTS ALL DEFERRED" in my procedure does not help.

> To me it looks that something with referential integrity checking goes wrong, but in this case the error message
wouldbe misleading. 

RI would not have anything to do with a duplicate-key error.

Do you have any SERIAL-type columns in these tables?  My first thought
is of a sequence that hasn't been updated to be above the existing ID
values.  It's fairly easy to get into such a state if you do anything
but a plain vanilla dump-all-and-reload-all update process ...

            regards, tom lane