Re: BUG #2393: update fails with unique constraint violation

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.