Lists: | pgsql-general |
---|
From: | neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | set-level update fails with unique constraint violation |
Date: | 2009-12-31 09:52:20 |
Message-ID: | 20091231095220.GA1484@isis.sigpipe.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Magnus Hagander <magnus(at)hagander(dot)net> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2009-12-31 15:52:31 |
Message-ID: | 9837222c0912310752w1a5c22ddr38a3023dcc65b064@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2009/12/31 neuhauser+pgsql-general#postgresql.org
<neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)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/
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2009-12-31 16:04:58 |
Message-ID: | 20091231160458.GD30171@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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;
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
From: | Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> |
---|---|
To: | David Fetter <david(at)fetter(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-02 08:40:42 |
Message-ID: | 20100102084041.GD1484@isis.sigpipe.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
# 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.
--
Roman Neuhauser
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | David Fetter <david(at)fetter(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-02 18:23:24 |
Message-ID: | dcc563d11001021023p2cdd0ba2kdbfbb74999a3e8b4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | David Fetter <david(at)fetter(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-03 09:16:10 |
Message-ID: | 20100103091610.GE1484@isis.sigpipe.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
# 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.
--
Roman Neuhauser
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-03 18:02:39 |
Message-ID: | 20100103180239.GG3149@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com> |
---|---|
To: | neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz |
Cc: | pgsql-general(at)postgresql(dot)org, pgsql-general-owner(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-04 04:33:29 |
Message-ID: | OFCD8B26B7.CF2C9018-ON652576A1.00188372-652576A1.00190B7F@ibsplc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)org(at)sigpipe(dot)cz
To: pgsql-general(at)postgresql(dot)org
Date: 12/31/2009 09:15 PM
Subject: [GENERAL] set-level update fails with unique constraint
violation
Sent by: pgsql-general-owner(at)postgresql(dot)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(at)postgresql(dot)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."
From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-04 12:40:29 |
Message-ID: | 14acc12c-3fde-46ce-9a56-7a4c3456fdc9@mm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> |
---|---|
To: | Daniel Verite <daniel(at)manitou-mail(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-05 14:47:44 |
Message-ID: | 8e2dbb701001050647t505eacah667e149a69073906@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2010/1/4 Daniel Verite <daniel(at)manitou-mail(dot)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
From: | Roman Neuhauser <neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz> |
---|---|
To: | Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-05 19:52:33 |
Message-ID: | 20100105195232.GF1484@isis.sigpipe.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
# Jayadevan(dot)Maymala(at)ibsplc(dot)com / 2010-01-04 10:03:29 +0530:
> From: neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)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
From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | "Dean Rasheed" <dean(dot)a(dot)rasheed(at)googlemail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-06 14:37:43 |
Message-ID: | 4f09530e-99ad-4c87-8e56-c3a695ae964b@mm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> |
---|---|
To: | Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-06 14:47:34 |
Message-ID: | 8e2dbb701001060647l40bb37acpcbe52dcfd2aafaf5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2010/1/5 Roman Neuhauser <neuhauser+pgsql-general#postgresql(dot)org(at)sigpipe(dot)cz>:
> # Jayadevan(dot)Maymala(at)ibsplc(dot)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
From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com> |
---|---|
To: | Daniel Verite <daniel(at)manitou-mail(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-06 15:05:30 |
Message-ID: | 8e2dbb701001060705odadead4oe6881f122fd5bb5c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
2010/1/6 Daniel Verite <daniel(at)manitou-mail(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
Cc: | "Dean Rasheed" <dean(dot)a(dot)rasheed(at)googlemail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-06 15:14:51 |
Message-ID: | 2198.1262790891@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Daniel Verite" <daniel(at)manitou-mail(dot)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
From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Dean Rasheed" <dean(dot)a(dot)rasheed(at)googlemail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-09 14:12:48 |
Message-ID: | 5f98325f-3daf-4011-add8-16ec9c90e2c5@mm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Tom Lane wrote:
> "Daniel Verite" <daniel(at)manitou-mail(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
Cc: | "Dean Rasheed" <dean(dot)a(dot)rasheed(at)googlemail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: set-level update fails with unique constraint violation |
Date: | 2010-01-09 16:39:42 |
Message-ID: | 7714.1263055182@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Daniel Verite" <daniel(at)manitou-mail(dot)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