Re: DELETE CASCADE

On Wed, Jun 9, 2021 at 8:48 AM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wednesday, June 9, 2021, Peter Eisentraut <
> peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
>
>>
>> It might work, I'm just saying it needs to be thought about carefully. If
>> you have functionality like, delete this if there is no matching record
>> over there, you need to have the permission to check that and need to make
>> sure it stays that way.
>>
>>
> Which I believe the presence of an existing foreign key does quite
> nicely. Thus if the executing user is the table owner (group membership
> usually) and a FK already exists, the conditions for the cascade are
> fulfilled, including locking I would think, because that FK could have been
> defined to just do it without all this. We are effectively just
> temporarily changing that aspect of the foreign key - the behavior should
> be identical to on cascade delete.
>

I think Peter is referring to the DELETE RESTRICT proposed mirror behavior
in this specific case, not DELETE CASCADE specifically.

> I require convincing that there is a use case that requires laxer
> permissions. Especially if we can solve the whole changing of the cascade
> option without having to drop the foreign key.
>

This was my original feeling as well, though really if I was going to run
this operation it would likely already be the database owner or superuser,
so my desire to make this work in all situations is tempered with my desire
to just have the basic functionality available at *some* level. :-)

David