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
