The following bug has been logged online:

Bug reference: 2393
Logged by: Laurence Dawson
Email address: larry.dawson (AT) vanderbilt (DOT) 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=>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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:

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

T.J. Ferraro wrote:
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.

---------------------------------------------------------------------------

--
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

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/cvsw.../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:
--
View this message in context: http://www.nabble.com/BUG--2393%3A-u....html#a8895405
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Lar <larry.dawson (AT) vanderbilt (DOT) edu> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq