Search Postgresql Archives

Re: set-level update fails with unique constraint violation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, Jan 03, 2010 at 10:16:10AM +0100, Roman Neuhauser wrote:
> # scott.marlowe@xxxxxxxxx / 2010-01-02 11:23:24 -0700:
> > On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser <neuhauser@xxxxxxxxxx> wrote:
> > > # david@xxxxxxxxxx / 2009-12-31 08:04:58 -0800:
> > >> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql.org@xxxxxxxxxx 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@xxxxxxxxxx> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@xxxxxxxxx
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux