Order of cascaded deletes in postgres

Question

I encountered a problem which makes me suspect Postgres deletes rows from dependent tables (ON DELETE CASCADE) only after it already deleted the original row.

I have these tables:

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'))
);

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)
);

I created a trigger (before delete) on the function_inclusion table:

CREATE OR REPLACE FUNCTION trg_function_inclusion_del_bef()
RETURNS trigger AS
$func$
DECLARE
    function_type VARCHAR(15);
BEGIN
    SELECT type INTO function_type FROM function WHERE id = OLD.super_function;
    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;

DROP TRIGGER IF EXISTS function_inclusion_delete_before ON function_inclusion CASCADE;

CREATE TRIGGER function_inclusion_delete_before
BEFORE DELETE ON function_inclusion
FOR EACH ROW 
EXECUTE PROCEDURE trg_function_inclusion_del_bef();

Let's say I have 2 functions and a function_inclusion:

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');

When I delete the super_function:

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

I get this error:

NOTICE:  function_type: <NULL>
Query 1 ERROR: ERROR:  The function abcf3dbc-9433-4b73-b9c1-f00745dc1175 doesn't have a correct type
CONTEXT:  PL/pgSQL function trg_function_inclusion_del_bef() line 13 at RAISE
SQL statement "DELETE FROM ONLY "public"."function_inclusion" WHERE $1 OPERATOR(pg_catalog.=) "super_function""

So it seems like the function has already been deleted and I cannot access it anymore from my trigger on function_inclusion.

I tried to find more information about 'ON DELETE CASCADE', but everywhere I read it only says that 'the referencing rows are automatically deleted', no mention of which get deleted first, the referencing rows or the referenced row.

Does postgres first delete the original (referenced) row before it deletes rows in dependent (referencing) tables? And if so, how can I implement the same thing without having to store redundant data in my function_inclusion table?

score 2 · Accepted Answer · answered Jun 28 '20 at 21:08

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)

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.

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".

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)

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)

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)
);

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();

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".

Order of cascaded deletes in postgres

1 Answers1