Duplicate key error when updating unique columns

[Michael Glaesemann <grzm@xxxxxxxxxxxxx>]

Hello all!

I've been working on a plpgsql function for the past couple of days and  
have been wondering why it's been failing to update a table with  
composite keys (where the key is a combination of two columns). I  
thought it was a problem with my function code, but it appears to be  
rooted in updating keys in general. Here's a test case that exhibits  
the behavior:

create table a1 (
a_id integer not null primary key
) without oids;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"a1_pkey" for table "a1"
CREATE TABLE
insert into a1 (a_id) values (1);
INSERT 0 1
insert into a1 (a_id) values (2);
INSERT 0 1
update a1 set a_id = a_id + 1;
ERROR:  duplicate key violates unique constraint "a1_pkey"

I thought that the unique key would be checked at the end of the update  
statement: after updating every row, a_id will again be unique.  I've  
checked this in 7.4.2 (the production environment) as well as 8.0.0,  
just to see if it was something that had been fixed in the interim.

Wrapping the update in a transaction and setting constraints deferred  
(SET CONSTRAINTS ALL DEFERRED) doesn't help, which I expected as the  
documentation (both 7.4 and 8.0) says that only foreign key constaints  
can be deferred.

I'll need to be able to do something like this in the function, which  
moves subtrees in a nested set hierarchy. Here's a bit more detail:

create table a_b (
	a_id integer not null
		references a (a_id) on update cascade on delete cascade
	, b_id integer not null
		references b (b_id) on update cascade on delete cascade
	, b_lt integer not null
	, b_rt integer not null
	, check (b_lt < b_rt)
	, unique (a_id, b_id)
	, unique (a_id, b_lt)
	, unique (a_id, b_rt)
) without oids;

a_b holds a number of different trees, each identified by a_id. The  
branches of the tree are identified by b_id, with their position in the  
different trees identified by b_lt and b_rt. When I'm moving branches  
around, I'll need to update b_lt and b_rt. During the update (which is  
a single UPDATE statement), b_lt and b_rt will have duplicate values,  
though they will all be unique (for a given a_id) by the time the  
UPDATE is finished.

I suppose I could do it by dropping the unique constraint before the  
update and applying it again after, but if there's a way without  
relaxing the constraints, I'd rather keep them.

Googling a bit shows someone else has noticed this in our very own user  
comments, albeit for 7.2.
http://www.postgresql.org/docs/7.2/interactive/sql-update.html

I feel like I'm missing something very simple. Any pointers? If I'm  
misunderstanding how this is expected to work, I'd love for someone to  
clue me in :)

Thanks for any suggestions and help!

Michael Glaesemann
grzm myrealbox com

test=# select version();
                                                         version
------------------------------------------------------------------------ 
-------------------------------------------------
 PostgreSQL 7.4.2 on powerpc-apple-darwin7.7.0, compiled by GCC gcc  
(GCC) 3.3 20030304 (Apple Computer, Inc. build 1671)
(1 row)

test=# select version();
                                                         version
------------------------------------------------------------------------ 
-------------------------------------------------
 PostgreSQL 8.0.0 on powerpc-apple-darwin7.7.0, compiled by GCC gcc  
(GCC) 3.3 20030304 (Apple Computer, Inc. build 1671)
(1 row)


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings