Updates not atomic with respect to indexes

[Ben Young]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Manfred Koizar]

On Wed, 28 Apr 2004 09:33:34 +0100, Ben Young <be*@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Manfred Koizar]

On Wed, 28 Apr 2004 09:33:34 +0100, Ben Young <be*@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[John Sidney-Woollett]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[John Sidney-Woollett]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Manfred Koizar <mk*****@aon.at> writes:

On Wed, 28 Apr 2004 09:33:34 +0100, Ben Young <be*@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Manfred Koizar <mk*****@aon.at> writes:

On Wed, 28 Apr 2004 09:33:34 +0100, Ben Young <be*@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ben Young]

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ben Young]

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match