Ardian Xharra skrev:
> *From:* Anoo Sivadasan Pillai <mailto:aspillai@in.rm.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