Thread: set-level update fails with unique constraint violation
set-level update fails with unique constraint violation
From
neuhauser+pgsql-general#postgresql.org@sigpipe.cz
Date:
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.org@sigpipe.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.org@sigpipe.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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@fetter.org / 2009-12-31 08:04:58 -0800: > On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql.org@sigpipe.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@sigpipe.cz> wrote: > # david@fetter.org / 2009-12-31 08:04:58 -0800: >> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql.org@sigpipe.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.marlowe@gmail.com / 2010-01-02 11:23:24 -0700: > On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser <neuhauser@sigpipe.cz> wrote: > > # david@fetter.org / 2009-12-31 08:04:58 -0800: > >> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql.org@sigpipe.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.marlowe@gmail.com / 2010-01-02 11:23:24 -0700: > > On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser <neuhauser@sigpipe.cz> wrote: > > > # david@fetter.org / 2009-12-31 08:04:58 -0800: > > >> On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql.org@sigpipe.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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.org@sigpipe.cz
To: pgsql-general@postgresql.org
Date: 12/31/2009 09:15 PM
Subject: [GENERAL] set-level update fails with unique constraint violation
Sent by: pgsql-general-owner@postgresql.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@postgresql.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."
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.org@sigpipe.cz
To: pgsql-general@postgresql.org
Date: 12/31/2009 09:15 PM
Subject: [GENERAL] set-level update fails with unique constraint violation
Sent by: pgsql-general-owner@postgresql.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@postgresql.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@manitou-mail.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.Maymala@ibsplc.com / 2010-01-04 10:03:29 +0530: > From: neuhauser+pgsql-general#postgresql.org@sigpipe.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.org@sigpipe.cz>: > # Jayadevan.Maymala@ibsplc.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@manitou-mail.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@manitou-mail.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@manitou-mail.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@manitou-mail.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