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

The following bug has been logged online:

Bug reference: 2393
Logged by: Laurence Dawson
Email address: larry(dot)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=>

Laurence,

that error is correct:

> CREATE TABLE test.test
> (
> a int4 NOT NULL DEFAULT nextval('test.test_a_seq'::regclass),
> CONSTRAINT pk PRIMARY KEY (a)
> )
> WITHOUT OIDS;
> And then try an update:
> lstore=> select * from test.test;
> a
> ----
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
>
> lstore=> update test.test set a = a + 2 where a >= 3;
> ERROR: duplicate key violates unique constraint "pk"
>

it starts anywhere in the table and updates line by line. So if it starts
with, say, a=4, it trys to set a=4+2, giving 6 which is allready present.

possible solution: create a temp table from a select with that a+x, and then
freshen your data from there.

On a side node ... if you have to change your PRIMARY KEY in this fashion,
there is propably a design error within your database scheme / application.

Best wishes

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
PostgreSQL - supported by a community that does not put you on hold

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

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

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.

Lar <larry(dot)dawson(at)vanderbilt(dot)edu> writes:
> I just wanted to see if there is any plan to develop a solution to
> this

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