The following bug has been logged online: Bug reference: 5654 Logged by: Daniel Howard Email address: cheesero...@yahoo.com PostgreSQL version: 8.4.4 Operating system: Linux (Ubuntu 10.04.1) Description: Deferred Constraints don't work Details:
The command SET CONSTRAINTS ALL DEFERRED seems to have no effect. According to the manual here: http://www.postgresql.org/docs/8.4/interactive/sql-set-constraints.html If a constraint is defined as deferrable, then you can instruct postgres to wait until the end of a transaction block before checking the constraint. This is supposed to work for foreign key constraints. The simple test case below demonstrates that postgres ignores the set constraint command and checks the constraint in the middle of a transaction. -- Setup two tables, users and items. One user can have many items. CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL); --NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id" --NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" --CREATE TABLE INSERT INTO users (id, name) VALUES (1,'Daniel'); --INSERT 0 1 CREATE TABLE items (id serial PRIMARY KEY, user_id integer NOT NULL REFERENCES users ON DELETE RESTRICT DEFERRABLE, itemname text); --NOTICE: CREATE TABLE will create implicit sequence "items_id_seq" for serial column "items.id" --NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "items_pkey" for table "items" --CREATE TABLE INSERT INTO items (user_id, itemname) VALUES (1,'hat'); --INSERT 0 1 -- -- Expect the following to fail because of the foreign key constraint DELETE FROM users; --ERROR: update or delete on table "users" violates foreign key constraint "items_user_id_fkey" on table "items" --DETAIL: Key (id)=(1) is still referenced from table "items". -- -- Try it in a transaction with the constraint deferred BEGIN; --BEGIN SET CONSTRAINTS ALL DEFERRED; --SET CONSTRAINTS -- This time it should work, because the constraint shouldn't be checked until the end of the transaction DELETE FROM users; --ERROR: update or delete on table "users" violates foreign key constraint "items_user_id_fkey" on table "items" --DETAIL: Key (id)=(1) is still referenced from table "items". ROLLBACK; --ROLLBACK -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
