Conditional Statement-Level Triggers

[wesleykendall]

Postgres row-level triggers allow for conditional execution based on the OLD/NEW rows. If the condition doesn't match, triggers aren't placed in the queue. Conditions are nice for expressing events, especially in libraries like django-pghistory.

However, row-level triggers come at a cost of O(N) executions. In the case of django-pghistory, this means a bulk insert of 1000 rows can do 1000 inserts.

Since triggered SQL doesn't incur end-to-end network requests, an O(N) query here isn't as bad as one in the application, however, it still incurs a significant performance cost.

Normally one would use statement-level triggers, but Postgres doesn't have the ability to express conditions on statement-level triggers, making the boilerplate verbose. I'm planning to support this in the trigger definition layer in the following way:

1. Users still define triggers with conditions.
2. If it's a statement-level trigger, transition tables are automatically declared, and boilerplate is inserted into the trigger.
3. This boilerplate constructs the COND_NEW and COND_OLD variables, which can be used in queries over the conditional old/new rows. I.e. SELECT EXISTS (SELECT * FROM COND_NEW)

(note that this is just pseudocode)

My hope is that this will allow for easier conditional statement-level trigger writing. Once available, I plan to expose this option in pghistory, which could be a 10x type of performance improvement.

Let me know what you all think

[wesleykendall]

Version 4.15 supports creating conditional statement-level triggers. Docs at https://django-pgtrigger.readthedocs.io/en/4.15.0/statement/

This feature is being rolled out to pghistory in AmbitionEng/django-pghistory#197