Обсуждение: BUG #2393: update fails with unique constraint violation
The following bug has been logged online: Bug reference: 2393 Logged by: Laurence Dawson Email address: larry.dawson@vanderbilt.edu PostgreSQL version: 8.1.3 Operating system: Ubuntu Dapper Drake Description: update fails with unique constraint violation Details: Here is the table definition: CREATE TABLE test.test ( a int4 NOT NULL DEFAULT nextval('test.test_a_seq'::regclass), CONSTRAINT pk PRIMARY KEY (a) ) WITHOUT OIDS; ALTER TABLE test.test OWNER TO lstore; And then try an update: lstore=> select * from test.test; a ---- 1 2 3 4 5 6 7 8 9 10 (10 rows) lstore=> update test.test set a = a + 2 where a >= 3; ERROR: duplicate key violates unique constraint "pk" lstore=>
TGF1cmVuY2UsCgp0aGF0IGVycm9yIGlzIGNvcnJlY3Q6Cgo+IENSRUFURSBU QUJMRSB0ZXN0LnRlc3QKPiAoCj4gICBhIGludDQgTk9UIE5VTEwgREVGQVVM VCBuZXh0dmFsKCd0ZXN0LnRlc3RfYV9zZXEnOjpyZWdjbGFzcyksCj4gICBD T05TVFJBSU5UIHBrIFBSSU1BUlkgS0VZIChhKQo+ICkKPiBXSVRIT1VUIE9J RFM7Cj4gQW5kIHRoZW4gdHJ5IGFuIHVwZGF0ZToKPiBsc3RvcmU9PiBzZWxl Y3QgKiBmcm9tIHRlc3QudGVzdDsKPiBhCj4gLS0tLQo+ICAgMQo+ICAgMgo+ ICAgMwo+ICAgNAo+ICAgNQo+ICAgNgo+ICAgNwo+ICAgOAo+ICAgOQo+IDEw Cj4KPiBsc3RvcmU9PiB1cGRhdGUgdGVzdC50ZXN0IHNldCBhID0gYSArIDIg d2hlcmUgYSA+PSAzOwo+IEVSUk9SOiAgZHVwbGljYXRlIGtleSB2aW9sYXRl cyB1bmlxdWUgY29uc3RyYWludCAicGsiCj4KCml0IHN0YXJ0cyBhbnl3aGVy ZSBpbiB0aGUgdGFibGUgYW5kIHVwZGF0ZXMgbGluZSBieSBsaW5lLiBTbyBp ZiBpdCBzdGFydHMKd2l0aCwgc2F5LCBhPTQsIGl0IHRyeXMgdG8gc2V0IGE9 NCsyLCBnaXZpbmcgNiB3aGljaCBpcyBhbGxyZWFkeSBwcmVzZW50LgoKcG9z c2libGUgc29sdXRpb246IGNyZWF0ZSBhIHRlbXAgdGFibGUgZnJvbSBhIHNl bGVjdCB3aXRoIHRoYXQgYSt4LCBhbmQgdGhlbgpmcmVzaGVuIHlvdXIgZGF0 YSBmcm9tIHRoZXJlLgoKT24gYSBzaWRlIG5vZGUgLi4uIGlmIHlvdSBoYXZl IHRvIGNoYW5nZSB5b3VyIFBSSU1BUlkgS0VZIGluIHRoaXMgZmFzaGlvbiwK dGhlcmUgaXMgcHJvcGFibHkgYSBkZXNpZ24gZXJyb3Igd2l0aGluIHlvdXIg ZGF0YWJhc2Ugc2NoZW1lIC8gYXBwbGljYXRpb24uCgpCZXN0IHdpc2hlcwoK SGFyYWxkCgotLQpHSFVNIEhhcmFsZCBNYXNzYQpwZXJzdWFkZXJlIGV0IHBy b2dyYW1tYXJlCkhhcmFsZCBBcm1pbiBNYXNzYQpSZWluc2J1cmdzdHJhw59l IDIwMmIKNzAxOTcgU3R1dHRnYXJ0CjAxNzMvOTQwOTYwNwotClBvc3RncmVT UUwgLSBzdXBwb3J0ZWQgYnkgYSBjb21tdW5pdHkgdGhhdCBkb2VzIG5vdCBw dXQgeW91IG9uIGhvbGQK
Isn't that expected? Your query will try to update row 3 first and set the primary key to 5, which in fact would violate the primary key constraint on that table. Laurence Dawson wrote: > And then try an update: > lstore=> select * from test.test; > a > ---- > 1 > 2 > 3 > 4 > 5 > 6 > 7 > 8 > 9 > 10 > (10 rows) > > lstore=> update test.test set a = a + 2 where a >= 3; > ERROR: duplicate key violates unique constraint "pk" > lstore=> >
T.J. Ferraro wrote: > Isn't that expected? Your query will try to update row 3 first and set > the primary key to 5, which in fact would violate the primary key > constraint on that table. While the error is expected, it isn't valid based on the SQL spec. The spec requires checks to happen at statement conclusion, not during statement execution. But because we use unique indexes to check the constraint, we check during the statement, leading to an error. We have in TODO: * Allow DEFERRABLE UNIQUE constraints? but the question mark is there because we don't know how to fix this without causing terrible performance. --------------------------------------------------------------------------- > > Laurence Dawson wrote: > > And then try an update: > > lstore=> select * from test.test; > > a > > ---- > > 1 > > 2 > > 3 > > 4 > > 5 > > 6 > > 7 > > 8 > > 9 > > 10 > > (10 rows) > > > > lstore=> update test.test set a = a + 2 where a >= 3; > > ERROR: duplicate key violates unique constraint "pk" > > lstore=> > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
I just wanted to see if there is any plan to develop a solution to this - I still see that there is a todo listed on the postgresql site at http://developer.postgresql.org/cvsweb.cgi/~checkout~/pgsql/doc/src/FAQ/TODO.html ...but it hasn't been visited since September 06. There was a motivation for requesting it - the original test code I attached was simply an abstract to show the problem - the actual code was for an implementation of inserting into a nested-set representation of a (huge) directory tree. Nested sets make some queries that we use work very quickly. For now, I have dropped the primary key constraints - inserts are always done through a single stored-procedure, so it's not too bad - but I really don't like it :-) Just for reference, the actual code looks like this:- CREATE OR REPLACE FUNCTION rumple.internal_insert_directory_noname(parent_id int8) RETURNS int8 AS $BODY$declare parent_right int8; new_id int8; begin parent_right = (select right_visit_id from rumple.directory where directory_id = parent_id); update rumple.directory set right_visit_id = right_visit_id + 2 where right_visit_id >= parent_right; update rumple.directory set left_visit_id = left_visit_id + 2 where left_visit_id > parent_right; new_id = nextval('rumple.lstore_seq1'); insert into rumple.directory (directory_id, left_visit_id, right_visit_id) values (new_id, parent_right, (parent_right + 1)); return new_id; end;$BODY$ Bruce Momjian-2 wrote: > > T.J. Ferraro wrote: >> Isn't that expected? Your query will try to update row 3 first and set >> the primary key to 5, which in fact would violate the primary key >> constraint on that table. > > While the error is expected, it isn't valid based on the SQL spec. The > spec requires checks to happen at statement conclusion, not during > statement execution. But because we use unique indexes to check the > constraint, we check during the statement, leading to an error. We have > in TODO: > > * Allow DEFERRABLE UNIQUE constraints? > > but the question mark is there because we don't know how to fix this > without causing terrible performance. > > --------------------------------------------------------------------------- > >> >> Laurence Dawson wrote: >> > And then try an update: >> > lstore=> select * from test.test; >> > a >> > ---- >> > 1 >> > 2 >> > 3 >> > 4 >> > 5 >> > 6 >> > 7 >> > 8 >> > 9 >> > 10 >> > (10 rows) >> > >> > lstore=> update test.test set a = a + 2 where a >= 3; >> > ERROR: duplicate key violates unique constraint "pk" >> > lstore=> >> > >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> > > -- > Bruce Momjian http://candle.pha.pa.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > -- View this message in context: http://www.nabble.com/BUG--2393%3A-update-fails-with-unique-constraint-violation-tf1454271.html#a8895405 Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
Lar <larry.dawson@vanderbilt.edu> writes: > I just wanted to see if there is any plan to develop a solution to > this Nothing is likely to happen until someone has a great idea about how to do it without a major performance hit. And you can't have great ideas on a schedule. regards, tom lane
You may want to suggest to the devs to extend the "UPDATE" syntax with "ORDER BY"? such that: update rumple.directory set right_visit_id = right_visit_id + 2 where right_visit_id >= parent_right ORDER BY right_visit_id DESC; ...would work by enforcing a certain update order. And index scan is performed anyway because of the 'where' clause, might as well do it in order. :) MySQL does it like that AFAIK. V.