Chris Wesseling ([EMAIL PROTECTED]) 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
