Re: DELETE CASCADE - Mailing list pgsql-hackers

From Isaac Morland
Subject Re: DELETE CASCADE
Date
Msg-id CAMsGm5d00a4vjrbtRN2O+2Jegs8ZBkTEB1_+rvD3oUTaP1ZAVA@mail.gmail.com
Whole thread Raw
In response to Re: DELETE CASCADE  (Peter Eisentraut <peter.eisentraut@enterprisedb.com>)
List pgsql-hackers
On Sat, 5 Jun 2021 at 03:30, Peter Eisentraut <peter.eisentraut@enterprisedb.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.

This is not as obviously useful as the other, but might conceivably still have applications.

We would need to be very careful about permissions. This is a substitute for checking whether there are any matching rows in the referring tables and throwing an error manually in that case. My immediate reaction is that this should require SELECT permission on the referring tables. Or to be more precise, SELECT permission on the foreign key columns in the referring tables.

pgsql-hackers by date:

Previous
From: David Christensen
Date:
Subject: Re: DELETE CASCADE
Next
From: David Christensen
Date:
Subject: Re: DELETE CASCADE