Bug #935: UPDATE on column with UNIQUE constraint sometimes(!) fails.

Chris Wesseling (chris(dot)wesseling(at)xs4all(dot)nl) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
UPDATE on column with UNIQUE constraint sometimes(!) fails.

Long Description
The result of an UPDATE changing a column with a uniqueness CONSTRAINT depends on the physical order of the rows (or tuple). I believe this is in contradiction with the relational model.

example.

Create a table "example" with a integer column "nr". And add a uniqueness constraint to it.
Fill it with the values 1,2 and 3.
Now if you want to increment those values by one, this should give you a table with values 2,3 and 4. (all values are unique).
UPDATE example SET nr = nr+1;

Fails with an error.

Empty the table. And fill it with the values 3, 2, and 1.
UPDATE example SET nr = nr+1;

Now gives the expected result.

The order 3,1,2 fails as well, leaving the table unchanged. Which at least is far far better, than what MySQL makes of it. (it updates untill can't update no more leaving you with a very unpredictable partially updated table! Luckily we have no dealings with MySQL's anomalies ;)
Logically there is no difference between the 3 relations, yet the same query gives 2 different results.

Sample Code
chris=# SELECT version();
version
-------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

chris=# CREATE TABLE example (nr INTEGER UNIQUE);
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'example_nr_key' for table 'example'
CREATE TABLE
chris=# INSERT INTO example VALUES (1);
INSERT 17011 1
chris=# INSERT INTO example VALUES (2);
INSERT 17012 1
chris=# INSERT INTO example VALUES (3);
INSERT 17013 1
chris=# SELECT * FROM example;
nr
----
1
2
3
(3 rows)

chris=# UPDATE example SET nr = nr+1;
ERROR: Cannot insert a duplicate key into unique index example_nr_key
chris=# DELETE FROM example;
DELETE 3
chris=# INSERT INTO example VALUES (3);
INSERT 17014 1
chris=# INSERT INTO example VALUES (2);
INSERT 17015 1
chris=# INSERT INTO example VALUES (1);
INSERT 17016 1
chris=# SELECT * FROM example;
nr
----
3
2
1
(3 rows)

chris=# UPDATE example SET nr = nr+1;
UPDATE 3
chris=# SELECT * FROM example;
nr
----
4
3
2
(3 rows)

chris=#

No file was uploaded with this report