From e675cd891a5f3019005ab4d01fc4b2d0477dc700 Mon Sep 17 00:00:00 2001 From: Euler Taveira 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; in PL/Python + + PL/Python can be used to define trigger + functions on data changes or database events. + A trigger function is created with the CREATE FUNCTION + command, declaring it as a function with no arguments and a return type of + trigger (for data change triggers) or + event_trigger (for database event triggers). + Special dictionary named TD are automatically defined to + describe the condition that triggered the call. + + + + Triggers on Data Changes + When a function is used as a trigger, the dictionary TD contains trigger-related values: @@ -767,6 +781,74 @@ $$ LANGUAGE plpython3u; "MODIFY" to indicate you've modified the new row. Otherwise the return value is ignored. + + + + Event Trigger Functions + + + event trigger + in PL/Python + + + + PL/Python can be used to define + event triggers. + PostgreSQL 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 event_trigger. + + + + When a PL/Python function is called as an + event trigger, a special dictionary named TD is + automatically created. The TD keys and their associated + values are: + + + + TD["event"] text + + + event the trigger is fired for. + + + + + + TD["tag"] text + + + command tag for which the trigger is fired. + + + + + + + + shows an example of an + event trigger function in PL/Python. + + + + A <application>PL/Python</application> Event Trigger Function + + + This example trigger simply raises a NOTICE message + each time a supported command is executed. + + + +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(); + + + + 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