Cascade delete on recursive relationship

I have a recursive resource related to itself.

I can destroy a record with reference :parent, index?: true, on_delete: :delete and all nested children are deleted by PostgreSQL.

I later found cascade_destroy. What would it do differently? Would it run the destroy action for all nested children records? Is it useful for soft destroys?

    destroy :destroy do
      primary? true
      change cascade_destroy(:children, action: :destroy)
    end

I tried removing on_delete: :delete and using it, but it didn’t work?

Do I need on_delete: :delete or deferrable: true to use cascade_destroy?

{:error,
 %Ash.Error.Invalid{
   errors: [
     %Ash.Error.Changes.InvalidAttribute{
       field: :id,
       message: "would leave records behind",
       private_vars: [
         constraint: "entities_parent_id_fkey",
         constraint_type: :foreign_key,
         detail: "Key (id)=(02df79f3-8b30-4288-bb50-ac446cc97f41) is still referenced from table \"entities\"."
       ],
       value: nil,
       splode: Ash.Error,
       bread_crumbs: [],
       vars: [],
       path: [],
       stacktrace: #Splode.Stacktrace<>,
       class: :invalid
     }
   ]
 }}

The docs for cascade_destroy explain this - it’s useful when you need to run business logic on the records being destroyed, it will run a destroy action for each of the records being destroyed, so you can hook in your own logic.

If a database on delete will do what you need, use that instead as it will be much faster!

If you want to run cascade_destroy as an after_action (the default) then you’ll need deferrable: :initially on the database reference, otherwise you get errors like the one you’ve seen. Alternatively, you can use a before_action (cascade_destroy(:stuff, after_action?: false)) and you won’t need to change the foreign key.

2 Likes

Would this N+1 on a recursive foreign key? That is to say, would it perform the recursion in Elixir instead of in SQL?

Yes, it would. Although assuming the actions can be done in bulk, N will be “depth” not “number of records * depth”

2 Likes

Yeah, I added the clarification because I realized I was abusing the term :slight_smile:

I would imagine it would be more annoying to generate a recursive query, but I suppose it could be done. Not sure how portable recursive CTEs are though (I’ve only done them in Postgres).