Re: BUG #5606: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the same

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.
***************************************************************************=
***