Skip site navigation (1) Skip section navigation (2)

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

From: Lar <larry(dot)dawson(at)vanderbilt(dot)edu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2393: update fails with unique constraint violation
Date: 2007-02-09 23:17:26
Message-ID: 8895405.post@talk.nabble.com (view raw or flat)
Thread:
Lists: 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.


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-02-10 00:40:58
Subject: Re: BUG #2393: update fails with unique constraint violation
Previous:From: Tom LaneDate: 2007-02-09 18:54:24
Subject: Re: Weird misinterpretation of EXECUTE in PL/pgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group