Deferred Constraints - Check at COMMIT

Day 29 CHECK and Day 31 EXCLUDE reject illegal rows at the statement boundary by default. Today: some multi-statement transactions must temporarily break a foreign key — then fix it before COMMIT. PostgreSQL’s answer is DEFERRABLE.

You ETL a parent and a child. The dump file lists children first. With a normal FK, the first child insert dies: “parent doesn’t exist yet.” You could rewrite every loader forever — or declare the FK deferrable so the check waits until the end of the transaction. Integrity still holds. Timing changes.

The problem: order vs integrity

Default foreign keys are NOT DEFERRABLE. PostgreSQL checks the reference as the statement runs. If the parent is missing right now, the statement fails — even if the next line in the same transaction would insert that parent.

-- Default FK = NOT DEFERRABLE (immediate)
CREATE TABLE parent (id int PRIMARY KEY);
CREATE TABLE child (
  id int PRIMARY KEY,
  parent_id int NOT NULL
    REFERENCES parent(id)
);

BEGIN;
INSERT INTO child  VALUES (1, 100);  -- FAILS here
INSERT INTO parent VALUES (100);     -- never reached
COMMIT;

The second insert never runs. The transaction is aborted. Your data was going to be consistent at COMMIT — but the engine never got there. That is the loader trap: file order ≠ FK dependency order.

Worked example: children-first dump

Imagine a CSV export that walks leaf tables first (common in dump tools that sort by table name, not dependency graph):

-- children.csv lands first in the import script
--  id,parent_id
--  1,100
-- parents.csv second
--  id
--  100

Under an immediate FK you must either (1) reorder the script by hand forever, or (2) drop/disable FKs during load (and pray the reload is clean). Deferrable FKs are the third option: keep the constraint, change when it fires.

The mechanism: DEFERRABLE INITIALLY DEFERRED

CREATE TABLE child (
  id int PRIMARY KEY,
  parent_id int NOT NULL,
  CONSTRAINT child_parent_fk
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO child  VALUES (1, 100);  -- allowed for now
INSERT INTO parent VALUES (100);     -- repairs the invariant
COMMIT;                              -- FK checked here → OK

Read it as: illegal mid-transaction is fine; illegal at COMMIT is not (Day 10). An orphan still dies — just later.

Three knobs (and SET CONSTRAINTS)

Declaration	Default check time	Can you change mid-tx?
NOT DEFERRABLE (default)	Each statement	No — cannot wait
DEFERRABLE INITIALLY IMMEDIATE	Each statement	Yes — SET CONSTRAINTS … DEFERRED
DEFERRABLE INITIALLY DEFERRED	COMMIT	Yes — SET CONSTRAINTS … IMMEDIATE to check early

BEGIN;
INSERT INTO child VALUES (1, 50);
SET CONSTRAINTS child_parent_fk IMMEDIATE;  -- check NOW
-- fails if parent 50 is still missing
INSERT INTO parent VALUES (50);
COMMIT;

SET CONSTRAINTS ALL DEFERRED / IMMEDIATE is the transaction-local switch for every deferrable constraint you own. Non-deferrable constraints ignore it.

Why cycles need deferral

Self-referential edges 1 → 2 and 2 → 1 cannot be inserted under an immediate self-FK: the first row references a missing key. Deferred FK lets both land, then validates both references at COMMIT.

CREATE TABLE edge (
  a int PRIMARY KEY,
  b int NOT NULL,
  CONSTRAINT edge_fk FOREIGN KEY (b) REFERENCES edge(a)
    DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO edge VALUES (1, 2);
INSERT INTO edge VALUES (2, 1);
COMMIT;  -- both FKs valid together

Same pattern shows up as mutual “spouse_id”, org charts with temporary cycles during merge, or any graph edge table where both endpoints arrive in one transaction.

CHECK vs deferrable EXCLUDE

Day 29 CHECK is row-local and is not deferrable. Day 31 EXCLUDE is multi-row and immediate by default, but PostgreSQL exclusion constraints can be declared DEFERRABLE INITIALLY IMMEDIATE or DEFERRABLE INITIALLY DEFERRED, just like the timing choices above. Deferral is a transaction-timing feature on supported constraints; it answers “when may the invariant be broken?” rather than removing the invariant.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
  room_id int,
  during tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
    DEFERRABLE INITIALLY DEFERRED
);
-- Overlaps may exist temporarily in a transaction, but not at COMMIT.

Tool	Sees	Typical when	Job
CHECK (Day 29)	One row	Statement	Local shape / range
EXCLUDE (Day 31)	Row pairs	Statement by default; COMMIT if declared deferred	No double-book / overlap by the configured check time
FK immediate	Reference	Statement	Parent must exist now
FK deferred	Reference	COMMIT (or SET IMMEDIATE)	Parent must exist by finish

Benchmark — integrity first (PostgreSQL 18.4)

Podman til-postgres. Correctness is the product claim. Latency is secondary (in-server clock_timestamp, 500 pairs × 30 runs, median). The standalone result artifact is not included in this repository.

Scenario	Result
IMMEDIATE FK: child before parent	Fails mid-statement; 0 rows land
DEFERRED FK: child then parent, COMMIT	OK — 1 child + 1 parent
DEFERRED FK: orphan child, COMMIT	Fails at COMMIT; 0 rows land
SET CONSTRAINTS … IMMEDIATE before parent	Fails when set immediate
Self-FK cycle 1↔2 DEFERRED	COMMIT OK (2 rows)
Self-FK cycle IMMEDIATE	Fails
Bulk 500 pairs parent-first median	6.146 ms (in-server)
Bulk 500 pairs child-first (deferred) median	3.467 ms (in-server) · ratio 0.564×

Absolute ms are machine-local and tiny — both paths are fast. Do not sell “deferral is always faster.” Sell: you can insert in the order your file requires, and COMMIT still refuses permanent orphans.

What breaks?

• Assuming every FK can be deferred — default is NOT DEFERRABLE. You must declare it on CREATE/ALTER.
• Leaving orphans “for later” outside the transaction — deferral is not a soft delete of integrity; COMMIT still fails and nothing lands.
• Long transactions with deferred checks — errors surface late; mid-tx debugging is harder; locks and bloat last longer. Prefer short txs.
• Using deferral to skip app-level ordering forever — loaders and graph edges benefit; interactive signup usually wants immediate “parent missing” feedback.
• Confusing with Day 30 idempotency — retries need keys; temporary order breaks need deferral. Different jobs.
• Assuming EXCLUDE is always immediate — it is immediate by default, but supports DEFERRABLE [INITIALLY IMMEDIATE | INITIALLY DEFERRED]. Choose late errors only when temporary overlap is valid inside the transaction.

How it connects

• Day 10 (Transactions): illegal state never commits — deferral only delays the check to the same finish line.
• Day 15 (JOINs / FKs): FKs are integrity tools; indexing FKs is separate. Timing of the check is today’s lever.
• Day 29 (CHECK): one-row rules fire with the row. Deferral is multi-statement timing.
• Day 31 (EXCLUDE): multi-row conflict rules are immediate by default, but a deferrable EXCLUDE can permit a temporary conflict until COMMIT.
• Day 30 (Idempotency): safe retries across attempts; deferral is safe reordering inside one tx.

Quiz