Thread: 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;

chris@chrullrich.net writes:
> According to the manual, ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE
> should work. It does not, according to the script below.

Hm.  It works fine if you cause a reconnect (\c) after the ALTER TABLE.
I suspect the problem is that ATExecAlterConstraint forces a relcache
inval on the named table (detail), but neglects to do so on the other
table (master).  The session's cached pg_trigger records for master thus
still say the triggers are nondeferrable.

            regards, tom lane