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.
