From: | pgsql-bugs(at)postgresql(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Bug #452: unique indices and multi-row UPDATE problem |
Date: | 2001-09-16 04:52:56 |
Message-ID: | 200109160452.f8G4quU75030@hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-09-16 21:25:38 | Re: Bug #443: Problem with time functions. |
Previous Message | John Summerfield | 2001-09-15 12:47:28 | Performance and 72.devel |