Re: Trigger violates foreign key constraint

On Mon, Oct 02, 2023 at 09:49:53AM -0400, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> > CREATE FUNCTION silly() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN RETURN NULL; END;';
> > CREATE TRIGGER silly BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTION silly();
>
> > The trigger function cancels the cascaded delete on "child", and we are left with
> > a row in "child" that references no row in "parent".
>
> Yes. This is by design: triggers operate at a lower level than
> foreign keys, so an ill-conceived trigger can break an FK constraint.
> That's documented somewhere, though maybe not visibly enough.
>
> There are good reasons to want triggers to be able to see and
> react to FK-driven updates,

I agree with that, but I also think it's a bug that other triggers can
invalidate the constraint, without even going out of their way to do so.
Ideally, triggers would be able to react, yet when all non-superuser-defined
code settles, the constraint would still hold. While UNIQUE indexes over
expressions aren't that strict, at least for those you need to commit the
clear malfeasance of redefining an IMMUTABLE function.

On Mon, Oct 02, 2023 at 12:02:17PM +0200, Laurenz Albe wrote:
> Perhaps it would be enough to run "RI_FKey_noaction_del" after
> "RI_FKey_cascade_del", although that would impact the performance.

Yes. A cure that doubles the number of heap fetches would be worse than the
disease, but a more-optimized version of this idea could work. The FK system
could use a broader optimization-oriented rewrite, to deal with the unbounded
memory usage and redundant I/O. If that happens, it could be a good time to
plan for closing the trigger hole.