Attached is a patch to provide a new event trigger that will fire on
transaction commit. I have tried to make certain that it fires at a
sufficiently early stage in the commit process that some of the evils
mentioned in previous discussions on this topic aren't relevant.
The triggers don't fire if there is no real XID, so only actual data
changes should cause the trigger to fire. They also don't fire in single
user mode, so that if you do something stupid like create a trigger that
unconditionally raises an error you have a way to recover.
This is intended to be somewhat similar to the same feature in the
Firebird database, and the initial demand came from a client migrating
from that system to Postgres.
cheers
andrew
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index ac31332..3bbf1a4 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -12,7 +12,7 @@
<productname>PostgreSQL</> also provides event triggers. Unlike regular
triggers, which are attached to a single table and capture only DML events,
event triggers are global to a particular database and are capable of
- capturing DDL events.
+ capturing DDL events or transaction commits.
</para>
<para>
@@ -29,8 +29,9 @@
occurs in the database in which it is defined. Currently, the only
supported events are
<literal>ddl_command_start</>,
- <literal>ddl_command_end</>
- and <literal>sql_drop</>.
+ <literal>ddl_command_end</>,
+ <literal>sql_drop</>, and
+ <literal>transaction_commit</>.
Support for additional events may be added in future releases.
</para>
@@ -65,6 +66,15 @@
</para>
<para>
+ A <literal>transaction_commit</> trigger is called at the end of a
+ transaction, just before any deferred triggers are fired, unless
+ no data changes have been made by the transaction, or
+ <productname>PostgreSQL</> is running in Single-User mode. This is so
+ that you can recover from a badly specified <literal>transaction_commit</>
+ trigger.
+ </para>
+
+ <para>
Event triggers (like other functions) cannot be executed in an aborted
transaction. Thus, if a DDL command fails with an error, any associated
<literal>ddl_command_end</> triggers will not be executed. Conversely,
@@ -77,8 +87,13 @@
</para>
<para>
- For a complete list of commands supported by the event trigger mechanism,
- see <xref linkend="event-trigger-matrix">.
+ A <literal>transaction_commit</> trigger is also not called in an
+ aborted transaction.
+ </para>
+
+ <para>
+ For a complete list of commands supported by the event trigger
+ mechanism, see <xref linkend="event-trigger-matrix">.
</para>
<para>
@@ -101,6 +116,11 @@
to intercept. A common use of such triggers is to restrict the range of
DDL operations which users may perform.
</para>
+
+ <para>
+ <literal>transaction_commit</> triggers do not currently support
+ <literal>WHEN</literal> clauses.
+ </para>
</sect1>
<sect1 id="event-trigger-matrix">
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 0591f3f..74fc04c 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -1825,6 +1825,16 @@ CommitTransaction(void)
Assert(s->parent == NULL);
/*
+ * First fire any pre-commit triggers, so if they in turn cause any
+ * deferred triggers etc to fire this will be picked up below.
+ * Only fire them, though, if we have a real transaction ID and
+ * we're not running standalone. Not firing when standalone provides
+ * a way to recover from setting up a bad transaction trigger.
+ */
+ if (s->transactionId != InvalidTransactionId && IsUnderPostmaster)
+ PreCommitTriggersFire();
+
+ /*
* Do pre-commit processing that involves calling user-defined code, such
* as triggers. Since closing cursors could queue trigger actions,
* triggers could open cursors, etc, we have to keep looping until there's
@@ -2030,6 +2040,16 @@ PrepareTransaction(void)
Assert(s->parent == NULL);
/*
+ * First fire any pre-commit triggers, so if they in turn cause any
+ * deferred triggers etc to fire this will be picked up below.
+ * Only fire them, though, if we have a real transaction ID and
+ * we're not running standalone. Not firing when standalone provides
+ * a way to recover from setting up a bad transaction trigger.
+ */
+ if (s->transactionId != InvalidTransactionId && IsUnderPostmaster)
+ PreCommitTriggersFire();
+
+ /*
* Do pre-commit processing that involves calling user-defined code, such
* as triggers. Since closing cursors could queue trigger actions,
* triggers could open cursors, etc, we have to keep looping until there's
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 328e2a8..f93441f 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -153,7 +153,8 @@ CreateEventTrigger(CreateEventTrigStmt *stmt)
/* Validate event name. */
if (strcmp(stmt->eventname, "ddl_command_start") != 0 &&
strcmp(stmt->eventname, "ddl_command_end") != 0 &&
- strcmp(stmt->eventname, "sql_drop") != 0)
+ strcmp(stmt->eventname, "sql_drop") != 0 &&
+ strcmp(stmt->eventname, "transaction_commit") != 0)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("unrecognized event name \"%s\"",
@@ -1291,3 +1292,42 @@ pg_event_trigger_dropped_objects(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+
+/*
+ * PreCommitTriggersFire
+ *
+ * fire triggers set for the commit event.
+ *
+ * This will be called just before deferred RI and Constraint triggers are
+ * fired.
+ */
+void
+PreCommitTriggersFire(void)
+{
+ List * trigger_list;
+ EventTriggerData trigdata;
+ List *runlist = NIL;
+ ListCell *lc;
+
+ trigger_list = EventCacheLookup(EVT_Commit);
+
+ foreach(lc, trigger_list)
+ {
+ EventTriggerCacheItem *item = lfirst(lc);
+
+ runlist = lappend_oid(runlist, item->fnoid);
+ }
+
+ /* don't spend any more time on this if no functions to run */
+ if (runlist == NIL)
+ return;
+
+ trigdata.type = T_EventTriggerData;
+ trigdata.event = "transaction_commit";
+ trigdata.parsetree = NULL;
+ trigdata.tag = "COMMIT";
+
+ EventTriggerInvoke(runlist, &trigdata);
+
+ list_free(runlist);
+}
diff --git a/src/backend/utils/cache/evtcache.c b/src/backend/utils/cache/evtcache.c
index c2242c4..c8bb1e6 100644
--- a/src/backend/utils/cache/evtcache.c
+++ b/src/backend/utils/cache/evtcache.c
@@ -169,6 +169,8 @@ BuildEventTriggerCache(void)
event = EVT_DDLCommandEnd;
else if (strcmp(evtevent, "sql_drop") == 0)
event = EVT_SQLDrop;
+ else if (strcmp(evtevent, "transaction_commit") == 0)
+ event = EVT_Commit;
else
continue;
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index cb0e5d5..67bbb35 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -52,4 +52,6 @@ extern void EventTriggerEndCompleteQuery(void);
extern bool trackDroppedObjectsNeeded(void);
extern void EventTriggerSQLDropAddObject(ObjectAddress *object);
+extern void PreCommitTriggersFire(void);
+
#endif /* EVENT_TRIGGER_H */
diff --git a/src/include/utils/evtcache.h b/src/include/utils/evtcache.h
index 43c6f61..60178ed 100644
--- a/src/include/utils/evtcache.h
+++ b/src/include/utils/evtcache.h
@@ -20,7 +20,8 @@ typedef enum
{
EVT_DDLCommandStart,
EVT_DDLCommandEnd,
- EVT_SQLDrop
+ EVT_SQLDrop,
+ EVT_Commit
} EventTriggerEvent;
typedef struct
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
