Hi Tom,
The wikipage says
> a deferrable constraint CAN be checked at the end of a
> transaction. You still have to ask PostgreSQL to defer it.
http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRABLE_UNIQUE_CONSTRAINTS
I don't have to ask PostgreSQL to defer, it works in the second test as well in
the third test without any changes. I guess the example in the wiki isn't
correct, right? A single UPDATE touching all records will check the uniqueness
after the complete update, not right after each update of a row as it does on
non-deferrable constraints.
This one fails, as it should:
CREATE TABLE test (a int primary key deferrable, b int )
INSERT INTO test values (1,1),(2,2);
BEGIN;
UPDATE test SET a = a+1 WHERE b = 1; -- FAIL: ERROR: duplicate key
value violates unique constraint "test_pkey"
UPDATE test SET a = a+1 WHERE b = 2;
COMMIT;
And this one works fine, as it should:
CREATE TABLE test (a int primary key deferrable, b int )
INSERT INTO test values (1,1),(2,2);
BEGIN;
SET constraints ALL deferred;
UPDATE test SET a = a+1 WHERE b = 1;
UPDATE test SET a = a+1 WHERE b = 2;
COMMIT;
And this one works fine as well, and got me confused:
CREATE TABLE test (a int primary key deferrable, b int )
INSERT INTO test values (1,1),(2,2);
UPDATE test set a = a+1; -- single UPDATE statement, only checks after all
updates, didn't see it coming.
Maybe someone could make this clear in the manual and wiki?
Regards,
Frank Heikens
-----Oorspronkelijk bericht-----
Van: Tom Lane [mailto:t...@sss.pgh.pa.us]
Verzonden: vrijdag 6 augustus 2010 16:14
Aan: Frank Heikens
CC: pgsql-bugs@postgresql.org
Onderwerp: Re: [BUGS] BUG #5606: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED
are the same
"Frank Heikens" <f.heik...@anva.nl> writes:
> Description: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the
> same
This test proves no such thing. You are supposing that a deferrable
unique index has the exact same behavior as a nondeferrable one.
Actually, a DEFERRABLE IMMEDIATE index enforces its checks as of the end
of the statement, which is per spec.
regards, tom lane
***************************DISCLAIMER***********************************
Deze e-mail is uitsluitend bestemd voor de geadresseerde(n). Verstrekking aan
en gebruik door anderen is niet toegestaan. ANVA bv sluit iedere
aansprakelijkheid uit die voortvloeit uit electronische verzending.
This e-mail is intended exclusively for the addressee(s), and may not be passed
on to, or made available for use by any person other than the addressee(s).
ANVA bv rules out any and every liability resulting from any electronic
transmission.
******************************************************************************
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
