Cascading Deletes in Postgresql

CASCADE DELETE just once

No. To do it just once you would simply write the delete statement for the table you want to cascade.

DELETE FROM some_child_table WHERE some_fk_field IN (SELECT some_id FROM some_Table);
DELETE FROM some_table;

postgresql on delete cascade

I guess you got misunderstanding. Try to delete row from TableY and corresponding rows from TableX will be cascade deleted. This option is indispensable when you have secondary related tables and would like to clean them all by deleting parent row from primary table without getting constraints violated or rubbish left.

Order of cascaded deletes in postgres

Just in case anyone else encounters the same problem I am posting the solution I found.

The problem was - as I suspected - that postgres first deletes the requested row itself before deleting rows from dependent tables (which have 'ON DELETE CASCADE' set).

The solution I found is a bit elaborate, implementing a soft-delete on the 'function' table (see Cascading Soft Delete)

1. First I added an extra field deleted_at to the "function" table:

CREATE TABLE IF NOT EXISTS function (
    id UUID PRIMARY KEY,
    level VARCHAR(4) NOT NULL CHECK (level IN ('ORG', 'DEP', 'GRP', 'SESS')),
    name VARCHAR(64) NOT NULL UNIQUE,
    type VARCHAR(15) NOT NULL CHECK (type IN ('SYSTEM', 'SYS-AUTO-ASSIGN', 'CUSTOM')),
    deleted_at TIMESTAMPTZ DEFAULT NULL
);

This table holds all "function" rows, including already (soft-)deleted rows.
From now on, we will need to use "SELECT FROM ONLY function" to select non-deleted rows.

2. Then I created an inherited table "function_deleted":

CREATE TABLE IF NOT EXISTS function_deleted () INHERITS(function);

Rows inserted into this table will also be found in the table "function".
To find rows that were deleted, we need to use "SELECT FROM function_deleted".

3. Then I created a generic trigger function for soft-deleting a row from any table:

CREATE OR REPLACE FUNCTION tr_soft_delete_row()
RETURNS TRIGGER AS 
$$
BEGIN
    IF (TG_OP = 'UPDATE' AND NEW.deleted_at IS NOT NULL) THEN
        EXECUTE format('DELETE FROM %I.%I WHERE id = $1', TG_TABLE_SCHEMA, TG_TABLE_NAME) USING OLD.id;
        RETURN OLD;
    END IF;
    IF (TG_OP = 'DELETE') THEN
        IF (OLD.deleted_at IS NULL) THEN
            OLD.deleted_at := timenow();
        END IF;
        EXECUTE format('INSERT INTO %I.%I SELECT $1.*', TG_TABLE_SCHEMA, TG_TABLE_NAME || '_deleted') USING OLD;
    END IF;
    RETURN OLD;
END;
$$ 
LANGUAGE plpgsql;

When called from a trigger at deletion of a row, the function sets the deleted_at field and inserts the row into the "(table)_deleted" table.

When called from a trigger at update of the deleted_at field, this function deletes the row (which will automatically become a soft-delete)

4. Then I created a trigger to call this soft-delete function:

CREATE TRIGGER _soft_delete_function
AFTER
    UPDATE OF deleted_at 
    OR DELETE
    ON function
FOR EACH ROW
EXECUTE PROCEDURE tr_soft_delete_row();

(the underscore in front of the trigger name ensures the trigger will be called before any other triggers)

5. Now I create the "function_inclusion" table the same as before:

CREATE TABLE IF NOT EXISTS function_inclusion (
    super_function UUID REFERENCES function (id) ON DELETE CASCADE,
    sub_function UUID REFERENCES function (id) ON DELETE CASCADE,
    UNIQUE (super_function, sub_function)
);

6. And the trigger function and trigger for this table:

CREATE OR REPLACE FUNCTION trg_function_inclusion_del_aft()
RETURNS trigger AS
$func$
DECLARE
    function_type VARCHAR(15);
BEGIN
    SELECT type INTO function_type FROM ONLY function WHERE id = OLD.super_function;
    -- if the function doesn't exist then it's because it was just deleted
    --> find it in table "function_deleted"
    IF NOT FOUND THEN
        SELECT type INTO function_type FROM function_deleted WHERE id = OLD.super_function 
        ORDER BY deleted_at DESC NULLS LAST LIMIT 1;
    END IF;
    RAISE NOTICE 'function_type: %', function_type;
    -- do stuff based on the function_type of the super_function
    CASE
        WHEN function_type = 'SYSTEM' OR function_type = 'SYS-AUTO-ASSIGN' THEN
            -- (do stuff)
        WHEN function_type = 'CUSTOM' THEN
            -- (do stuff)
        ELSE RAISE EXCEPTION 'The function % doesn''t have a correct type', OLD.super_function;
    END CASE;
    RETURN OLD;
END
$func$  
LANGUAGE plpgsql;

CREATE TRIGGER function_inclusion_delete_after
AFTER DELETE ON function_inclusion
FOR EACH ROW 
EXECUTE PROCEDURE trg_function_inclusion_del_aft();

7. Now when I create the functions and function-inclusions:

INSERT INTO function (id, level, name, type)
VALUES ('abcf3dbc-9433-4b73-b9c1-f00745dc1175', 'DEP', 'custom-function-1', 'CUSTOM');

INSERT INTO function (id, level, name, type)
VALUES ('360bde13-7953-49ed-a923-793b2d828d7e', 'DEP', 'custom-function-2', 'CUSTOM');

INSERT INTO function_inclusion (super_function, sub_function)
VALUES ('abcf3dbc-9433-4b73-b9c1-f00745dc1175', '360bde13-7953-49ed-a923-793b2d828d7e');

and then delete one of the functions:

DELETE FROM ONLY function WHERE id = 'abcf3dbc-9433-4b73-b9c1-f00745dc1175';

I now don't get an error, and I get the correct information in the raised notice (from the AFTER DELETE trigger on function_inclusion):

NOTICE:  function_type: CUSTOM
Query 1 OK: DELETE 1, 1 row affected

---

One drawback of this method is that now everywhere in my SQL code I need to remember to use "SELECT * FROM ONLY function" instead of "SELECT * FROM function".

How does one write a DELETE CASCADE for postgres?

In case you can't do what others have suggested:

begin;
delete from classification_item where transaction_id in (select id from "transaction" where slice_id = (select id from slice where name = 'my_slice'));
delete from "transaction" where slice_id in (select id from slice where name='my_slice');
delete from slice where name='my_slice';
commit;

How to list tables affected by cascading delete

Use pg_depend. Example:

create table master (id int primary key);
create table detail_1 (id int, master_id int references master(id) on delete restrict);
create table detail_2 (id int, master_id int references master(id) on delete cascade);

select pg_describe_object(classid, objid, objsubid)
from pg_depend 
where refobjid = 'master'::regclass and deptype = 'n';

                  pg_describe_object
------------------------------------------------------
 constraint detail_1_master_id_fkey on table detail_1
 constraint detail_2_master_id_fkey on table detail_2
(2 rows)

deptype = 'n' means:

DEPENDENCY NORMAL - A normal relationship between separately-created
objects. The dependent object can be dropped without affecting the
referenced object. The referenced object can only be dropped by
specifying CASCADE, in which case the dependent object is dropped,
too.

Use pg_get_constraintdef() to get constraint definitions:

select 
    pg_describe_object(classid, objid, objsubid), 
    pg_get_constraintdef(objid)
from pg_depend 
where refobjid = 'master'::regclass and deptype = 'n';

                  pg_describe_object                  |                       pg_get_constraintdef
------------------------------------------------------+------------------------------------------------------------------
 constraint detail_1_master_id_fkey on table detail_1 | FOREIGN KEY (master_id) REFERENCES master(id) ON DELETE RESTRICT
 constraint detail_2_master_id_fkey on table detail_2 | FOREIGN KEY (master_id) REFERENCES master(id) ON DELETE CASCADE
(2 rows)

---

To find the full chain of cascading dependencies we should use recursion and look into the catalog pg_constraint to get id of a dependent table.

with recursive chain as (
    select classid, objid, objsubid, conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    where refobjid = 'the_table'::regclass and deptype = 'n'
union all
    select d.classid, d.objid, d.objsubid, c.conrelid
    from pg_depend d
    join pg_constraint c on c.oid = objid
    join chain on d.refobjid = chain.conrelid and d.deptype = 'n'
    )
select pg_describe_object(classid, objid, objsubid), pg_get_constraintdef(objid)
from chain;

---

Related Topics