sql - SET CONSTRAINTS ALL DEFERRED not working as expected

Question

In a PostgreSQL 9.3 database, if I define tables a and b as follows:

CREATE TABLE a(i integer);
ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i);
CREATE TABLE b(j integer);
ALTER TABLE b add CONSTRAINT fkey_ij FOREIGN KEY (j)
      REFERENCES a (i) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;
INSERT INTO a(i) VALUES(1);

And then do the following:

START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;

It produces the error below. Why is SET CONSTRAINTS not having the desired effect?

ERROR: insert or update on table "b" violates foreign key constraint "fkey_ij"
SQL state: 23503 Detail: Key (j)=(2) is not present in table "a".

Answer 1

Only DEFERRABLE constraints can be deferred.

Let me suggest superior alternatives first:

1. INSERT in order

Reverse the sequence of the INSERT statements and nothing needs to be deferred. Simplest and fastest - if at all possible.

2. Single command

Do it in a single command. Then still nothing needs to be deferred, as non-deferrable constraints are checked after each command and CTEs are considered to be part of single command:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(2)
   )
INSERT INTO a(i) VALUES(2);

While being at it, you can reuse the values for the first INSERT; safer / more convenient for certain cases or multi-row inserts:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(3)
   RETURNING j
   )
INSERT INTO a(i)
SELECT j FROM ins1;

But I need deferred constraints! (Really?)

ALTER TABLE b ADD CONSTRAINT fkey_ij FOREIGN KEY (j)
   REFERENCES a (i) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;  -- !!!

Then your original code works (a bit slower, as deferred constraints add cost).

db<>fiddle here

Related:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

---

My original answer quoted the manual:

Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable.

But that was misleading as it only applies to "referential actions", i.e. what happens ON UPDATE or ON DELETE to rows in the referenced table. The case at hand is not one of those - as @zer0hedge pointed out.