Proposal for db level triggers - Mailing list pgsql-hackers

Background:

Firebird 2.1 allows:

CREATE TRIGGER name ON
( CONNECT
| DISCONNECT
| TRANSACTION START
| TRANSACTION COMMIT
| TRANSACTION ROLLBACK
)


I want to allow syntax:

CREATE TRIGGER <name> event

event (3 variations, application can specify any number of them): AFTER CONNECT AFTER TRANSACTION START BEFORE
TRANSACTIONCOMMIT
 

In usage:

AFTER START clears counters and flags.
UPDATE triggers on data set counters and flags.
BEFORE COMMIT examines the counters and flags and performs any final 
validation or
adjustments (or external events such as sending a MoM message)

Some of this can be done with the event mechanism but:- it can be handy to have a place to check the consistency of all

changes as a whole (assuming they've been recorded by other triggers)- the event mechanism is a bit sucky at the moment
anyway,and with 
 
this I can go straightto my MoM of choice instead of using a gateway




Storage:

In pg_trigger with: tgrelid = (oid)0 (and constraint data set to 'f',0,0) use additional bits in the tgtype column
(smallint)

Check: enough bits remain in the tgtype column.  Currently it stores 
(B|A)(I|U|D) but we may
be able to reuse the I,U,D bits for CONNECT, START, COMMIT respectively.


Caching:

Backends should cache the set of database-level triggers and refresh it 
at the start of a transaction.
The transaction triggers should be stored in 2 lists for ease of access.

A shared memory location should store the last time (or a sequence 
value) reflecting changes to the
set of database-level triggers.  The timestamp/sequencer should be 
updated atomically as we changes
to pg_trigger (either for all such changes, or only for database 
triggers) : such changes are quite
rare.


Execution:

Triggers in the same class run in alphabetic order.

Database triggers are NOT run in the account that is dbo.  This ensures 
that dbo
is never prevented from connecting to a database or from listing or dropping
such triggers.  (Consider allowing them to be turned on using a 'set' 
command: the
connect will happen immediately that completes.  The default will be 'off'.)

The AFTER CONNECT triggers run immediately after authentication in an 
implicit transaction.  If the
transaction is rolled back by any trigger then we disconnect immediately.

The AFTER START triggers are run when we enter a new transaction 
context, before any changes are
made.  If there is a rollback then we stop processing triggers.

The BEFORE COMMIT triggers are run just before normal commit processing 
starts.  They may elect to
roll back.  If there is a rollback then we stop processing triggers.


Implementation:

(I made this up.  I don't know what I'm doing really.  This is from a 
very cursory
look at the code.  Help!)

AFTER CONNECT needs to be inserted at the end of the authorization 
processing and we need to
detect a rollback there so we can disconnect.

AFTER START can be handled by a new state inserted in front of 
TRANS_INPROGRESS (or handled
inline in xact.c:StartTransaction?)

BEFORE COMMIT is handled in xact.c:CommitTransaction.  We already allow 
triggers to be deferred
to the commit and run in AfterTriggerFireDeferred.  We need to allow 
those triggers to run,
then we run the BEFORE COMMIT triggers, and then perform a second  round 
of pending deferred
triggers.  So, insert code before AfterTriggerEndXact(true).  Needs to 
be in PrepareTransaction
too.

Unknown: how do autocommit statements work?  Need to operate 'as if' we 
have start and commit.



The usage is:

AFTER CONNECT provides a way to enhance security and permissioning.  It 
allows us to
initialise state.

AFTER START is used to re-initialise state.  We enter START immediately 
we try to do anything, so we
defer recovery from ROLLBACK to this point.  We can tell we have rolled 
back logically since we
have state left around.

BEFORE COMMIT is run just before we perform the actual commit: it can 
still make
changes to data, and can still rollback.  It needs to do side effects 
and clear out state.



James