2026年7月21日(火) 12:38 Rithvika Devisetti <[email protected]>: > > Hi, > > On Mon, Jul 20, 2026 at 6:49 PM Ian Lawrence Barwick <[email protected]> > wrote: >> >> 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. > > > Patch applies clean to me, and tests passed. I see a grammatical typo below, > there is an additional "be". > > cannot be actually be used in a valid statement
Thanks for checking! Revised version attached, with that and a couple of minor text tweaks. Regards Ian Barwick
From 587c1212abf938c41d60cd14665c966a2e612a4b Mon Sep 17 00:00:00 2001 From: Ian Barwick <[email protected]> Date: Tue, 21 Jul 2026 14:08:02 +0900 Subject: [PATCH v4] 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 | 211 +++++++++++++++++++- 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, 376 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index 69ef3857cfa..2f24cfa335e 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> or + <literal>DISABLE</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..fd3e30a8700 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,208 @@ 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 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 omitted. + */ +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
