event trigger support for PL/Python

From 4902acc0740fb795dc282d73bb2ef9775fb26e1f Mon Sep 17 00:00:00 2001
From: Euler Taveira <euler@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 <euler@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