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.

Answer 2

I agree with others that the right way to do it is in the right order - but there are just times when that is not a feasible option and something easier is needed to get the job done within the time budget.

In case this helps anyone, I made a procedure that will automate adding the deferred option to all FKs so that the

SET CONSTRAINTS ALL DEFERRED;

command will work. Use it only as necessary of course.

DO
$$
DECLARE
    temp_rec RECORD;
    sql_exe TEXT;
BEGIN

sql_exe := $sql$
ALTER TABLE %1$s ALTER CONSTRAINT %2$s DEFERRABLE;
$sql$
;

FOR temp_rec IN 

(select constraint_name, table_name from information_schema.table_constraints where constraint_type = 'FOREIGN KEY')

LOOP
    EXECUTE format(sql_exe, temp_rec.table_name, temp_rec.constraint_name);
END LOOP;

END;
$$
LANGUAGE plpgsql
;