Corey Huinker <corey.huin...@gmail.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
