# 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. -- Roman Neuhauser -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
