"Daniel Verite" 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,
Tom Lane wrote:
> "Daniel Verite" 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 e
"Daniel Verite" 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
2010/1/6 Daniel Verite :
> 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])
2010/1/5 Roman Neuhauser :
> # jayadevan.maym...@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.
>
> --
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 transac
# jayadevan.maym...@ibsplc.com / 2010-01-04 10:03:29 +0530:
> From: neuhauser+pgsql-general#postgresql@sigpipe.cz
> >
> > this fails with "duplicate key value":
> >
> > CREATE TABLE x (
> > i INT NOT NULL UNIQUE
> > );
> > INSERT INTO x (i) VALUES (1), (2), (3);
> > UP
2010/1/4 Daniel Verite :
> 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 vis
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 i
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@sigpipe.cz
To: pgsql-general@postgresql.org
Date: 12/31/2009 09:15 PM
Subject:[GENERAL] set-level update fails
On Sun, Jan 03, 2010 at 10:16:10AM +0100, Roman Neuhauser wrote:
> # scott.marl...@gmail.com / 2010-01-02 11:23:24 -0700:
> > On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser
> > wrote:
> > > # da...@fetter.org / 2009-12-31 08:04:58 -0800:
> > >> On Thu, Dec 31, 2009 at 10:52:20AM +0100,
> > >> n
# scott.marl...@gmail.com / 2010-01-02 11:23:24 -0700:
> On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser wrote:
> > # da...@fetter.org / 2009-12-31 08:04:58 -0800:
> >> On Thu, Dec 31, 2009 at 10:52:20AM +0100,
> >> neuhauser+pgsql-general#postgresql@sigpipe.cz wrote:
> >> > Hello,
> >> >
> >
On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser wrote:
> # da...@fetter.org / 2009-12-31 08:04:58 -0800:
>> On Thu, Dec 31, 2009 at 10:52:20AM +0100,
>> neuhauser+pgsql-general#postgresql@sigpipe.cz wrote:
>> > Hello,
>> >
>> > this fails with "duplicate key value":
>> >
>> > CREATE TABLE
# da...@fetter.org / 2009-12-31 08:04:58 -0800:
> On Thu, Dec 31, 2009 at 10:52:20AM +0100,
> neuhauser+pgsql-general#postgresql@sigpipe.cz wrote:
> > Hello,
> >
> > this fails with "duplicate key value":
> >
> > CREATE TABLE x (
> > i INT NOT NULL UNIQUE
> > );
> > INSERT
On Thu, Dec 31, 2009 at 10:52:20AM +0100,
neuhauser+pgsql-general#postgresql@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;
>
>
2009/12/31 neuhauser+pgsql-general#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?
Sure. 8.5 (curre
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@postgr
17 matches
Mail list logo
