BUG #13224: Foreign key constraints cannot be changed to deferrable

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;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs