Postgres Pro
Downloads
Home   >   mailing lists

Thread: Updates not atomic with respect to indexes

Updates not atomic with respect to indexes

From
Ben Young
Date:
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?

Thanks for your help in advance!

Ben Young

Re: Updates not atomic with respect to indexes

From
"John Sidney-Woollett"
Date:
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

Re: Updates not atomic with respect to indexes

From
Manfred Koizar
Date:
On Wed, 28 Apr 2004 09:33:34 +0100, Ben Young <ben@transversal.com>
wrote:
>UPDATE temp SET num = num+1;
>ERROR:  duplicate key violates unique constraint "temp_num_key"

>Is this [...] a known problem in Posgresql,

Yes.

> or bug which will be fixed sometime.

Don't know.

If you need a workaround for now, do it in two steps:

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

This assumes you have a range of numbers that is normally not used.

Servus
 Manfred

Re: Updates not atomic with respect to indexes

From
Tom Lane
Date:
Manfred Koizar <mkoi-pg@aon.at> writes:
> On Wed, 28 Apr 2004 09:33:34 +0100, Ben Young <ben@transversal.com>
> wrote:
>> Is this [...] a known problem in Posgresql,
> Yes.
>> or bug which will be fixed sometime.
> Don't know.

I believe this is meant to be covered by this TODO item:

* Allow DEFERRABLE UNIQUE constraints

because the real issue is that the uniqueness check occurs immediately
rather than being deferred till end of statement or transaction.

Dunno when it will rise to the top of anyone's priority list...

            regards, tom lane

Re: Updates not atomic with respect to indexes

From
Ben Young
Date:
On Wednesday 28 April 2004 13:37, Tom Lane wrote:
>
> I believe this is meant to be covered by this TODO item:
>
> * Allow DEFERRABLE UNIQUE constraints
>
> because the real issue is that the uniqueness check occurs immediately
> rather than being deferred till end of statement or transaction.
>
> Dunno when it will rise to the top of anyone's priority list...
>
>             regards, tom lane


Thanks for everyones responses. I guess for now I will just need to find a way
around it. Looking forward to deferrable unique contraints though!

Ben