Hi,
There is an old thread [1] that proposed $SUBJECT. That patch was not committed
and the thread died. I use the referred patch as base for the attached version.
The key differences between them are: documentation, tests, refactor (0001) and
a few cleanups.
[1] https://www.postgresql.org/message-id/m2ob7wihex.fsf%402ndQuadrant.fr
--
Euler Taveira
EDB https://www.enterprisedb.com/
From 4902acc0740fb795dc282d73bb2ef9775fb26e1f Mon Sep 17 00:00:00 2001
From: Euler Taveira <eu...@eulerto.com>
Date: Tue, 1 Jul 2025 19:43:41 -0300
Subject: [PATCH v1 1/2] PL/Python: refactor for trigger support
Change is_trigger type from boolean to enum. That's a preparation to add
event trigger support.
---
src/pl/plpython/plpy_exec.c | 2 +-
src/pl/plpython/plpy_main.c | 25 +++++++++++++++++++------
src/pl/plpython/plpy_procedure.c | 13 ++++++++-----
src/pl/plpython/plpy_procedure.h | 13 +++++++++++--
src/tools/pgindent/typedefs.list | 1 +
5 files changed, 40 insertions(+), 14 deletions(-)
diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.c
index 28fbd443b98..22835174b69 100644
--- a/src/pl/plpython/plpy_exec.c
+++ b/src/pl/plpython/plpy_exec.c
@@ -509,7 +509,7 @@ PLy_function_save_args(PLyProcedure *proc)
Py_XINCREF(result->args);
/* If it's a trigger, also save "TD" */
- if (proc->is_trigger)
+ if (proc->is_trigger == PLPY_TRIGGER)
{
result->td = PyDict_GetItemString(proc->globals, "TD");
Py_XINCREF(result->td);
diff --git a/src/pl/plpython/plpy_main.c b/src/pl/plpython/plpy_main.c
index f36eadbadc6..66e11aba754 100644
--- a/src/pl/plpython/plpy_main.c
+++ b/src/pl/plpython/plpy_main.c
@@ -19,6 +19,7 @@
#include "plpy_procedure.h"
#include "plpy_subxactobject.h"
#include "plpy_util.h"
+#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -38,7 +39,7 @@ PG_FUNCTION_INFO_V1(plpython3_call_handler);
PG_FUNCTION_INFO_V1(plpython3_inline_handler);
-static bool PLy_procedure_is_trigger(Form_pg_proc procStruct);
+static PLyTrigType PLy_procedure_is_trigger(Form_pg_proc procStruct);
static void plpython_error_callback(void *arg);
static void plpython_inline_error_callback(void *arg);
static void PLy_init_interp(void);
@@ -163,7 +164,7 @@ plpython3_validator(PG_FUNCTION_ARGS)
Oid funcoid = PG_GETARG_OID(0);
HeapTuple tuple;
Form_pg_proc procStruct;
- bool is_trigger;
+ PLyTrigType is_trigger;
if (!CheckFunctionValidatorAccess(fcinfo->flinfo->fn_oid, funcoid))
PG_RETURN_VOID();
@@ -235,14 +236,14 @@ plpython3_call_handler(PG_FUNCTION_ARGS)
Relation tgrel = ((TriggerData *) fcinfo->context)->tg_relation;
HeapTuple trv;
- proc = PLy_procedure_get(funcoid, RelationGetRelid(tgrel), true);
+ proc = PLy_procedure_get(funcoid, RelationGetRelid(tgrel), PLPY_TRIGGER);
exec_ctx->curr_proc = proc;
trv = PLy_exec_trigger(fcinfo, proc);
retval = PointerGetDatum(trv);
}
else
{
- proc = PLy_procedure_get(funcoid, InvalidOid, false);
+ proc = PLy_procedure_get(funcoid, InvalidOid, PLPY_NOT_TRIGGER);
exec_ctx->curr_proc = proc;
retval = PLy_exec_function(fcinfo, proc);
}
@@ -336,10 +337,22 @@ plpython3_inline_handler(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
-static bool
+static PLyTrigType
PLy_procedure_is_trigger(Form_pg_proc procStruct)
{
- return (procStruct->prorettype == TRIGGEROID);
+ PLyTrigType ret;
+
+ switch (procStruct->prorettype)
+ {
+ case TRIGGEROID:
+ ret = PLPY_TRIGGER;
+ break;
+ default:
+ ret = PLPY_NOT_TRIGGER;
+ break;
+ }
+
+ return ret;
}
static void
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index c176d24e801..15b7c100144 100644
--- a/src/pl/plpython/plpy_procedure.c
+++ b/src/pl/plpython/plpy_procedure.c
@@ -21,7 +21,7 @@
static HTAB *PLy_procedure_cache = NULL;
-static PLyProcedure *PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool is_trigger);
+static PLyProcedure *PLy_procedure_create(HeapTuple procTup, Oid fn_oid, PLyTrigType is_trigger);
static bool PLy_procedure_valid(PLyProcedure *proc, HeapTuple procTup);
static char *PLy_procedure_munge_source(const char *name, const char *src);
@@ -63,15 +63,18 @@ PLy_procedure_name(PLyProcedure *proc)
* be used with, so no sensible fn_rel can be passed.
*/
PLyProcedure *
-PLy_procedure_get(Oid fn_oid, Oid fn_rel, bool is_trigger)
+PLy_procedure_get(Oid fn_oid, Oid fn_rel, PLyTrigType is_trigger)
{
- bool use_cache = !(is_trigger && fn_rel == InvalidOid);
+ bool use_cache = true;
HeapTuple procTup;
PLyProcedureKey key;
PLyProcedureEntry *volatile entry = NULL;
PLyProcedure *volatile proc = NULL;
bool found = false;
+ if (is_trigger == PLPY_TRIGGER && fn_rel == InvalidOid)
+ use_cache = false;
+
procTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(fn_oid));
if (!HeapTupleIsValid(procTup))
elog(ERROR, "cache lookup failed for function %u", fn_oid);
@@ -127,7 +130,7 @@ PLy_procedure_get(Oid fn_oid, Oid fn_rel, bool is_trigger)
* Create a new PLyProcedure structure
*/
static PLyProcedure *
-PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool is_trigger)
+PLy_procedure_create(HeapTuple procTup, Oid fn_oid, PLyTrigType is_trigger)
{
char procName[NAMEDATALEN + 256];
Form_pg_proc procStruct;
@@ -200,7 +203,7 @@ PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool is_trigger)
* get information required for output conversion of the return value,
* but only if this isn't a trigger.
*/
- if (!is_trigger)
+ if (is_trigger == PLPY_NOT_TRIGGER)
{
Oid rettype = procStruct->prorettype;
HeapTuple rvTypeTup;
diff --git a/src/pl/plpython/plpy_procedure.h b/src/pl/plpython/plpy_procedure.h
index 5db854fc8bd..601b91d5d94 100644
--- a/src/pl/plpython/plpy_procedure.h
+++ b/src/pl/plpython/plpy_procedure.h
@@ -11,6 +11,15 @@
extern void init_procedure_caches(void);
+/*
+ * Trigger type
+ */
+typedef enum PLyTrigType
+{
+ PLPY_TRIGGER,
+ PLPY_NOT_TRIGGER,
+} PLyTrigType;
+
/* saved arguments for outer recursion level or set-returning function */
typedef struct PLySavedArgs
{
@@ -33,7 +42,7 @@ typedef struct PLyProcedure
bool fn_readonly;
bool is_setof; /* true, if function returns result set */
bool is_procedure;
- bool is_trigger; /* called as trigger? */
+ PLyTrigType is_trigger; /* called as trigger? */
PLyObToDatum result; /* Function result output conversion info */
PLyDatumToOb result_in; /* For converting input tuples in a trigger */
char *src; /* textual procedure code, after mangling */
@@ -65,7 +74,7 @@ typedef struct PLyProcedureEntry
/* PLyProcedure manipulation */
extern char *PLy_procedure_name(PLyProcedure *proc);
-extern PLyProcedure *PLy_procedure_get(Oid fn_oid, Oid fn_rel, bool is_trigger);
+extern PLyProcedure *PLy_procedure_get(Oid fn_oid, Oid fn_rel, PLyTrigType is_trigger);
extern void PLy_procedure_compile(PLyProcedure *proc, const char *src);
extern void PLy_procedure_delete(PLyProcedure *proc);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ff050e93a50..5da48dd4ac7 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1989,6 +1989,7 @@ PLyScalarToOb
PLySubtransactionData
PLySubtransactionObject
PLyTransformToOb
+PLyTrigType
PLyTupleToOb
PLyUnicode_FromStringAndSize_t
PLy_elog_impl_t
--
2.39.5
From e675cd891a5f3019005ab4d01fc4b2d0477dc700 Mon Sep 17 00:00:00 2001
From: Euler Taveira <eu...@eulerto.com>
Date: Mon, 14 Jul 2025 15:47:58 -0300
Subject: [PATCH v1 2/2] PL/Python: add event trigger support
Allow event trigger to be written in PL/Python. It provides a TD
dictionary with some information about the event trigger.
---
doc/src/sgml/plpython.sgml | 82 +++++++++++++++++++
src/pl/plpython/expected/plpython_trigger.out | 25 ++++++
src/pl/plpython/plpy_exec.c | 40 +++++++++
src/pl/plpython/plpy_exec.h | 1 +
src/pl/plpython/plpy_main.c | 13 ++-
src/pl/plpython/plpy_procedure.h | 1 +
src/pl/plpython/sql/plpython_trigger.sql | 21 +++++
7 files changed, 182 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index cb065bf5f88..9e8e7bbc3c7 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -662,6 +662,20 @@ $$ LANGUAGE plpython3u;
<secondary>in PL/Python</secondary>
</indexterm>
+ <para>
+ <application>PL/Python</application> can be used to define trigger
+ functions on data changes or database events.
+ A trigger function is created with the <command>CREATE FUNCTION</command>
+ command, declaring it as a function with no arguments and a return type of
+ <type>trigger</type> (for data change triggers) or
+ <type>event_trigger</type> (for database event triggers).
+ Special dictionary named <varname>TD</varname> are automatically defined to
+ describe the condition that triggered the call.
+ </para>
+
+ <sect2 id="plpython-dml-trigger">
+ <title>Triggers on Data Changes</title>
+
<para>
When a function is used as a trigger, the dictionary
<literal>TD</literal> contains trigger-related values:
@@ -767,6 +781,74 @@ $$ LANGUAGE plpython3u;
<literal>"MODIFY"</literal> to indicate you've modified the new row.
Otherwise the return value is ignored.
</para>
+ </sect2>
+
+ <sect2 id="plpython-event-trigger">
+ <title>Event Trigger Functions</title>
+
+ <indexterm zone="plpython-event-trigger">
+ <primary>event trigger</primary>
+ <secondary>in PL/Python</secondary>
+ </indexterm>
+
+ <para>
+ <application>PL/Python</application> can be used to define
+ <link linkend="event-triggers">event triggers</link>.
+ <productname>PostgreSQL</productname> requires that a function that
+ is to be called as an event trigger must be declared as a function with
+ no arguments and a return type of <literal>event_trigger</literal>.
+ </para>
+
+ <para>
+ When a <application>PL/Python</application> function is called as an
+ event trigger, a special dictionary named <varname>TD</varname> is
+ automatically created. The <varname>TD</varname> keys and their associated
+ values are:
+
+ <variablelist>
+ <varlistentry>
+ <term><varname>TD["event"]</varname> <type>text</type></term>
+ <listitem>
+ <para>
+ event the trigger is fired for.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>TD["tag"]</varname> <type>text</type></term>
+ <listitem>
+ <para>
+ command tag for which the trigger is fired.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ <xref linkend="plpython-event-trigger-example"/> shows an example of an
+ event trigger function in <application>PL/Python</application>.
+ </para>
+
+ <example id="plpython-event-trigger-example">
+ <title>A <application>PL/Python</application> Event Trigger Function</title>
+
+ <para>
+ This example trigger simply raises a <literal>NOTICE</literal> message
+ each time a supported command is executed.
+ </para>
+
+<programlisting>
+CREATE OR REPLACE FUNCTION pysnitch() RETURNS event_trigger AS $$
+ plpy.notice("TD[event] => " + str(TD["event"]) + " ; TD[tag] => " + str(TD["tag"]));
+$$ LANGUAGE plpython3u;
+
+CREATE EVENT TRIGGER pysnitch ON ddl_command_start EXECUTE FUNCTION pysnitch();
+</programlisting>
+ </example>
+
+ </sect2>
</sect1>
<sect1 id="plpython-database">
diff --git a/src/pl/plpython/expected/plpython_trigger.out b/src/pl/plpython/expected/plpython_trigger.out
index 64eab2fa3f4..efc1610de63 100644
--- a/src/pl/plpython/expected/plpython_trigger.out
+++ b/src/pl/plpython/expected/plpython_trigger.out
@@ -646,3 +646,28 @@ SELECT * FROM recursive_trigger_test;
1 | 2
(2 rows)
+-- event triggers
+CREATE OR REPLACE FUNCTION pysnitch() RETURNS event_trigger AS $$
+ plpy.notice("TD[event] => " + str(TD["event"]) + " ; TD[tag] => " + str(TD["tag"]));
+$$ LANGUAGE plpython3u;
+CREATE EVENT TRIGGER python_a_snitch ON ddl_command_start
+ EXECUTE PROCEDURE pysnitch();
+CREATE EVENT TRIGGER python_b_snitch ON ddl_command_end
+ EXECUTE PROCEDURE pysnitch();
+CREATE OR REPLACE FUNCTION foobar() RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
+NOTICE: TD[event] => ddl_command_start ; TD[tag] => CREATE FUNCTION
+NOTICE: TD[event] => ddl_command_end ; TD[tag] => CREATE FUNCTION
+ALTER FUNCTION foobar() COST 77;
+NOTICE: TD[event] => ddl_command_start ; TD[tag] => ALTER FUNCTION
+NOTICE: TD[event] => ddl_command_end ; TD[tag] => ALTER FUNCTION
+DROP FUNCTION foobar();
+NOTICE: TD[event] => ddl_command_start ; TD[tag] => DROP FUNCTION
+NOTICE: TD[event] => ddl_command_end ; TD[tag] => DROP FUNCTION
+CREATE TABLE foo();
+NOTICE: TD[event] => ddl_command_start ; TD[tag] => CREATE TABLE
+NOTICE: TD[event] => ddl_command_end ; TD[tag] => CREATE TABLE
+DROP TABLE foo;
+NOTICE: TD[event] => ddl_command_start ; TD[tag] => DROP TABLE
+NOTICE: TD[event] => ddl_command_end ; TD[tag] => DROP TABLE
+DROP EVENT TRIGGER python_a_snitch;
+DROP EVENT TRIGGER python_b_snitch;
diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.c
index 22835174b69..1cfbf0caa73 100644
--- a/src/pl/plpython/plpy_exec.c
+++ b/src/pl/plpython/plpy_exec.c
@@ -9,6 +9,7 @@
#include "access/htup_details.h"
#include "access/xact.h"
#include "catalog/pg_type.h"
+#include "commands/event_trigger.h"
#include "commands/trigger.h"
#include "executor/spi.h"
#include "funcapi.h"
@@ -427,6 +428,45 @@ PLy_exec_trigger(FunctionCallInfo fcinfo, PLyProcedure *proc)
return rv;
}
+/* event trigger subhandler */
+void
+PLy_exec_event_trigger(FunctionCallInfo fcinfo, PLyProcedure *proc)
+{
+ EventTriggerData *tdata;
+ PyObject *volatile pltdata = NULL;
+
+ Assert(CALLED_AS_EVENT_TRIGGER(fcinfo));
+ tdata = (EventTriggerData *) fcinfo->context;
+
+ PG_TRY();
+ {
+ PyObject *pltevent,
+ *plttag;
+
+ pltdata = PyDict_New();
+ if (!pltdata)
+ PLy_elog(ERROR, NULL);
+
+ pltevent = PLyUnicode_FromString(tdata->event);
+ PyDict_SetItemString(pltdata, "event", pltevent);
+ Py_DECREF(pltevent);
+
+ plttag = PLyUnicode_FromString(GetCommandTagName(tdata->tag));
+ PyDict_SetItemString(pltdata, "tag", plttag);
+ Py_DECREF(plttag);
+
+ PLy_procedure_call(proc, "TD", pltdata);
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish() failed");
+ }
+ PG_FINALLY();
+ {
+ Py_XDECREF(pltdata);
+ }
+ PG_END_TRY();
+}
+
/* helper functions for Python code execution */
static PyObject *
diff --git a/src/pl/plpython/plpy_exec.h b/src/pl/plpython/plpy_exec.h
index 68da1ffcb2e..f35eabbd8ee 100644
--- a/src/pl/plpython/plpy_exec.h
+++ b/src/pl/plpython/plpy_exec.h
@@ -9,5 +9,6 @@
extern Datum PLy_exec_function(FunctionCallInfo fcinfo, PLyProcedure *proc);
extern HeapTuple PLy_exec_trigger(FunctionCallInfo fcinfo, PLyProcedure *proc);
+extern void PLy_exec_event_trigger(FunctionCallInfo fcinfo, PLyProcedure *proc);
#endif /* PLPY_EXEC_H */
diff --git a/src/pl/plpython/plpy_main.c b/src/pl/plpython/plpy_main.c
index 66e11aba754..17b27f3fbe5 100644
--- a/src/pl/plpython/plpy_main.c
+++ b/src/pl/plpython/plpy_main.c
@@ -9,6 +9,7 @@
#include "access/htup_details.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
+#include "commands/event_trigger.h"
#include "commands/trigger.h"
#include "executor/spi.h"
#include "miscadmin.h"
@@ -195,7 +196,7 @@ Datum
plpython3_call_handler(PG_FUNCTION_ARGS)
{
bool nonatomic;
- Datum retval;
+ Datum retval = (Datum) 0;
PLyExecutionContext *exec_ctx;
ErrorContextCallback plerrcontext;
@@ -241,6 +242,13 @@ plpython3_call_handler(PG_FUNCTION_ARGS)
trv = PLy_exec_trigger(fcinfo, proc);
retval = PointerGetDatum(trv);
}
+ else if (CALLED_AS_EVENT_TRIGGER(fcinfo))
+ {
+ proc = PLy_procedure_get(funcoid, InvalidOid, PLPY_EVENT_TRIGGER);
+ exec_ctx->curr_proc = proc;
+ PLy_exec_event_trigger(fcinfo, proc);
+ /* there's no return value in this case */
+ }
else
{
proc = PLy_procedure_get(funcoid, InvalidOid, PLPY_NOT_TRIGGER);
@@ -347,6 +355,9 @@ PLy_procedure_is_trigger(Form_pg_proc procStruct)
case TRIGGEROID:
ret = PLPY_TRIGGER;
break;
+ case EVENT_TRIGGEROID:
+ ret = PLPY_EVENT_TRIGGER;
+ break;
default:
ret = PLPY_NOT_TRIGGER;
break;
diff --git a/src/pl/plpython/plpy_procedure.h b/src/pl/plpython/plpy_procedure.h
index 601b91d5d94..3ef22844a9b 100644
--- a/src/pl/plpython/plpy_procedure.h
+++ b/src/pl/plpython/plpy_procedure.h
@@ -17,6 +17,7 @@ extern void init_procedure_caches(void);
typedef enum PLyTrigType
{
PLPY_TRIGGER,
+ PLPY_EVENT_TRIGGER,
PLPY_NOT_TRIGGER,
} PLyTrigType;
diff --git a/src/pl/plpython/sql/plpython_trigger.sql b/src/pl/plpython/sql/plpython_trigger.sql
index 440549c0785..92a712d35e8 100644
--- a/src/pl/plpython/sql/plpython_trigger.sql
+++ b/src/pl/plpython/sql/plpython_trigger.sql
@@ -492,3 +492,24 @@ CREATE TRIGGER recursive_trigger_trig
INSERT INTO recursive_trigger_test VALUES (0, 0);
UPDATE recursive_trigger_test SET a = 11 WHERE b = 0;
SELECT * FROM recursive_trigger_test;
+
+-- event triggers
+
+CREATE OR REPLACE FUNCTION pysnitch() RETURNS event_trigger AS $$
+ plpy.notice("TD[event] => " + str(TD["event"]) + " ; TD[tag] => " + str(TD["tag"]));
+$$ LANGUAGE plpython3u;
+
+CREATE EVENT TRIGGER python_a_snitch ON ddl_command_start
+ EXECUTE PROCEDURE pysnitch();
+CREATE EVENT TRIGGER python_b_snitch ON ddl_command_end
+ EXECUTE PROCEDURE pysnitch();
+
+CREATE OR REPLACE FUNCTION foobar() RETURNS int LANGUAGE sql AS $$SELECT 1;$$;
+ALTER FUNCTION foobar() COST 77;
+DROP FUNCTION foobar();
+
+CREATE TABLE foo();
+DROP TABLE foo;
+
+DROP EVENT TRIGGER python_a_snitch;
+DROP EVENT TRIGGER python_b_snitch;
--
2.39.5
