Re: COMMIT TRIGGERs, take n+1

The attached file demonstrates how to create COMMIT, BEGIN, and even
session CONNECT TRIGGERs for PostgreSQL using PlPgSQL only, via normal
and CONSTRAINT TRIGGERs.

There have been many threads on the PG mailing lists about commit
triggers, with much skepticism shown about the possible semantics of
such a thing. Below we list use cases, and demonstrate reasonable,
useful, and desirable semantics.

The previous version of this could be defeated by using SET CONSTRAINTS
.. IMMEDIATE. This version detects this when it would cause commit
triggers to run too soon, and causes an exception to be raised. The
technique used to detect this could be used by anyone whose business
logic breaks when SET CONSTRAINTS .. IMMEDIATE is used.

There is one shortcoming of this implementation: it is inefficient
because it has to use FOR EACH ROW triggers under the hood, so if you
do 1,000 inserts, then 999 of the resulting internal trigger
procedure invocations will be unnecessary. These are FOR EACH ROW
triggers because that is the only level permitted for CONSTRAINT
triggers, which are used under the hood to trigger running at commit
time.

(It would be nice if CONSTRAINT triggers could be FOR EACH STATEMENT
too...)

Use-cases:

- create TEMP schema before it's needed by regular triggers

This can be useful if CREATE TEMP TABLE IF EXISTS and such in regular
triggers could slow them down.

- cleanup internal, temporary state left by triggers from earlier
transactions

- perform global consistency checks (assertions, if you like)

Note that these can be made to scale by checking only the changes
made by the current transaction. Transition tables, temporal
tables, audit tables -- these can all help for the purpose of
checking only deltas as opposed to the entire database.

Related: there was a thread about a patch to add assertions:

https://www.postgresql.org/message-id/flat/20131218113911(dot)GC5224%40alap2(dot)anarazel(dot)de#20131218113911(dot)GC5224(at)alap2(dot)anarazel(dot)de

- update materializations of views when all the relevant deltas can
be considered together

I use an alternatively view materialization system that allows direct
updates of the materialization table, and records updates in a
related history table. Being about to update such materializations
via triggers is very convenient; being able to defer such updates as
long as possible is a nice optimization.

- call C functions that have external side-effects when you know the
transaction will succeed (or previous ones that have succeeded but
not had those functions called)

Semantics:

- connect/begin/commit trigger procedures called exactly once per-
transaction that had any writes (even if they changed nothing
in the end), with one exception:

- exceptions thrown by triggers may be caught, and the triggering
statement retried, in which case triggers will run again

- connect/begin trigger procedures called in order of trigger
name (ascending) before any rows are inserted/updated/deleted by
any DML statements on non-TEMP tables in the current transaction

- commit trigger procedures called in order of commit trigger name
(ascending) at commit time, after the last statement sent by the
client/user for the current transaction

- begin and commit trigger procedures may perform additional write
operations, and if so that will NOT cause additional invocations
of begin/commit trigger procedures

- commit trigger procedures may RAISE EXCEPTION, triggering a
rollback of the transaction

Nico
--