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_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:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Verzonden: vrijdag 6 augustus 2010 16:14
Aan: Frank Heikens
CC: pgsql-bugs(at)postgresql(dot)org
Onderwerp: Re: [BUGS] BUG #5606: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the same

"Frank Heikens" <f(dot)heikens(at)anva(dot)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.
******************************************************************************