Re: DELETE CASCADE

On Thu, Jun 3, 2021 at 4:48 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Thu, Jun 3, 2021 at 1:49 PM David Christensen <
> david(dot)christensen(at)crunchydata(dot)com> wrote:
>
>> 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.
>>
>
> ON DELETE NO ACTION constraints become ON DELETE CASCADE constraints - ON
> DELETE SET NULL constraints are ignored, and not possible to emulate via
> this feature.
>

I have not tested this part per se (which clearly I need to expand the
existing test suite), but my reasoning here was that ON DELETE SET
NULL/DEFAULT would still be applied with their defined behaviors (being
that we're still calling the underlying RI triggers using SPI) with the
same results; the intent of this feature is just to suppress the RESTRICT
action and cascade the DELETE to all tables (on down the chain) which would
normally block this, without having to manually figure all the dependencies
which can be inferred by the database itself.

> 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.
>>
>
> Once...but I agreed.
>

Heh.

> That said, I'm happy to quibble about the specific approach to be taken;
>> I've written this based on the most straightforward way I could come up
>> with to accomplish this, but if there are better directions to take to get
>> the equivalent functionality I'm happy to discuss.
>>
>>
> This behavior should require the same permissions as actually creating an
> ON DELETE CASCADE FK on the cascaded-to tables. i.e., Table Owner role
> membership (the requirement for FK permissions can be assumed by the
> presence of the existing FK constraint and being the table's owner).
>

I'm not sure if this would be overly prohibitive or not, but if you're the
table owner this should just work, like you point out. I think this
restriction could be fine for the common case, and if there was a way to
hint if/when this failed to cascade as to the actual reason for the failure
I'm fine with that part too. (I was assuming that DELETE permission on the
underlying tables + existence of FK would be enough in practice, but we
could definitely tighten that up.)

> Having the defined FK behaviors be more readily changeable, while not
> mitigating this need, is IMO a more important feature to implement. If
> there is a reason that cannot be implemented (besides no one has bothered
> to take the time) then I would consider that reason to also apply to
> prevent implementing this work-around.
>

Agreed that this would be a nice feature to have too; noone wants to break
FK consistency to change things or require a rescan of a valid constraint.