PostgreSQL "Deferred Deletion" still faces a delete limit

Question

I want to delete rows from two tables that are dependent on each other through a set of pending constraints. To simplify this post, I mocked a simple DB schema.

I hope to delete records from the "delete_from_me" table inside the SQL / DB Patch transaction. The trick I want to remove based on the selection from the second constraints_table is before I lose the link.

Here is 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 are some examples of the data I just said:

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 is 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 do this manually, I get 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".

, , , , ?

Answer 1

ON DELETE NO ACTION DEFERRABLE ON DELETE RESTRICT DEFERRABLE. RESTRICT NO ACTION , , DEFERRABLE.

CREATE TABLE:

, NO ACTION, , .

, RESTRICT.

NO ACTION RESTRICT:

, , . , , . .

RESTRICT

, , . , NO ACTION, , .

, NO ACTION RESTRICT, NO ACTION . - , , .