Skip to content
Advertisement

Order of cascaded deletes in postgres

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:

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

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

When I delete the super_function:

I get this error:

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?

Advertisement

Answer

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:

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.

  1. Then I created an inherited table “function_deleted”:

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

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

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)

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

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

  1. Now I create the “function_inclusion” table the same as before:
  1. And the trigger function and trigger for this table:
  1. Now when I create the functions and function-inclusions:

and then delete one of the functions:

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


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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement