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

Andrew Gerweck (andy(at)presedia(dot)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 conform to the unique index's constraint as an atom fail because there are two keys with the same value while Postgres hasn't yet 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 for tree representation. See http://www.intelligententerprise.com/001020/celko1_1.shtml for a discussion of the elegant but obscure 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