SET CONSTRAINTS ALL DEFERRED not working as expected
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
;
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.