Re: Referential Integrity Checks with Statement-level Triggers

Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:

> 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.

I thought of a trigger that still fires for each row, but most of the time it
only stores the row deleted into a tuplestore of the appropriate lifespan. The
queries that you proposed would only be executed if the tuplestore contains
given amount of tuples or if the last row of the current statement has been
stored.

> 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.

Constraint-specific tuplestore can make some things easier, but if table has
both constraints and (non-constraint) triggers which use the transition
tables, then the tuples will have to be stored in both tuplestores. On the
other hand, using the same tuplestore for both constraint and non-constraint
triggers is difficult because deferred constraint triggers need to see rows
added by all statements while the non-constraint triggers should only see rows
of the current statement.

In order to avoid per-row calls of the constraint trigger functions, we could
try to "aggregate" the constraint-specific events somehow, but I think a
separate queue would be needed for the constraint-specific events.

In general, the (after) triggers and constraints have too much in common, so
separation of these w/o seeing code changes is beyond my imagination.

--
Antonin Houska
https://www.cybertec-postgresql.com