Re: DELETE CASCADE

On Mon, Jun 7, 2021 at 2:54 AM Peter Eisentraut <
peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:

> On 05.06.21 14:21, David Christensen wrote:
> >
> >> On Jun 5, 2021, at 2:30 AM, Peter Eisentraut <
> peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
> >>
> >> On 03.06.21 22:49, David Christensen 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. 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 think, if we think this is useful, the other way around would also be
> useful: Override a foreign key defined as ON DELETE CASCADE to behave as
> RESTRICT for a particular command.
> >
> > I am not opposed to this, but I am struggling to come up with a use
> case. Where would this be useful?
>
> If you suspect a primary key row is no longer used, you want to delete
> it, but don't want to accidentally delete it if it's still used.
>

Okay, I can see that.

> I sense more complicated concurrency and permission issues, however.
>

Assuming this happens in the same transaction, wouldn't this just work? Or
are you thinking there needs to be some sort of predicate lock to prevent a
concurrent add of the referencing record in the FK table?

David