postgresql, deferred constraint not working as I expect-CodePudding

I'm struggling to understand the way DEFERRED constraints work in postgres (v13 if that matters).

I have the following schema and data:

CREATE TEMP TABLE t1 (
  id int,
  CONSTRAINT unique_id PRIMARY KEY (id)
);

CREATE TEMP TABLE t2 (
  id int,
  ref int,
  CONSTRAINT fk FOREIGN KEY (ref) REFERENCES t1 (id) ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO t1 VALUES (1);
INSERT INTO t2 (id,ref) VALUES (1,1);

Then I execute the instructions below:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

DELETE FROM t1;
INSERT INTO t1 VALUES (1);
COMMIT;

Because constraints are deferred, I would expect the ON DELETE SET NULL to trigger at the end of the transaction and preserve the link from t1 to t2. Yet it does not.

SELECT * FROM t2;
 id | ref 
---- -----
  1 |    
(1 row)

What am I missing here ?

CodePudding user response：

DEFERRABLE only means that the check if no row violates the constraint is postponed until the transaction is committed. It does not mean that the effect of the DML that you run, is postponed until the end of the transaction.

In your example at the end of the transaction no row in t2 violates the foreign key constraint. So the COMMIT is successful.

Additionally: the DELETE FROM t1 will set all ref columns to NULL in the table t2 that contained the deleted IDs. If you then insert a new row into t1, how should Postgres know which of the (possibly millions) of ref columns that are NULL to reset to the initial value?