Re: set-level update fails with unique constraint violation

Hello,

this fails with "duplicate key value":

CREATE TABLE x (
i INT NOT NULL UNIQUE
);
INSERT INTO x (i) VALUES (1), (2), (3);
UPDATE x SET i = i + 1;

are there any plans to make this work?

--
Roman Neuhauser

2009/12/31 neuhauser+pgsql-general#postgresql.org
<neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz>:
> Hello,
>
> this fails with "duplicate key value":
>
>    CREATE TABLE x (
>      i INT NOT NULL UNIQUE
>    );
>    INSERT INTO x (i) VALUES (1), (2), (3);
>    UPDATE x SET i = i + 1;
>
> are there any plans to make this work?

Sure. 8.5 (current alpha release included) will let you do
i INT NOT NULL UNIQUE DEFERRABLE

which will make that work.

(you might want to consider using an actual email address if you want
to get responses to your questions in the future)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz wrote:
> Hello,
>
> this fails with "duplicate key value":
>
> CREATE TABLE x (
> i INT NOT NULL UNIQUE
> );
> INSERT INTO x (i) VALUES (1), (2), (3);
> UPDATE x SET i = i + 1;
>
> are there any plans to make this work?

This will work in 8.5:

CREATE TABLE x (
i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO x (i) VALUES (1), (2), (3);
UPDATE x SET i = i + 1;

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

# david(at)fetter(dot)org / 2009-12-31 08:04:58 -0800:
> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz wrote:
> > Hello,
> >
> > this fails with "duplicate key value":
> >
> > CREATE TABLE x (
> > i INT NOT NULL UNIQUE
> > );
> > INSERT INTO x (i) VALUES (1), (2), (3);
> > UPDATE x SET i = i + 1;
> >
> > are there any plans to make this work?
>
> This will work in 8.5:
>
> CREATE TABLE x (
> i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED
> );
> INSERT INTO x (i) VALUES (1), (2), (3);
> UPDATE x SET i = i + 1;

thanks, this might be a bearable workaround in some cases
provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE.
what I really want is a mode that fires the constraint check
at the end of the statement.

--
Roman Neuhauser

On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> wrote:
> # david(at)fetter(dot)org / 2009-12-31 08:04:58 -0800:
>> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz wrote:
>> > Hello,
>> >
>> > this fails with "duplicate key value":
>> >
>> >     CREATE TABLE x (
>> >       i INT NOT NULL UNIQUE
>> >     );
>> >     INSERT INTO x (i) VALUES (1), (2), (3);
>> >     UPDATE x SET i = i + 1;
>> >
>> > are there any plans to make this work?
>>
>> This will work in 8.5:
>>
>> CREATE TABLE x (
>>     i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED
>> );
>> INSERT INTO x (i) VALUES (1), (2), (3);
>> UPDATE x SET i = i + 1;
>
> thanks, this might be a bearable workaround in some cases
> provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE.
> what I really want is a mode that fires the constraint check
> at the end of the statement.

What advantage would there be to a constraint that fires right after
to one that fires at the end of the transaction?

# scott(dot)marlowe(at)gmail(dot)com / 2010-01-02 11:23:24 -0700:
> On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> wrote:
> > # david(at)fetter(dot)org / 2009-12-31 08:04:58 -0800:
> >> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz wrote:
> >> > Hello,
> >> >
> >> > this fails with "duplicate key value":
> >> >
> >> >     CREATE TABLE x (
> >> >       i INT NOT NULL UNIQUE
> >> >     );
> >> >     INSERT INTO x (i) VALUES (1), (2), (3);
> >> >     UPDATE x SET i = i + 1;
> >> >
> >> > are there any plans to make this work?
> >>
> >> This will work in 8.5:
> >>
> >> CREATE TABLE x (
> >>     i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED
> >> );
> >> INSERT INTO x (i) VALUES (1), (2), (3);
> >> UPDATE x SET i = i + 1;
> >
> > thanks, this might be a bearable workaround in some cases
> > provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE.
> > what I really want is a mode that fires the constraint check
> > at the end of the statement.
>
> What advantage would there be to a constraint that fires right after
> to one that fires at the end of the transaction?

What? I didn't say that. I'm saying that I want IMMEDIATE constraint
that is atomic with regard to the statement. It's obvious that

UPDATE x SET i = i + 1

cannot break a UNIQUE constraint on x.i lest the constraint checking
is not atomic.

I can see how such non-atomic checking can be good performance-wise,
but I'm more interested in logical correctness.

--
Roman Neuhauser

On Sun, Jan 03, 2010 at 10:16:10AM +0100, Roman Neuhauser wrote:
> # scott(dot)marlowe(at)gmail(dot)com / 2010-01-02 11:23:24 -0700:
> > On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> wrote:
> > > # david(at)fetter(dot)org / 2009-12-31 08:04:58 -0800:
> > >> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz wrote:
> > >> > Hello,
> > >> >
> > >> > this fails with "duplicate key value":
> > >> >
> > >> >     CREATE TABLE x (
> > >> >       i INT NOT NULL UNIQUE
> > >> >     );
> > >> >     INSERT INTO x (i) VALUES (1), (2), (3);
> > >> >     UPDATE x SET i = i + 1;
> > >> >
> > >> > are there any plans to make this work?
> > >>
> > >> This will work in 8.5:
> > >>
> > >> CREATE TABLE x (
> > >>     i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED
> > >> );
> > >> INSERT INTO x (i) VALUES (1), (2), (3);
> > >> UPDATE x SET i = i + 1;
> > >
> > > thanks, this might be a bearable workaround in some cases
> > > provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE.
> > > what I really want is a mode that fires the constraint check
> > > at the end of the statement.
> >
> > What advantage would there be to a constraint that fires right after
> > to one that fires at the end of the transaction?
>
> What? I didn't say that. I'm saying that I want IMMEDIATE constraint
> that is atomic with regard to the statement. It's obvious that
>
> UPDATE x SET i = i + 1
>
> cannot break a UNIQUE constraint on x.i lest the constraint checking
> is not atomic.
>
> I can see how such non-atomic checking can be good performance-wise,
> but I'm more interested in logical correctness.

At least one of us hasn't understood the situation. :)

There is a problem in all released versions of PostgreSQL where, when
you issue that UPDATE, it is checked at each row. If at any given
row, the UDPATE causes a conflict, the statement fails, even though
the whole UPDATE would have succeeded if it had completed. The
DEFERRED uniqueness constraints in 8.5alpha3 fix this problem.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi,
This seems to work..
UPDATE x set i=i+1
from (select i as m from x order by m desc) y where x.i = y.m
Jayadevan

From: neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz
To: pgsql-general(at)postgresql(dot)org
Date: 12/31/2009 09:15 PM
Subject: [GENERAL] set-level update fails with unique constraint
violation
Sent by: pgsql-general-owner(at)postgresql(dot)org

Hello,

this fails with "duplicate key value":

CREATE TABLE x (
i INT NOT NULL UNIQUE
);
INSERT INTO x (i) VALUES (1), (2), (3);
UPDATE x SET i = i + 1;

are there any plans to make this work?

--
Roman Neuhauser

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

David Fetter wrote:

> The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem

That fix has a drawback: when the unique constraint is violated, the rest of
the transaction runs with data that is somehow corrupted, with duplicate
values being visible. It may be uneasy to predict if and how the statements
following the temporary-ignored constraint violation will misbehave.
Generally, the transaction will ultimately fail and the mess will be cleaned
up by the rollback, but in the worst case it may not even fail, for instance
if the offending rows get deleted before the end.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

2010/1/4 Daniel Verite <daniel(at)manitou-mail(dot)org>:
>        David Fetter wrote:
>
>> The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem
>
> That fix has a drawback: when the unique constraint is violated, the rest of
> the transaction runs with data that is somehow corrupted, with duplicate
> values being visible. It may be uneasy to predict if and how the statements
> following the temporary-ignored constraint violation will misbehave.
> Generally, the transaction will ultimately fail and the mess will be cleaned
> up by the rollback, but in the worst case it may not even fail, for instance
> if the offending rows get deleted before the end.
>

No, deferrable constraints are more flexible than that, so you can
have end-of-statement checks if that's what you want.

A deferrable constraint has 2 modes of operation, depending on how you
choose to define the constraint:

1). DEFERRABLE INITIALLY IMMEDIATE will result in the constraint being
checked after each statement in the transaction. This will allow the
i=i+1 UPDATE to succeed, but any UPDATE which causes uniqueness to be
violated at the end of the statement will fail immediately, and you
will have to rollback.

2). DEFERRABLE INITIALLY DEFERRED will cause the constraint check to
be done at the end of the transaction (or when SET CONSTRAINTS is
called). This will allow the constraint to be temporarily violated by
statements inside a transaction, and if the duplicates are then
deleted, the transaction will succeed.

If you just specify DEFERRABLE, then INITIALLY IMMEDIATE is the default.
See http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html

This is all per the SQL spec, and also the same behaviour as Oracle.

So there is quite a bit of flexibility - you may choose to have the
constraint checked at any of these times:
- after each row (the default for NON DEFERRABLE constraints)
- after each statement (DEFERRABLE [INITIALLY IMMEDIATE])
- at the end of the transaction (DEFERRABLE INITIALLY DEFERRED)
- whenever you want in a transaction using SET CONSTRAINTS

Regards,
Dean

# Jayadevan(dot)Maymala(at)ibsplc(dot)com / 2010-01-04 10:03:29 +0530:
> From: neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz
> >
> > this fails with "duplicate key value":
> >
> > CREATE TABLE x (
> > i INT NOT NULL UNIQUE
> > );
> > INSERT INTO x (i) VALUES (1), (2), (3);
> > UPDATE x SET i = i + 1;
> >
> > are there any plans to make this work?
>
> Hi,
> This seems to work..
> UPDATE x set i=i+1
> from (select i as m from x order by m desc) y where x.i = y.m
> Jayadevan

Thanks, that nicely achieves the illusion of atomic immediate checking.

--
Roman Neuhauser

Dean Rasheed wrote:

> So there is quite a bit of flexibility - you may choose to have the
> constraint checked at any of these times:
> - after each row (the default for NON DEFERRABLE constraints)
> - after each statement (DEFERRABLE [INITIALLY IMMEDIATE])
> - at the end of the transaction (DEFERRABLE INITIALLY DEFERRED)
> - whenever you want in a transaction using SET CONSTRAINTS

Thanks for clarifying that. I've just tried the different scenarios with
8.5alpha3, and I find that these improvements are quite useful and welcome.
But still I wonder why there is that difference in behavior between NON
DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint
doesn't get deferred by using SET CONSTRAINTS.
In the first case, we get the "after each row" behavior with the pk=pk+1
failure, as with the previous PG versions.
In the second case, we get the "after each statement" behavior which I
believe complies with the standard, contrary to the first case, and
successfully achieves the pk=pk+1 update as expected.
Personally, I would have imagined that behavior #1 would be removed once
behavior #2 was implemented, not that the two would co-exist. Is there a
reason to keep #1?

Also, I read in the current doc for 8.5:
http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html
<quote>
DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that
is not deferrable will be checked immediately after every command
</quote>

"after every command" seems to describe behavior #2, not #1.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

2010/1/5 Roman Neuhauser <neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz>:
> # Jayadevan(dot)Maymala(at)ibsplc(dot)com / 2010-01-04 10:03:29 +0530:
>> This seems to work..
>> UPDATE x  set i=i+1
>> from  (select i as m from x order by m desc) y   where x.i = y.m
>> Jayadevan
>
> Thanks, that nicely achieves the illusion of atomic immediate checking.
>
> --
> Roman Neuhauser

That is not guaranteed to work. Depending on how the optimiser does
the join, the reverse ordering may not be preserved in the update. Try
it for larger tables (for me it fails at 100000 rows).

Regards,
Dean

2010/1/6 Daniel Verite <daniel(at)manitou-mail(dot)org>:
>        Dean Rasheed wrote:
>
>> So there is quite a bit of flexibility - you may choose to have the
>> constraint checked at any of these times:
>>  - after each row (the default for NON DEFERRABLE constraints)
>>  - after each statement (DEFERRABLE [INITIALLY IMMEDIATE])
>>  - at the end of the transaction (DEFERRABLE INITIALLY DEFERRED)
>>  - whenever you want in a transaction using SET CONSTRAINTS
>
> Thanks for clarifying that. I've just tried the different scenarios with
> 8.5alpha3, and I find that these improvements are quite useful and welcome.
> But still I wonder why there is that difference in behavior between NON
> DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint
> doesn't get deferred by using SET CONSTRAINTS.
> In the first case, we get the "after each row" behavior with the pk=pk+1
> failure, as with the previous PG versions.
> In the second case, we get the "after each statement" behavior which I
> believe complies with the standard, contrary to the first case, and
> successfully achieves the pk=pk+1 update as expected.
> Personally, I would have imagined that behavior #1 would be removed once
> behavior #2 was implemented, not that the two would co-exist. Is there a
> reason to keep #1?
>

Performance is one reason (perhaps the only one?). #1 is implemented
using a unique index, which is checked as each row is inserted. #2
uses triggers in addition to the unique index (conflicts are queued up
to be re-checked at the end of the command/transaction). So #1 will
always out-perform #2 (unless there aren't any temporary conflicts to
be re-checked).

> Also, I read in the current doc for 8.5:
> http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html
> <quote>
> DEFERRABLE
> NOT DEFERRABLE
>
>    This controls whether the constraint can be deferred. A constraint that
> is not deferrable will be checked immediately after every command
> </quote>
>
> "after every command" seems to describe behavior #2, not #1.
>

Hmm. Yes that comment is misleading in this context. Non-deferrable
unique constraints are currently checked after each row.

Regards,
Dean

"Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
> But still I wonder why there is that difference in behavior between NON
> DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint
> doesn't get deferred by using SET CONSTRAINTS.
> In the first case, we get the "after each row" behavior with the pk=pk+1
> failure, as with the previous PG versions.
> In the second case, we get the "after each statement" behavior which I
> believe complies with the standard, contrary to the first case, and
> successfully achieves the pk=pk+1 update as expected.
> Personally, I would have imagined that behavior #1 would be removed once
> behavior #2 was implemented, not that the two would co-exist. Is there a
> reason to keep #1?

1. Performance. The cost of #2 is very large, and the number of cases
where you actually need it is not.

2. Backwards compatibility. Some apps might be depending on the details
of the behavior.

regards, tom lane

Tom Lane wrote:

> "Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
> > But still I wonder why there is that difference in behavior between NON
> > DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint
> > doesn't get deferred by using SET CONSTRAINTS.
> > In the first case, we get the "after each row" behavior with the pk=pk+1
> > failure, as with the previous PG versions.
> > In the second case, we get the "after each statement" behavior which I
> > believe complies with the standard, contrary to the first case, and
> > successfully achieves the pk=pk+1 update as expected.
> > Personally, I would have imagined that behavior #1 would be removed once
> > behavior #2 was implemented, not that the two would co-exist. Is there a
> > reason to keep #1?
>
> 1. Performance. The cost of #2 is very large, and the number of cases
> where you actually need it is not.

Per Dean's explanation upthread, It looks like an additional cost for #2
would occur mostly when temporary conflicts occur, that is, when it's needed.

I've tried UPDATEs of a primary key in batches of 1M rows with 8.5, and in
the general case of no conflict, I get #2 being about 8-15% slower than #1.
I've seen no difference for INSERTs.
When there are temporary conflicts, #2 is slower but succeeds whereas #1
fails, so #2 is the winner.
When there are persistant conflicts, #2 fails slower than #1, but do we
really care?

> 2. Backwards compatibility. Some apps might be depending on the details
> of the behavior.

Apparently, the occurrence of conflicts during the execution is mostly
unpredictable anyway, from the point of view of the end user. For example I
was under the illusion that UPDATE...SET pk=pk-1 always worked, but I've
discovered while testing that it wasn't the case. Conversely depending on it
to fail, for this update or a similar update, thats seems insane for an app.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

"Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
> Tom Lane wrote:
>> 1. Performance. The cost of #2 is very large, and the number of cases
>> where you actually need it is not.

> Per Dean's explanation upthread, It looks like an additional cost for #2
> would occur mostly when temporary conflicts occur, that is, when it's needed.

I'm not sure where you got that from his explanation, but it's not the
case. The problem with any type of delayed verification is that it
requires a second index search, on top of the one you already did while
making your index entry. This occurs whether or not there is any conflict.
The problem is especially acute when you have an update or insert
affecting a large fraction of the table.

regards, tom lane