Thread: Duplicate key error when updating unique columns

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)

Michael Glaesemann <grzm@myrealbox.com> writes:
> I thought that the unique key would be checked at the end of the update
> statement:

No, it's never worked that way.  Each row is checked as it is inserted.
With an example like this, you may or may not get a failure depending on
chance physical ordering of rows.

This is not per spec, and I believe we have a TODO to fix it, but a fix
is far from trivial.

            regards, tom lane

On May 7, 2005, at 1:10, Tom Lane wrote:

> Michael Glaesemann <grzm@myrealbox.com> writes:
>> I thought that the unique key would be checked at the end of the
>> update
>> statement:
>
> No, it's never worked that way.  Each row is checked as it is inserted.
> With an example like this, you may or may not get a failure depending
> on
> chance physical ordering of rows.
>
> This is not per spec, and I believe we have a TODO to fix it, but a fix
> is far from trivial.

Thanks for clarifying this point. I've since made a simple workaround
for my particular situation:

update a1 set a_id = (-1) * (a_id + 1);
update a1 set a_id = (-1) * (a_id);

This relies on all a_id values to have the same sign (or at least
satisfy unique(abs(a_id))), which is true in my case.

Thanks again!

Michael Glaesemann
grzm myrealbox com