On 11/15/2013 09:07 PM, Peter Eisentraut wrote:
On Fri, 2013-11-15 at 13:01 -0500, Andrew Dunstan wrote:
Attached is a patch to provide a new event trigger that will fire on
transaction commit.
xact.c: In function ‘CommitTransaction’:
xact.c:1835:3: warning: implicit declaration of function
‘PreCommitTriggersFire’ [-Wimplicit-function-declaration]
Oops. missed a #include. Revised patch attached.
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..e7f5095 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -30,6 +30,7 @@
#include "catalog/namespace.h"
#include "catalog/storage.h"
#include "commands/async.h"
+#include "commands/event_trigger.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
#include "executor/spi.h"
@@ -1825,6 +1826,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 +2041,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