Re: set-level update fails with unique constraint violation

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