Thread: 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.