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_U=
NIQUE_CONSTRAINTS
I don't have to ask PostgreSQL to defer, it works in the second test as wel=
l in the third test without any changes. I guess the example in the wiki is=
n't correct, right? A single UPDATE touching all records will check the uni=
queness 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 =3D a+1 WHERE b =3D 1; -- FAIL: ERROR: duplic=
ate key value violates unique constraint "test_pkey"
UPDATE test SET a =3D a+1 WHERE b =3D 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 =3D a+1 WHERE b =3D 1;
UPDATE test SET a =3D a+1 WHERE b =3D 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 =3D a+1; -- single UPDATE statement, only checks after al=
l 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:tgl@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 DEFERR=
ED are the same
"Frank Heikens" <f.heikens@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 a=
an en gebruik door anderen is niet toegestaan. ANVA bv sluit iedere aanspra=
kelijkheid uit die voortvloeit uit electronische verzending.
This e-mail is intended exclusively for the addressee(s), and may not be pa=
ssed on to, or made available for use by any person other than the addresse=
e(s). ANVA bv rules out any and every liability resulting from any electron=
ic transmission.
***************************************************************************=
***
