Re: DELETE CASCADE

On Thu, 3 Jun 2021 at 16:49, David Christensen <david.christensen@crunchydata.com> wrote:

Hi -hackers,

Presented for discussion is a POC for a DELETE CASCADE functionality, which will allow you one-shot usage of treating existing NO ACTION and RESTRICT FK constraints as if they were originally defined as CASCADE constraints.  I can't tell you how many times this functionality would have been useful in the field, and despite the expected answer of "define your constraints right in the first place", this is not always an option, nor is the ability to change that easily (or create new constraints that need to revalidate against big tables) always the best option.

I would sometimes find this convenient. There are circumstances where I don't want every DELETE to blunder all over the database deleting stuff, but certain specific DELETEs should take care of the referencing tables.

An additional syntax to say "CASCADE TO table1, table2" would be safer and sometimes useful in the case where I know I want to cascade to specific other tables but not all (and in particular not to ones I didn't think of when I wrote the query); I might almost suggest omitting the cascade to all syntax (or maybe have a separate syntax, literally "CASCADE TO ALL TABLES" or some such).

What happens if I don't have delete permission on the referencing table? When a foreign key reference delete cascades, I can cause records to disappear from a referencing table even if I don't have delete permission on that table. This feels like it's just supposed to be a convenience that replaces multiple DELETE invocations but one way or the other we need to be clear on the behaviour.

Sidebar: isn't this inconsistent with trigger behaviour in general? When I say "ON DELETE CASCADE" what I mean and what I get are the same: whenever the referenced row is deleted, the referencing row also disappears, regardless of the identity or permissions of the role running the actual DELETE. But any manually implemented trigger runs as the caller; I cannot make the database do something when a table update occurs; I can only make the role doing the table update perform some additional actions.