The following bug has been logged online:
Bug reference: 5606
Logged by: Frank Heikens
Email address: f.heikens@anva.nl
PostgreSQL version: PostgreSQL9.0b4
Operating system: WinXP
Description: DEFERRABLE and DEFERRABLE INITIALLY DEFERRED are the
same
Details:
Today I checked out the wiki and did some tests using the deferrable unique
constraints described at:
http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#DEFERRABLE_UN
IQUE_CONSTRAINTS
I copied/pasted the examples and to my surprise, DEFERRABLE and DEFERRABLE
INITIALLY DEFERRED work exactly the same, there is no need to use
CONSTRAINTS ALL DEFERRED; at all. The wiki and manual describe the need for
this, but no matter what, it always works the same. (see tests and results
below)
It's tested on a local machine, Windows XP, 32bit, using the
EnterpriseDB-installer:
PostgreSQL 9.0beta4, compiled by Visual C++ build 1500, 32-bit
-- Test 1
CREATE TABLE test (a int primary key);
INSERT INTO test values (1), (2);
UPDATE test set a = a+1;
-- Result: Error: duplicate key value violates unique constraint
"test_pkey"
-- This is Ok.
-- Test 2
CREATE TABLE test (a int primary key deferrable);
INSERT INTO test values (1),(2);
UPDATE test set a = a+1;
-- Result: Query returned successfully: 2 rows affected, 15 ms execution
time.
-- Not Ok, see the manual and wiki, should result in the same error as test
1.
-- Test 3
CREATE TABLE test (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);
INSERT INTO test values (1),(2);
UPDATE test set a = a+1;
-- Result: Query returned successfully: 2 rows affected, 31 ms execution
time.
-- This is Ok.
Each test was executed using it's own connection.
If you need more information, just let me know.
Kind regards,
Frank Heikens
PostgreSQL DBA
ANVA
Postbus 190, 3800 AD Amersfoort
Stadsring 201, 3817 BA Amersfoort
T: +31 (0)33 479 82 00
F: +31 (0)33 472 78 22
www.anva.nl