Hi Jim 2026年7月21日(火) 0:13 Jim Jones <[email protected]>: > > Hi Ian > > Thanks for the patch! Here a few comments on v2: > > == shadow variable == > > The function pg_get_event_trigger_ddl_internal has a bool parameter > named owner, and its body has a char* with the same name.
Good catch - I note the compiler-level shadow check is only effective for variables of the same type, which hadn't occurred to me before. > == trailing XXX (placeholder?) == > > + * CREATE EVENT TRIGGER statement; XXX Whoops, fixed. > == typo (2x the) == > > + is false, the the corresponding <literal>ENABLE</literal> clause is Classic typo invisible to the author, fixed. > === switch without default == > > I realise that "switch (evtForm->evtenabled)" already tests all possible > values of evtenable, but I'm wondering if we really should let it > silently finish the buffer like "ALTER EVENT TRIGGER foo ;" if evtenable > ever gets a different value. I'd argue that an error message would be > better than a malformed DDL. What do you think? I did wonder - the corresponding switch statement in pg_dump falls through to "ENABLE" as the default value, but only calls that if "evtenabled" is not 'O' (the default), so the addition of some new trigger firing state would result in "ALTER EVENT TRIGGER foo ENABLE;" if the code was not updated, which is easy to overlook with this kind of setup. On the other hand, I can't imagine what kind of new trigger firing state might be added, so maybe it's more of a theoretical risk. In any case I added an error message, and also updated the tests to check each existing firing state is correctly rendered. While looking at that, I did update the comment in "src/include/commands/trigger.h" to note that the "TRIGGER_*" definitions also apply to pg_event_trigger. Regards Ian Barwick
From 88e1f98d06cf3aa748147ee74d07c1e22150bf11 Mon Sep 17 00:00:00 2001 From: Ian Barwick <[email protected]> Date: Tue, 21 Jul 2026 10:07:27 +0900 Subject: [PATCH v3] Add pg_get_event_trigger_ddl() function Add a new SQL-callable function that returns the DDL statement needed to recreate an event trigger. It takes a name argument and an optional boolean argument for formatted output, and options to suppress output of ALTER EVENT TRIGGER statements to set the owner and whether/how the trigger is enabled. In passing, update the comment about the trigger firing states in trigger.h to mention they are used for event triggers as well. --- doc/src/sgml/func/func-info.sgml | 28 +++ src/backend/utils/adt/ddlutils.c | 212 +++++++++++++++++++- src/include/catalog/pg_proc.dat | 6 + src/include/commands/trigger.h | 3 +- src/test/regress/expected/event_trigger.out | 94 +++++++++ src/test/regress/sql/event_trigger.sql | 36 ++++ 6 files changed, 377 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index 69ef3857cfa..d4a8eeb1540 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -3902,6 +3902,34 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres} <literal>TABLESPACE</literal> clause is omitted. </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_get_event_trigger_ddl</primary> + </indexterm> + <function>pg_get_event_trigger_ddl</function> + ( <parameter>event_trigger</parameter> <type>name</type> + <optional>, <parameter>pretty</parameter> <type>boolean</type> + <literal>DEFAULT</literal> false</optional> + <optional>, <parameter>owner</parameter> <type>boolean</type> + <literal>DEFAULT</literal> true</optional> + <optional>, <parameter>enable</parameter> <type>boolean</type> + <literal>DEFAULT</literal> true</optional> ) + <returnvalue>setof text</returnvalue> + </para> + <para> + Reconstructs the <link linkend="sql-createeventtrigger"><command> + CREATE EVENT TRIGGER</command></link> statement for the specified event + trigger, followed by <link linkend="sql-altereventtrigger"><command>ALTER + EVENT TRIGGER</command></link> statements for owner, and whether the event + trigger is enabled or not. Each statement is returned as a separate row. + When <parameter>pretty</parameter> is true, the output + is pretty-printed. When <parameter>owner</parameter> is false, the + <literal>OWNER</literal> clause is omitted. When <parameter>enable</parameter> + is false, the corresponding <literal>ENABLE</literal> clause is + omitted. + </para></entry> + </row> <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c index a70f1c28655..95da93a3fa2 100644 --- a/src/backend/utils/adt/ddlutils.c +++ b/src/backend/utils/adt/ddlutils.c @@ -26,9 +26,12 @@ #include "catalog/pg_collation.h" #include "catalog/pg_database.h" #include "catalog/pg_db_role_setting.h" +#include "catalog/pg_event_trigger.h" +#include "catalog/pg_proc.h" #include "catalog/pg_tablespace.h" #include "commands/tablespace.h" #include "common/relpath.h" +#include "commands/trigger.h" #include "funcapi.h" #include "mb/pg_wchar.h" #include "miscadmin.h" @@ -56,7 +59,8 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid); static List *pg_get_database_ddl_internal(Oid dbid, bool pretty, bool no_owner, bool no_tablespace); - +static List *pg_get_event_trigger_ddl_internal(Name evtTrigName, bool pretty, + bool owner, bool enable); /* * Helper to append a formatted string with optional pretty-printing. @@ -975,3 +979,209 @@ pg_get_database_ddl(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } } + +static List * +pg_get_event_trigger_ddl_internal(Name evtTrigName, bool pretty, + bool owner, bool enable) +{ + HeapTuple evtTup; + HeapTuple procTup; + Form_pg_event_trigger evtForm; + char *evtevent; + char *evtname; + Datum evttagsDatum; + bool evttagsIsNull; + Form_pg_proc proc; + char *proname; + StringInfoData buf; + List *statements = NIL; + + evtTup = SearchSysCache1(EVENTTRIGGERNAME, NameGetDatum(evtTrigName)); + + if (!HeapTupleIsValid(evtTup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("event trigger \"%s\" does not exist", NameStr(*evtTrigName)))); + + evtForm = (Form_pg_event_trigger) GETSTRUCT(evtTup); + evtname = pstrdup(NameStr(evtForm->evtname)); + evtevent = pstrdup(NameStr(evtForm->evtevent)); + + procTup = SearchSysCache1(PROCOID, ObjectIdGetDatum(evtForm->evtfoid)); + + if (!HeapTupleIsValid(procTup)) + elog(ERROR, + "cache lookup failed for function %u", evtForm->evtfoid); + + proc = (Form_pg_proc) GETSTRUCT(procTup); + proname = NameStr(proc->proname); + + initStringInfo(&buf); + + appendStringInfo(&buf, "CREATE EVENT TRIGGER %s", + quote_identifier(evtname)); + append_ddl_option(&buf, pretty, 4, "ON %s", + quote_identifier(evtevent)); + + /* + * Generate WHEN clause, if any filter events were specified. + * + * Note that as of PostgreSQL 20, the only filter_variable supported + * is "TAG", which is not stored in pg_event_trigger, so is hard-coded + * in the generated output. This also means that by definition, the AND + * clause supported by the CREATE EVENT TRIGGER grammar cannot be actually + * be used in a valid statement, and thus will never be part of the + * generated output. + */ + evttagsDatum = SysCacheGetAttr(EVENTTRIGGEROID, evtTup, + Anum_pg_event_trigger_evttags, + &evttagsIsNull); + + if (!evttagsIsNull) + { + ArrayType *arr = DatumGetArrayTypeP(evttagsDatum); + Datum *elems; + int nelems; + int i; + StringInfoData filters; + + initStringInfo(&filters); + + deconstruct_array_builtin(arr, TEXTOID, &elems, NULL, &nelems); + + for (i = 0; i < nelems; ++i) + { + char *str = TextDatumGetCString(elems[i]); + + if (i) + appendStringInfoString(&filters, ", "); + + appendStringInfoString(&filters, quote_literal_cstr(str)); + } + + append_ddl_option(&buf, pretty, 4, "WHEN TAG IN (%s)", + filters.data); + } + + /* + * Finally, add the EXECUTE clause. Note that although the command syntax + * accepts the keyword PROCEDURE for historical reasons, only an actual + * FUNCTION can be referenced, so we don't check the prokind. + */ + append_ddl_option(&buf, pretty, 4, "EXECUTE FUNCTION %s.%s();", + quote_identifier(get_namespace_name(proc->pronamespace)), + quote_identifier(proname)); + + ReleaseSysCache(procTup); + + statements = lappend(statements, pstrdup(buf.data)); + + /* OWNER */ + if (owner && OidIsValid(evtForm->evtowner)) + { + char *owner_name = GetUserNameFromId(evtForm->evtowner, false); + + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER EVENT TRIGGER %s OWNER TO %s;", + quote_identifier(evtname), quote_identifier(owner_name)); + pfree(owner_name); + statements = lappend(statements, pstrdup(buf.data)); + } + + /* ENABLE */ + if (enable) + { + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER EVENT TRIGGER %s ", + quote_identifier(evtname)); + + switch (evtForm->evtenabled) + { + case TRIGGER_DISABLED: + appendStringInfoString(&buf, "DISABLE"); + break; + case TRIGGER_FIRES_ON_ORIGIN: + appendStringInfoString(&buf, "ENABLE"); + break; + case TRIGGER_FIRES_ON_REPLICA: + appendStringInfoString(&buf, "ENABLE REPLICA"); + break; + case TRIGGER_FIRES_ALWAYS: + appendStringInfoString(&buf, "ENABLE ALWAYS"); + break; + default: + elog(ERROR, "unsupported evtenabled type %c", evtForm->evtenabled); + break; + } + + appendStringInfoChar(&buf, ';'); + statements = lappend(statements, pstrdup(buf.data)); + } + + ReleaseSysCache(evtTup); + + pfree(buf.data); + pfree(evtname); + pfree(evtevent); + + return statements; +} + +/* + * pg_get_event_trigger_ddl + * Get the DDL statement for an event trigger. + * + * Each row is a complete SQL statement. The first row is always the + * CREATE EVENT TRIGGER statement; subsequent elements are ALTER EVENT + * TRIGGER statements for ownership and enablement state, which can be + * optionally suppressed. + */ +Datum +pg_get_event_trigger_ddl(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + List *statements; + + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + Name evtTrigName; + bool pretty; + bool owner; + bool enable; + + funcctx = SRF_FIRSTCALL_INIT(); + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + evtTrigName = PG_GETARG_NAME(0); + pretty = PG_GETARG_BOOL(1); + owner = PG_GETARG_BOOL(2); + enable = PG_GETARG_BOOL(3); + + statements = pg_get_event_trigger_ddl_internal(evtTrigName, pretty, + owner, enable); + + funcctx->user_fctx = statements; + funcctx->max_calls = list_length(statements); + + MemoryContextSwitchTo(oldcontext); + } + + funcctx = SRF_PERCALL_SETUP(); + statements = (List *) funcctx->user_fctx; + + if (funcctx->call_cntr < funcctx->max_calls) + { + char *stmt; + + stmt = list_nth(statements, funcctx->call_cntr); + + SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt)); + } + else + { + list_free_deep(statements); + SRF_RETURN_DONE(funcctx); + } +} + diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 1c55a4dea34..b179c01fdd6 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8631,6 +8631,12 @@ proargtypes => 'regdatabase bool bool bool', proargnames => '{database,pretty,owner,tablespace}', proargdefaults => '{false,true,true}', prosrc => 'pg_get_database_ddl' }, +{ oid => '9067', descr => 'get DDL to recreate an event trigger', + proname => 'pg_get_event_trigger_ddl', prorows => '10', proretset => 't', + provolatile => 's', pronargdefaults => '3' , prorettype => 'text', + proargtypes => 'name bool bool bool', + proargnames => '{event_trigger,pretty,owner,enable}', + proargdefaults => '{false,true,true}', prosrc => 'pg_get_event_trigger_ddl' }, { oid => '2509', descr => 'deparse an encoded expression with pretty-print option', proname => 'pg_get_expr', provolatile => 's', prorettype => 'text', diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 0c3d485abf4..337a76c0b9f 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -146,7 +146,8 @@ extern PGDLLIMPORT int SessionReplicationRole; /* * States at which a trigger can be fired. These are the - * possible values for pg_trigger.tgenabled. + * possible values for pg_trigger.tgenabled and + * pg_event_trigger.evtenabled. */ #define TRIGGER_FIRES_ON_ORIGIN 'O' #define TRIGGER_FIRES_ALWAYS 'A' diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index 86ae50ce531..6f1506757c1 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -84,6 +84,99 @@ create event trigger regress_event_trigger2 on ddl_command_start execute procedure test_event_trigger(); -- OK comment on event trigger regress_event_trigger is 'test comment'; +-- further output may display the trigger owner name, so ensure it +-- is set to something predictable. +CREATE ROLE regress_evt_superuser SUPERUSER; +ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; +ALTER EVENT TRIGGER regress_event_trigger2 OWNER TO regress_evt_superuser; +-- these are all OK - checks the DDL output without a tag filter and with one +SELECT pg_get_event_trigger_ddl('regress_event_trigger'); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; + ALTER EVENT TRIGGER regress_event_trigger ENABLE; +(3 rows) + +SELECT pg_get_event_trigger_ddl('regress_event_trigger2'); + pg_get_event_trigger_ddl +---------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger2 ON ddl_command_start WHEN TAG IN ('CREATE TABLE', 'CREATE FUNCTION') EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger2 OWNER TO regress_evt_superuser; + ALTER EVENT TRIGGER regress_event_trigger2 ENABLE; +(3 rows) + +-- check formatted output +SELECT pg_get_event_trigger_ddl('regress_event_trigger', true); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger + + ON ddl_command_start + + EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; + ALTER EVENT TRIGGER regress_event_trigger ENABLE; +(3 rows) + +-- check output without owner/enable type +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger ENABLE; +(2 rows) + +SELECT pg_get_event_trigger_ddl('regress_event_trigger', enable:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; +(2 rows) + +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false, enable:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); +(1 row) + +-- check output with different enable values +ALTER EVENT TRIGGER regress_event_trigger DISABLE; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger DISABLE; +(2 rows) + +ALTER EVENT TRIGGER regress_event_trigger ENABLE ALWAYS; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger ENABLE ALWAYS; +(2 rows) + +ALTER EVENT TRIGGER regress_event_trigger ENABLE REPLICA; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + pg_get_event_trigger_ddl +--------------------------------------------------------------------------------------------------------------- + CREATE EVENT TRIGGER regress_event_trigger ON ddl_command_start EXECUTE FUNCTION public.test_event_trigger(); + ALTER EVENT TRIGGER regress_event_trigger ENABLE REPLICA; +(2 rows) + +-- reset enable state +ALTER EVENT TRIGGER regress_event_trigger ENABLE; +-- will return an empty result set +SELECT pg_get_event_trigger_ddl(NULL); + pg_get_event_trigger_ddl +-------------------------- +(0 rows) + +-- should fail, no argument +SELECT pg_get_event_trigger_ddl(); +ERROR: function pg_get_event_trigger_ddl() does not exist +LINE 1: SELECT pg_get_event_trigger_ddl(); + ^ +DETAIL: No function of that name accepts the given number of arguments. -- drop as non-superuser should fail create role regress_evt_user; set role regress_evt_user; @@ -398,6 +491,7 @@ SELECT * FROM dropped_objects WHERE object_type = 'schema'; (3 rows) DROP ROLE regress_evt_user; +DROP ROLE regress_evt_superuser; DROP EVENT TRIGGER regress_event_trigger_drop_objects; DROP EVENT TRIGGER undroppable; -- Event triggers on relations. diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql index d0e6ba295fe..43db4b9615d 100644 --- a/src/test/regress/sql/event_trigger.sql +++ b/src/test/regress/sql/event_trigger.sql @@ -85,6 +85,41 @@ create event trigger regress_event_trigger2 on ddl_command_start -- OK comment on event trigger regress_event_trigger is 'test comment'; +-- further output may display the trigger owner name, so ensure it +-- is set to something predictable. +CREATE ROLE regress_evt_superuser SUPERUSER; +ALTER EVENT TRIGGER regress_event_trigger OWNER TO regress_evt_superuser; +ALTER EVENT TRIGGER regress_event_trigger2 OWNER TO regress_evt_superuser; + +-- these are all OK - checks the DDL output without a tag filter and with one +SELECT pg_get_event_trigger_ddl('regress_event_trigger'); +SELECT pg_get_event_trigger_ddl('regress_event_trigger2'); + +-- check formatted output +SELECT pg_get_event_trigger_ddl('regress_event_trigger', true); + +-- check output without owner/enable type +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); +SELECT pg_get_event_trigger_ddl('regress_event_trigger', enable:=false); +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false, enable:=false); + +-- check output with different enable values +ALTER EVENT TRIGGER regress_event_trigger DISABLE; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); +ALTER EVENT TRIGGER regress_event_trigger ENABLE ALWAYS; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); +ALTER EVENT TRIGGER regress_event_trigger ENABLE REPLICA; +SELECT pg_get_event_trigger_ddl('regress_event_trigger', owner:=false); + +-- reset enable state +ALTER EVENT TRIGGER regress_event_trigger ENABLE; + +-- will return an empty result set +SELECT pg_get_event_trigger_ddl(NULL); + +-- should fail, no argument +SELECT pg_get_event_trigger_ddl(); + -- drop as non-superuser should fail create role regress_evt_user; set role regress_evt_user; @@ -296,6 +331,7 @@ DROP OWNED BY regress_evt_user; SELECT * FROM dropped_objects WHERE object_type = 'schema'; DROP ROLE regress_evt_user; +DROP ROLE regress_evt_superuser; DROP EVENT TRIGGER regress_event_trigger_drop_objects; DROP EVENT TRIGGER undroppable; -- 2.52.0
