Re: Referential Integrity Checks with Statement-level Triggers

>
>
> While the idea to use the transition table is good, this approach probably
> requires the trigger engine (trigger.c) to be adjusted, and that in a
> non-trivial way.
>

It probably does. Several people with advanced knowledge of trigger.c
expressed a desire to rebuild trigger.c from the ground up, and with it
create case-specific tuplestores for handling referential integrity
constraints, which would be lighter than either the transition tables or
the per-row invocation of a trigger. After all, we need a RI check to
happen, we don't need it to happen *through a trigger function*.

I'm also not sure if it's o.k. that performance related patch potentially
> makes performance worse in some cases. If FK violations are checked at
> statement boundary, the wasted effort / time can (at least in theory) be
> high
> if early rows violate the FK.
>

That concern was also expressed with varying levels of alarm in their
voices.

Have you considered bulk processing of individual rows by row-level trigger?
> For IMMEDIATE constraints we'd have to ensure that the trigger is notified
> that the current row is the last one from the current query, but that might
> not be difficult.
>

I'm not sure I understand what you're suggesting, but if it keeps the
overhead of one trigger firing per row deleted, then it doesn't seem like
much of a win.

Given that this patch has been punted to v13, I'd like to instead look at
how we might go about building up the transition tuplestores for the
specific purpose of doing the RI checks, not just deletes, and executing
those at the appropriate time, rather than trying to make our needs fit
into trigger form.