PostgreSQL 'Deferrable Delete' still hits constraint on Delete

Question

I want to delete rows from two tables which have a dependence upon each other through a set of deferrable constraints. To simplify this post, I've mocked up a simple DB schema.

I'm hoping to remove entries from some table, 'delete_from_me', inside a SQL transaction/DB Patch. The catch is, I want to delete based on a select from a second table 'constraining_table' before I loose the link itself.

Here's a description of the two tables:

tab-quarantine=> \d delete_from_me
       Table "public.delete_from_me"
  Column   |       Type        | Modifiers 
-----------+-------------------+-----------
 id        | character varying | not null
 extension | character varying | not null
Indexes:
    "delete_from_me_pkey" PRIMARY KEY, btree (id)

tab-quarantine=> \d constraining_table 
   Table "public.constraining_table"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 image  | character varying | not null
 type   | character varying | not null
Foreign-key constraints:
    "constraining_table_image_fkey" FOREIGN KEY (image) REFERENCES delete_from_me(id)
         ON UPDATE CASCADE
         ON DELETE RESTRICT DEFERRABLE

Here's some sample data I just blatted in there:

tab-quarantine=> SELECT * FROM delete_from_me;
     id     | extension 
------------+-----------
 12345abcde | png
(1 row)

tab-quarantine=> SELECT * FROM constraining_table;
   image    |   type   
------------+----------
 12345abcde | select_me
(1 row)

And here goes my transaction:

BEGIN;
\set ON_ERROR_STOP 1
SET CONSTRAINTS ALL DEFERRED;
DELETE FROM delete_from_me WHERE id IN (
    SELECT image FROM constraining_table WHERE type = 'select_me'
);
DELETE FROM constraining_table WHERE type = 'select_me';
COMMIT;

This transaction fails. When I step through and do this manually, I'm presented with the following error message:

ERROR:  update or delete on table "delete_from_me" violates foreign key constraint "constraining_table_image_fkey" on table "constraining_table"
DETAIL:  Key (id)=(12345abcde) is still referenced from table "constraining_table".

This seems like a good candidate for a temporary table, however I'd like to know why it is that I can't delete in this order given the constraints should not be effective till the end of the transaction?

Answer 1

Use ON DELETE NO ACTION DEFERRABLE instead of ON DELETE RESTRICT DEFERRABLE. Using RESTRICT instead of NO ACTION forces the constraint to be non-deferrable, regardless of whether you apply the DEFERRABLE modifier.

This is in the fine print of the manual page for CREATE TABLE:

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

Obviously, the above caveat includes RESTRICT.

Following shortly after this sentence are the definitions of NO ACTION and RESTRICT:

NO ACTION

Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action.

RESTRICT

Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

As you can see, NO ACTION will behave identically to RESTRICT, except NO ACTION is deferrable. This is why I recommended it -- I think it's just what you're asking for.