[HACKERS] Re: COMMIT TRIGGERs, take n+1 - Mailing list pgsql-hackers

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.GC5224%40alap2.anarazel.de#20131218113911.GC5224@alap2.anarazel.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
--

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers