Re: Updates not atomic with respect to indexes

Ben Young said:
> Hi there, I am having a problem with the following code:
>
> CREATE TABLE temp ( num integer UNIQUE );
>
> INSERT INTO temp (num) VALUES (1);
> INSERT INTO temp (num) VALUES (2);
> INSERT INTO temp (num) VALUES (3);
>
> UPDATE temp SET num = num+1;
>
> If the update is really meant to be atomic then this should work, as the
> column is still unique afterwards. However, I get a
> ERROR: duplicate key violates unique constraint "temp_num_key"
>
> Is this something I misunderstand about SQL & ACID in general, a known
> problem
> in Posgresql, or bug which will be fixed sometime. Should I report this as
> a
> bug?

Although the command is atomic, the processing behind the scenes is not.
The problem occurs because record one's PK is updated to 2, but record 2
already has the PK value 2, so you get the PK unique constraint error
message.

One way round this is to create a procedure to select the records in
reverse order (select * from temp order by num DESC), and increment the PK
value in a loop.

Or update the PK values in two stages adding and then subtracting some
number > highest current PK value (inefficient/wasteful):

UPDATE temp SET num = num+10000000+1;
UPDATE temp SET num = num-10000000;

Hope that helps.

John Sidney-Woollett