Re: BUG #2393: update fails with unique constraint violation - Mailing list pgsql-bugs

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.