The following bug has been logged online:
Bug reference: 5654
Logged by: Daniel Howard
Email address: cheeserolls@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