Thread: Proposal for db level triggers
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
James Mansion wrote: > 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) > I'd like to point out also that AFTER CONNECT is a good opportunity to CREATE TEMP TABLE (be nice if a global temp table definition could be persisted and automatically duplicated into each session, but never mind). And if we use data update triggers to insert into a DELETE ROWS temp table or an in-memory data structure, the BEFORE COMMIT trigger is the place to do a bulk copy into real table(s) or combine rows into a BLOb in an audit table. James
On Fri, Mar 14, 2008 at 3:44 AM, James Mansion <james@mansionfamily.plus.com> wrote: > > And if we use data update triggers to insert into a DELETE ROWS temp > table or an in-memory data > structure, the BEFORE COMMIT trigger is the place to do a bulk copy into > real table(s) or combine > rows into a BLOb in an audit table. > Another use case of BEFORE COMMIT trigger is to update the row counts for fast select count(*) operations (of course with some additional infrastructure) Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
On Mar 13, 2008, at 5:14 PM, James Mansion wrote: > James Mansion wrote: >> 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) >> > I'd like to point out also that AFTER CONNECT is a good opportunity > to CREATE TEMP TABLE (be > nice if a global temp table definition could be persisted and > automatically duplicated into each session, but > never mind). +1 on both counts. Can we get a TODO? -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Hi, We have something that seems to work and may be used as a start point. Please, take a look at http://gorda.di.uminho.pt/community/pgsqlg/ In particular, take a look at the file src/backend/commands/triggerspecial. Cheers, Alfranio. > > On Mar 13, 2008, at 5:14 PM, James Mansion wrote: > >> James Mansion wrote: >>> 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) >>> >> I'd like to point out also that AFTER CONNECT is a good opportunity >> to CREATE TEMP TABLE (be >> nice if a global temp table definition could be persisted and >> automatically duplicated into each session, but >> never mind). > > +1 on both counts. Can we get a TODO? > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > >
Added to TODO: * Add database and transaction-level triggers http://archives.postgresql.org/pgsql-hackers/2008-03/msg00451.php --------------------------------------------------------------------------- Decibel! wrote: > > On Mar 13, 2008, at 5:14 PM, James Mansion wrote: > > > James Mansion wrote: > >> 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) > >> > > I'd like to point out also that AFTER CONNECT is a good opportunity > > to CREATE TEMP TABLE (be > > nice if a global temp table definition could be persisted and > > automatically duplicated into each session, but > > never mind). > > +1 on both counts. Can we get a TODO? > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +