Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Ardian Xharra skrev:

> *From:* Anoo Sivadasan Pillai <mailto:aspillai(at)in(dot)rm(dot)com>

>> I am not using any sequences, The following batch can reproduce the
>> behaviour.
>> CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
>> INSERT INTO master VALUES ( 1, 1 ) ;
>> INSERT INTO master VALUES ( 2, 2) ;
>> UPDATE master SET m2 = m2 + 1;

> It's normal behaviour, because after the first update it will be 2 same
> values for m2 and you don't want that since you have a unique constraint
> for that column.

Please note: This is a bug in Postgresql, not "normal behaviour". From a
conceptual perspective, there is no "after the first update" - the
statement is supposed to be atomic.

Unfortunately, the problem is waiting for someone to get a great idea:

http://svr5.postgresql.org/pgsql-bugs/2007-02/msg00075.php

If you can't wait, you are probably better off working around the
problem. Standard solution is to do:

UPDATE master SET m2 = -m2;
UPDATE master SET m2 = -m2+1;

or something similar.

Nis