tgdeferrable-tginitdeferred-not-restored.sql

-- Clean up existing tables
DROP TABLE IF EXISTS fktable;
DROP TABLE IF EXISTS pktable;

-- 1. Create tables for testing
CREATE TABLE pktable (
    id INT PRIMARY KEY
);

CREATE TABLE fktable (
    id INT PRIMARY KEY,
    parent_id INT,
    -- Define a foreign key with DEFERRABLE INITIALLY DEFERRED
    CONSTRAINT fk_test FOREIGN KEY (parent_id) REFERENCES pktable(id)
    DEFERRABLE INITIALLY DEFERRED
);

-- 2. Check initial state (Both should be 't')
SELECT 
    conname, 
    condeferrable,
    condeferred
FROM pg_constraint 
WHERE conname = 'fk_test';

SELECT 
    tgname, 
    tgdeferrable,
    tginitdeferred
FROM pg_trigger 
WHERE tgname LIKE 'RI_ConstraintTrigger%';

-- 3. Toggle NOT ENFORCED then back to ENFORCED
ALTER TABLE fktable ALTER CONSTRAINT fk_test NOT ENFORCED;
ALTER TABLE fktable ALTER CONSTRAINT fk_test ENFORCED;

-- 4. Check state after toggle
-- Bug: pg_constraint remains 't', but pg_trigger is reset to 'f'
SELECT 
    conname, 
    condeferrable,
    condeferred
FROM pg_constraint 
WHERE conname = 'fk_test';

SELECT 
    tgname, 
    tgdeferrable,
    tginitdeferred
FROM pg_trigger 
WHERE tgname LIKE 'RI_ConstraintTrigger%';

-- 5. Verify actual behavior
-- If the bug exists, INSERT fails immediately even after SET CONSTRAINTS DEFERRED
BEGIN;
SET CONSTRAINTS fk_test DEFERRED;
INSERT INTO fktable (id, parent_id) VALUES (1, 999); 
-- Expected: No error here, delay verification until COMMIT.
-- Actual: Fails immediately if tgdeferrable is 'f'.
COMMIT;

-- Clean up
DROP TABLE IF EXISTS fktable;
DROP TABLE IF EXISTS pktable;