Re: DELETE CASCADE

On Thu, Jun 3, 2021 at 4:15 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
wrote:

> On Thu, 3 Jun 2021 at 16:49, David Christensen <
> david(dot)christensen(at)crunchydata(dot)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).
>

I'm not fond of the syntax requirements for the explicitness here, plus it
seems like it would complicate the functionality of the patch (which
currently is able to just slightly refactor the RI triggers to account for
a single state variable, rather than do anything smarter than that). I do
understand the desire/need for visibility into what would be affected with
an offhand statement.

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

Did you test this and find a failure? Because it is literally using all of
the same RI proc code/permissions as defined I would expect that it would
just abort the transaction. (I am working on expanding the test suite for
this feature to allow for test cases like this, so keep 'em coming... :-))

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

Have you found a failure? Because all this is doing is effectively calling
the guts of the cascade RI routines, so no differences should occur. If
not, I'm not quite clear on your objection; can you clarify?

David