The following bug has been logged on the website:
Bug reference: 13224
Logged by: Christian Ullrich
Email address: chris@chrullrich.net
PostgreSQL version: 9.4.1
Operating system: Windows
Description:
According to the manual, ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE
should work. It does not, according to the script below.
-- Demonstrates bug related to FK constraints ALTERed to DEFERRABLE.
CREATE TABLE master (
id INTEGER PRIMARY KEY
);
CREATE TABLE detail (
id_master INTEGER REFERENCES master (id),
val TEXT,
PRIMARY KEY (id_master)
);
-- Show the constraint.
SELECT * FROM pg_constraint WHERE conname = 'detail_id_master_fkey';
INSERT INTO master VALUES (1);
INSERT INTO detail VALUES (1, 'one');
-- Fail to change the primary key on the master table (expected).
BEGIN;
UPDATE master SET id = 2 WHERE id = 1;
ROLLBACK;
-- Make the constraint deferrable.
ALTER TABLE detail ALTER CONSTRAINT detail_id_master_fkey DEFERRABLE
INITIALLY IMMEDIATE;
-- Show the constraint. Note condeferrable = true.
SELECT * FROM pg_constraint WHERE conname = 'detail_id_master_fkey';
-- Fail again (unexpected).
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE master SET id = 2 WHERE id = 1;
ROLLBACK;
-- Recreate the constraint as deferrable.
ALTER TABLE detail DROP CONSTRAINT detail_id_master_fkey;
ALTER TABLE detail ADD CONSTRAINT detail_id_master_fkey FOREIGN KEY
(id_master) REFERENCES master (id) DEFERRABLE INITIALLY IMMEDIATE;
-- Show the constraint. Note the record is identical to the one from
-- the previous query.
SELECT * FROM pg_constraint WHERE conname = 'detail_id_master_fkey';
-- Succeed this time (expected).
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE master SET id = 2 WHERE id = 1;
UPDATE detail SET id_master = 2 WHERE id_master = 1;
COMMIT;