Thread: Bug #452: unique indices and multi-row UPDATE problem

Andrew Gerweck (andy@presedia.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
unique indices and multi-row UPDATE problem

Long Description
When doing a multi-row update, unique indices seem to be recalculated in the middle of the query.  Updates that would
conformto the unique index's constraint as an atom fail because there are two keys with the same value while Postgres
hasn'tyet finished the query. 

What I expected: my (single) query would execute without complaint if its end state was consistent with all unique
indices.

The multi-row update I suggested is crucial for insertions into tables representing trees using the nested set model
fortree representation.  See http://www.intelligententerprise.com/001020/celko1_1.shtml for a discussion of the elegant
butobscure nested set model. 

Sample Code
# CREATE TABLE a (b INTEGER);
# CREATE UNIQUE INDEX c ON a (b);
# INSERT INTO a VALUES (1);
# INSERT INTO b VALUES (2);
# UPDATE a SET b = b+1;
ERROR: Cannot insert a duplicate key into unique index c

No file was uploaded with this report