Re: DELETE CASCADE - Mailing list pgsql-hackers
| From | David Christensen |
|---|---|
| Subject | Re: DELETE CASCADE |
| Date | |
| Msg-id | CAOxo6XJbKQVmsiHijYYAGH=7N4Z1o98Ypd0NeNbdumMdjCq8ow@mail.gmail.com Whole thread Raw |
| In response to | Re: DELETE CASCADE ("David G. Johnston" <david.g.johnston@gmail.com>) |
| List | pgsql-hackers |
On Thu, Jun 3, 2021 at 4:48 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Jun 3, 2021 at 1:49 PM David Christensen <david.christensen@crunchydata.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.
pgsql-hackers by date: