PostgreSQL Event Triggers — DDL Hooks and Command Collection

Contents:

• Theoretical Background
• Common DBMS Design
• PostgreSQL’s Approach
• Source Walkthrough
• Source verification (as of 2026-06-05)
• Beyond PostgreSQL — Comparative Designs & Research Frontiers
• Sources

Theoretical Background

A trigger is the database’s answer to a recurring need: run some code automatically when a particular thing happens, without the application having to remember to call it. The classic, SQL-standard form is the row/statement trigger — code attached to a table that fires BEFORE or AFTER an INSERT, UPDATE, or DELETE. That mechanism is covered in postgres-triggers.md. This document is about a different and younger animal: the event trigger, code that fires not on data change but on schema change and on session lifecycle events.

The conceptual gap is worth stating plainly. A DML trigger reacts to a mutation of rows; an event trigger reacts to a mutation of the catalog — a CREATE TABLE, an ALTER ... ADD COLUMN, a DROP INDEX, a GRANT — or to a connection establishing a session. DML triggers are in the SQL standard; DDL/event triggers are not, and every system that offers them has invented its own vocabulary and its own firing model. The reason such a mechanism is wanted at all comes down to four durable use cases:

1. Auditing and change capture. Record every schema change to a log table, so a compliance team can answer “who altered this table and when?” without trawling server logs.

2. Policy enforcement. Forbid DROP TABLE outside a maintenance window, require that every new table have a primary key, or reject object names that violate a naming convention — all by raising an error from inside the trigger before or after the command runs.

3. Replication and schema propagation. Logical replication ships data changes but historically not schema changes. An event trigger that captures the DDL text (or a structured representation of it) lets an extension forward schema changes to replicas. This is the motivation behind PostgreSQL’s DDL command collection feature.

4. Reactive automation. Automatically GRANT privileges on every newly created table in a schema, or attach a default COMMENT, or register the object in an external metadata service.

The theoretical tension an event-trigger designer must resolve is one of placement and timing. A DML trigger has a natural, uniform firing point: the storage layer’s tuple-insert/update/delete routines. DDL has no such single chokepoint — CREATE TABLE, ALTER TYPE, and GRANT travel through wildly different code, and a single utility command can recursively spawn sub-commands (an ALTER TABLE that adds a column with a default may rewrite the table, create a TOAST table, and build an index). So the designer must decide: where in the command’s lifecycle does the trigger fire (before parse-analysis? before execution? after?), what context does the trigger function receive (just the command tag? the parse tree? the list of affected objects?), and how does that context survive the recursive, multi-phase nature of DDL execution.

Database System Concepts (Silberschatz, Korth, Sudarshan, 7th ed., ch. 5 “Advanced SQL”, §5.3 “Triggers”) develops the general trigger model as the triple event–condition–action (ECA): an event (the thing that happens), a condition (a predicate that gates whether the action runs), and an action (the code). The chapter’s cautions about triggers — that they execute implicitly, that cascading triggers are hard to reason about, that a buggy trigger can wedge the very operations needed to fix it — apply with extra force to event triggers, because the “event” is a DDL statement and a broken event trigger can make the database unalterable. PostgreSQL’s event-trigger design is, in ECA terms: the event is one of five named event types, the condition is a WHEN tag IN (...) filter plus a session-replication-role check, and the action is a function returning the pseudo-type event_trigger. The system’s defensive choices — disabling event triggers entirely in single-user (standalone) mode, gating them behind a superuser-only GUC — are the direct operational consequence of the textbook’s warning that a trigger can lock out its own repair.

Common DBMS Design

DDL/event triggers are a non-standard feature, so there is less convergence here than for, say, B-trees. Still, the systems that offer them share a recognizable set of design moves, and naming them makes PostgreSQL’s specific choices read as one point in a small design space.

Named event types, not a generic hook list

Rather than expose a single “on any DDL” hook, systems enumerate a small set of event types that fire at well-defined moments. Oracle has BEFORE/AFTER system events (CREATE, ALTER, DROP, LOGON, LOGOFF, SERVERERROR); SQL Server has DDL triggers keyed on event groups (DDL_TABLE_EVENTS, DDL_LOGIN_EVENTS, …). The enumeration buys two things: the engine only has to plant firing calls at the points that correspond to a defined event, and the catalog can validate at CREATE time that the user named a real event.

A firing model anchored in the command-processing pipeline

A DDL trigger has to fire somewhere in the utility-command path. The universal split is before the command (so the trigger can veto it or record intent) versus after the command (so the trigger can see the resulting catalog state). PostgreSQL’s ddl_command_start / ddl_command_end pair is exactly this split; Oracle’s BEFORE / AFTER system triggers and SQL Server’s INSTEAD OF / AFTER DDL triggers are the analogues.

A context object handed to the trigger function

The trigger function needs to know what fired it. The minimum is the command tag (CREATE TABLE); richer designs pass the object name, the schema, and sometimes the full statement text. PostgreSQL passes a purpose-built node (EventTriggerData) holding the event name, the parse tree, and the command tag, and additionally exposes set-returning functions (pg_event_trigger_dropped_objects, pg_event_trigger_ddl_commands) that the function can call to pull a structured description of what happened. SQL Server’s analogue is the EVENTDATA() XML blob.

A filter/condition to avoid firing on everything

Firing on every DDL is expensive and usually unwanted. Systems provide a way to scope the trigger: SQL Server scopes by event group; PostgreSQL scopes by an optional WHEN tag IN (...) list, stored in the catalog as a text array and compiled into a Bitmapset of command tags for fast membership testing at firing time.

A defensive escape hatch

Because a broken DDL trigger can make the schema unmodifiable, systems provide a way to bypass them. PostgreSQL disables event triggers entirely in standalone (single-user) mode and behind the superuser-settable event_triggers GUC. This is the “break glass” mechanism the textbook’s caution implies.

Theory ↔ PostgreSQL mapping

Concept	PostgreSQL name
Event-condition-action triple	event type + WHEN tag filter + event_trigger function
Enumerated event types	EventTriggerEvent enum (EVT_DDLCommandStart … EVT_Login)
Before-command hook	ddl_command_start (EventTriggerDDLCommandStart)
After-command hook	ddl_command_end (EventTriggerDDLCommandEnd)
Object-drop hook	sql_drop (EventTriggerSQLDrop)
Table-rewrite hook	table_rewrite (EventTriggerTableRewrite)
Session-login hook	login (EventTriggerOnLogin)
Context object passed to function	EventTriggerData node
CALLED_AS_EVENT_TRIGGER guard	macro in event_trigger.h
Catalog row	pg_event_trigger (evtevent, evtfoid, evttags, evtenabled)
Compiled trigger list	EventTriggerCacheItem in syscache-backed evtcache
Tag filter (compiled)	Bitmapset *tagset
Per-command scratch state	EventTriggerQueryState
Dropped-object side channel	SQLDropList → pg_event_trigger_dropped_objects
Command-collection side channel	commandList of CollectedCommand → pg_event_trigger_ddl_commands
Break-glass disable	event_triggers GUC + standalone-mode check

PostgreSQL’s Approach

Five event types, hand-placed firing calls

PostgreSQL does not have a generic “intercept any DDL” dispatcher. Instead, five specific events are defined, and each is fired by an explicitly named C function placed by hand at the right point in the command-processing path. The events are enumerated in evtcache.h:

// EventTriggerEvent — src/include/utils/evtcache.h
typedef enum
{
    EVT_DDLCommandStart,
    EVT_DDLCommandEnd,
    EVT_SQLDrop,
    EVT_TableRewrite,
    EVT_Login,
} EventTriggerEvent;

CreateEventTrigger validates that the user named one of exactly these five (by their SQL spelling) and rejects anything else up front:

// CreateEventTrigger (excerpt) — src/backend/commands/event_trigger.c
if (strcmp(stmt->eventname, "ddl_command_start") != 0 &&
    strcmp(stmt->eventname, "ddl_command_end") != 0 &&
    strcmp(stmt->eventname, "sql_drop") != 0 &&
    strcmp(stmt->eventname, "login") != 0 &&
    strcmp(stmt->eventname, "table_rewrite") != 0)
    ereport(ERROR,
            (errcode(ERRCODE_SYNTAX_ERROR),
             errmsg("unrecognized event name \"%s\"", stmt->eventname)));

Note CreateEventTrigger requires superuser, validates that the trigger function returns the event_trigger pseudo-type, and validates any WHEN tag filter list against the set of tags that support event triggers (validate_ddl_tags → command_tag_event_trigger_ok). The catalog row goes into pg_event_trigger; for login triggers it also sets a fast-path flag, discussed below.

The context node: EventTriggerData

When a trigger function runs, it receives a single node as its fmgr “context”, not as a normal argument. The node is small:

// EventTriggerData — src/include/commands/event_trigger.h
typedef struct EventTriggerData
{
    NodeTag     type;
    const char *event;          /* event name */
    Node       *parsetree;      /* parse tree */
    CommandTag  tag;
} EventTriggerData;

#define CALLED_AS_EVENT_TRIGGER(fcinfo) \
    ((fcinfo)->context != NULL && IsA((fcinfo)->context, EventTriggerData))

A function written in C checks CALLED_AS_EVENT_TRIGGER(fcinfo) to confirm it was invoked as an event trigger and not called directly. PL languages (PL/pgSQL) surface the same three fields as TG_EVENT and TG_TAG. Everything richer than these three fields — the list of dropped objects, the list of collected commands, the table being rewritten — is obtained by calling a dedicated SQL function from inside the trigger body, which reads it out of the per-command scratch state described next.

Per-command scratch state: EventTriggerQueryState

The hard part of event triggers is not firing them — it is keeping enough context alive, across the recursive multi-phase execution of a single DDL command, that the ddl_command_end and sql_drop triggers have something useful to report. PostgreSQL solves this with a per-command state object that is pushed onto a stack at the start of each top-level utility command and popped at the end:

// EventTriggerQueryState — src/backend/commands/event_trigger.c
typedef struct EventTriggerQueryState
{
    MemoryContext cxt;          /* memory context for this state's objects */

    /* sql_drop */
    slist_head  SQLDropList;
    bool        in_sql_drop;

    /* table_rewrite */
    Oid         table_rewrite_oid;
    int         table_rewrite_reason;

    /* Support for command collection */
    bool        commandCollectionInhibited;
    CollectedCommand *currentCommand;
    List       *commandList;    /* list of CollectedCommand */
    struct EventTriggerQueryState *previous;
} EventTriggerQueryState;

static EventTriggerQueryState *currentEventTriggerState = NULL;

The previous pointer makes this a stack: a DDL command run from inside an event trigger gets its own state, chained to the outer one, so dropped objects and collected commands are attributed to the right command level. The state lives in its own MemoryContext, so cleanup is a single MemoryContextDelete rather than a retail free of every SQLDropObject.

Where the events fire

The two DDL events that bracket a command — ddl_command_start and ddl_command_end — fire from inside ProcessUtilitySlow in utility.c. The “slow” path is the half of utility processing that handles commands needing parse-analysis, locking, and event-trigger support; standard_ProcessUtility routes DDL there. The bracketing is visible at the top and bottom of ProcessUtilitySlow:

// ProcessUtilitySlow (excerpt) — src/backend/tcop/utility.c
bool    isCompleteQuery = (context != PROCESS_UTILITY_SUBCOMMAND);
bool    needCleanup;

/* All event trigger calls are done only when isCompleteQuery is true */
needCleanup = isCompleteQuery && EventTriggerBeginCompleteQuery();

PG_TRY();
{
    if (isCompleteQuery)
        EventTriggerDDLCommandStart(parsetree);

    switch (nodeTag(parsetree))
    {
        /* ... one case per DDL statement type; each case executes the
           command and calls EventTriggerCollectSimpleCommand (or a more
           specific collector) for the affected object ... */
    }

    if (!commandCollected)
        EventTriggerCollectSimpleCommand(address, secondaryObject, parsetree);

    if (isCompleteQuery)
    {
        EventTriggerSQLDrop(parsetree);
        EventTriggerDDLCommandEnd(parsetree);
    }
}
PG_FINALLY();
{
    if (needCleanup)
        EventTriggerEndCompleteQuery();
}
PG_END_TRY();

Three things are load-bearing here. First, isCompleteQuery is false for sub-commands (PROCESS_UTILITY_SUBCOMMAND): an ALTER TABLE that internally issues a CREATE INDEX does not fire a second pair of events for the index. Event triggers fire once, at the top-level command boundary. Second, the whole body is wrapped in PG_TRY/PG_FINALLY so that EventTriggerEndCompleteQuery runs even if the command errors out — otherwise the state stack would leak. Third, sql_drop fires before ddl_command_end, because the drop list is collected during execution and the sql_drop trigger consumes it while the objects’ metadata is still reconstructable.

table_rewrite does not fire from utility.c; it fires from deep inside ALTER TABLE execution, at the point where the table is about to be physically rewritten:

// ATRewriteTables (excerpt) — src/backend/commands/tablecmds.c
/* And fire it only once. */
if (parsetree)
    EventTriggerTableRewrite((Node *) parsetree,
                             tab->relid,
                             tab->rewrite);

And login fires from PostgresMain once, after authentication and before the main query loop:

// PostgresMain (excerpt) — src/backend/tcop/postgres.c
/* Fire any defined login event triggers, if appropriate */
EventTriggerOnLogin();

flowchart TB
  A["standard_ProcessUtility<br/>(DDL command)"] --> B["ProcessUtilitySlow"]
  B --> C["EventTriggerBeginCompleteQuery<br/>push EventTriggerQueryState if needed"]
  C --> D["EventTriggerDDLCommandStart<br/>fire ddl_command_start"]
  D --> E["execute command<br/>(switch on nodeTag)"]
  E --> F["EventTriggerCollect*<br/>append CollectedCommand to commandList"]
  E --> G["dependency.c drops<br/>EventTriggerSQLDropAddObject -> SQLDropList"]
  F --> H["EventTriggerSQLDrop<br/>fire sql_drop, consume SQLDropList"]
  G --> H
  H --> I["EventTriggerDDLCommandEnd<br/>fire ddl_command_end, expose commandList"]
  I --> J["EventTriggerEndCompleteQuery<br/>pop + MemoryContextDelete (in PG_FINALLY)"]

Figure 1 — Where the DDL events fire inside ProcessUtilitySlow. The state is pushed once per top-level command, the start event fires before execution, drops and collected commands accumulate during execution, and the end/drop events fire afterward — all bracketed by a PG_TRY so the state is always popped. (Flow from ProcessUtilitySlow in utility.c and event_trigger.c.)

The common firing path: EventTriggerCommonSetup + EventTriggerInvoke

Every one of the five EventTrigger* firing functions funnels through EventTriggerCommonSetup, which looks up the relevant triggers in the cache, filters them, and builds the EventTriggerData node; the surviving function OIDs are then run by EventTriggerInvoke. The lookup is served by a dedicated syscache-backed cache (evtcache) keyed on event type, so the common case — no triggers for this event — is a single hash probe and an early return.

// EventTriggerCommonSetup (excerpt) — src/backend/commands/event_trigger.c
/* Use cache to find triggers for this event; fast exit if none. */
cachelist = EventCacheLookup(event);
if (cachelist == NIL)
    return NIL;

tag = EventTriggerGetTag(parsetree, event);

foreach(lc, cachelist)
{
    EventTriggerCacheItem *item = lfirst(lc);

    if (unfiltered || filter_event_trigger(tag, item))
        runlist = lappend_oid(runlist, item->fnoid);
}
if (runlist == NIL)
    return NIL;

trigdata->type = T_EventTriggerData;
trigdata->event = eventstr;
trigdata->parsetree = parsetree;
trigdata->tag = tag;
return runlist;

filter_event_trigger applies the two conditions: the session replication role (an ENABLE REPLICA/ENABLE ALWAYS distinction, so a trigger can be made to fire only on a logical replica or only on the origin), and the WHEN tag membership test against the compiled Bitmapset:

// filter_event_trigger (excerpt) — src/backend/commands/event_trigger.c
if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
{
    if (item->enabled == TRIGGER_FIRES_ON_ORIGIN)
        return false;
}
else
{
    if (item->enabled == TRIGGER_FIRES_ON_REPLICA)
        return false;
}

/* Filter by tags, if any were specified. */
if (!bms_is_empty(item->tagset) && !bms_is_member(tag, item->tagset))
    return false;

return true;

EventTriggerInvoke runs each surviving function in a fresh memory context (reset between functions so leaks are bounded), calling them via the fmgr machinery with the EventTriggerData node as context and no ordinary arguments:

// EventTriggerInvoke (excerpt) — src/backend/commands/event_trigger.c
context = AllocSetContextCreate(CurrentMemoryContext,
                                "event trigger context",
                                ALLOCSET_DEFAULT_SIZES);
oldcontext = MemoryContextSwitchTo(context);

foreach(lc, fn_oid_list)
{
    LOCAL_FCINFO(fcinfo, 0);
    Oid     fnoid = lfirst_oid(lc);
    FmgrInfo flinfo;

    if (first)
        first = false;
    else
        CommandCounterIncrement();      /* each trigger sees prior trigger's work */

    fmgr_info(fnoid, &flinfo);
    InitFunctionCallInfoData(*fcinfo, &flinfo, 0,
                             InvalidOid, (Node *) trigdata, NULL);
    FunctionCallInvoke(fcinfo);
    MemoryContextReset(context);
}

The CommandCounterIncrement between functions matters: it makes each trigger function see the catalog effects of the previous one, so a chain of triggers behaves like a sequence of sub-commands.

The event-trigger cache: evtcache

EventCacheLookup is the fast-path lookup that EventTriggerCommonSetup calls. Rather than scan pg_event_trigger on every DDL command, a small per-backend cache holds, per event type, the list of compiled EventTriggerCacheItems (function OID, enabled flag, compiled tag Bitmapset). The cache is built lazily and invalidated by a syscache callback on pg_event_trigger:

// EventCacheLookup — src/backend/utils/cache/evtcache.c
List *
EventCacheLookup(EventTriggerEvent event)
{
    EventTriggerCacheEntry *entry;

    if (EventTriggerCacheState != ETCS_VALID)
        BuildEventTriggerCache();
    entry = hash_search(EventTriggerCache, &event, HASH_FIND, NULL);
    return entry != NULL ? entry->triggerlist : NIL;
}

BuildEventTriggerCache scans pg_event_trigger in name order (so the firing order is deterministic), skips disabled triggers, decodes the event name back into an EventTriggerEvent, and decodes the stored evttags text array into a Bitmapset via DecodeTextArrayToBitmapset. The name-ordered scan is also why event triggers are disabled in standalone mode: it relies on systable_beginscan_ordered, which needs intact pg_event_trigger indexes, so a damaged index would otherwise make the database unrecoverable.

sql_drop and the dropped-object side channel

The sql_drop event answers “what objects did this command drop?” — a question whose answer must be captured during execution, because once dependency.c has finished dropping an object its catalog row is gone. The mechanism is a side channel: while a command runs, dependency.c calls EventTriggerSQLDropAddObject for each object it drops, appending a SQLDropObject to the current state’s SQLDropList. The collector captures the object’s identity, type, schema, and name before the row disappears:

// EventTriggerSQLDropAddObject (excerpt) — src/backend/commands/event_trigger.c
if (!currentEventTriggerState)
    return;

Assert(EventTriggerSupportsObject(object));

oldcxt = MemoryContextSwitchTo(currentEventTriggerState->cxt);

obj = palloc0(sizeof(SQLDropObject));
obj->address = *object;
obj->original = original;
obj->normal = normal;
/* ... special-case temp namespaces, column defaults, triggers, policies ... */

/* object identity, objname and objargs */
obj->objidentity =
    getObjectIdentityParts(&obj->address, &obj->addrnames, &obj->addrargs, false);
obj->objecttype = getObjectTypeDescription(&obj->address, false);

slist_push_head(&(currentEventTriggerState->SQLDropList), &obj->next);
MemoryContextSwitchTo(oldcxt);

When EventTriggerSQLDrop fires, it sets in_sql_drop = true (under a PG_TRY so it is always reset) and runs the triggers. The trigger function reaches the collected list by calling pg_event_trigger_dropped_objects, which is guarded so it errors unless called from inside a sql_drop trigger:

// pg_event_trigger_dropped_objects (excerpt) — src/backend/commands/event_trigger.c
if (!currentEventTriggerState ||
    !currentEventTriggerState->in_sql_drop)
    ereport(ERROR,
            (errcode(ERRCODE_E_R_I_E_EVENT_TRIGGER_PROTOCOL_VIOLATED),
             errmsg("%s can only be called in a sql_drop event trigger function",
                    "pg_event_trigger_dropped_objects()")));

InitMaterializedSRF(fcinfo, 0);

slist_foreach(iter, &(currentEventTriggerState->SQLDropList))
{
    SQLDropObject *obj = slist_container(SQLDropObject, next, iter.cur);
    /* emit (classid, objid, objsubid, original, normal, is_temporary,
       object_type, schema_name, object_name, object_identity,
       address_names, address_args) */
    tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
}

DDL command collection and pg_event_trigger_ddl_commands

The richest feature is command collection: capturing a structured representation of each DDL command so that a ddl_command_end trigger can inspect exactly which objects were touched and how. This is what makes generic schema replication possible. The unit of capture is a CollectedCommand, a tagged union over the kinds of command that need distinct handling:

// CollectedCommand (excerpt) — src/include/tcop/deparse_utility.h
typedef enum CollectedCommandType
{
    SCT_Simple, SCT_AlterTable, SCT_Grant, SCT_AlterOpFamily,
    SCT_AlterDefaultPrivileges, SCT_CreateOpClass, SCT_AlterTSConfig,
} CollectedCommandType;

typedef struct CollectedCommand
{
    CollectedCommandType type;
    bool        in_extension;
    Node       *parsetree;
    union {
        struct { ObjectAddress address; ObjectAddress secondaryObject; } simple;
        struct { Oid objectId; Oid classId; List *subcmds; } alterTable;
        struct { InternalGrant *istmt; } grant;
        /* ... opfam, createopc, atscfg, defprivs ... */
    } d;
    struct CollectedCommand *parent;    /* when nested */
} CollectedCommand;

Most commands take the simple path: ProcessUtilitySlow calls EventTriggerCollectSimpleCommand with the ObjectAddress of the thing the command created or altered. The collector copies the parse tree (so it survives past the command) and appends a SCT_Simple record to commandList:

// EventTriggerCollectSimpleCommand (excerpt) — src/backend/commands/event_trigger.c
if (!currentEventTriggerState ||
    currentEventTriggerState->commandCollectionInhibited)
    return;

oldcxt = MemoryContextSwitchTo(currentEventTriggerState->cxt);
command = palloc(sizeof(CollectedCommand));
command->type = SCT_Simple;
command->in_extension = creating_extension;
command->d.simple.address = address;
command->d.simple.secondaryObject = secondaryObject;
command->parsetree = copyObject(parsetree);

currentEventTriggerState->commandList =
    lappend(currentEventTriggerState->commandList, command);
MemoryContextSwitchTo(oldcxt);

ALTER TABLE is special because one statement carries many subcommands (add column, set default, add constraint), and the relation OID may not be known until partway through. So it uses a three-call protocol: EventTriggerAlterTableStart pushes a SCT_AlterTable record onto currentCommand (a pending slot, not yet on the list); EventTriggerAlterTableRelid fills in the OID once known; EventTriggerCollectAlterTableSubcmd appends each subcommand’s ObjectAddress; and EventTriggerAlterTableEnd finally moves the populated record onto commandList — but only if it actually had subcommands:

// EventTriggerAlterTableEnd (excerpt) — src/backend/commands/event_trigger.c
parent = currentEventTriggerState->currentCommand->parent;

/* If no subcommands, don't collect */
if (currentEventTriggerState->currentCommand->d.alterTable.subcmds != NIL)
{
    oldcxt = MemoryContextSwitchTo(currentEventTriggerState->cxt);
    currentEventTriggerState->commandList =
        lappend(currentEventTriggerState->commandList,
                currentEventTriggerState->currentCommand);
    MemoryContextSwitchTo(oldcxt);
}
else
    pfree(currentEventTriggerState->currentCommand);

currentEventTriggerState->currentCommand = parent;

The parent pointer makes currentCommand a stack too, so a nested ALTER TABLE (one triggered by another) nests correctly. A handful of commands that do not fit the simple or alter-table shapes have their own collectors: EventTriggerCollectGrant (GRANT/REVOKE), EventTriggerCollectAlterOpFam, EventTriggerCollectCreateOpClass, EventTriggerCollectAlterTSConfig, and EventTriggerCollectAlterDefPrivs. There is also an inhibit/undo pair (EventTriggerInhibitCommandCollection) for code paths that must not be double-counted.

Finally, a ddl_command_end trigger reads the whole list by calling pg_event_trigger_ddl_commands, which walks commandList and, for each record, reconstructs the object identity, type, and schema and emits a row whose last column is the opaque pg_ddl_command value (the CollectedCommand pointer itself), which an extension’s deparse function can later turn into runnable SQL:

// pg_event_trigger_ddl_commands (excerpt) — src/backend/commands/event_trigger.c
if (!currentEventTriggerState)
    ereport(ERROR,
            (errcode(ERRCODE_E_R_I_E_EVENT_TRIGGER_PROTOCOL_VIOLATED),
             errmsg("%s can only be called in an event trigger function",
                    "pg_event_trigger_ddl_commands()")));

InitMaterializedSRF(fcinfo, 0);

foreach(lc, currentEventTriggerState->commandList)
{
    CollectedCommand *cmd = lfirst(lc);
    /* IF NOT EXISTS no-op: skip records with InvalidOid object */
    if (cmd->type == SCT_Simple && !OidIsValid(cmd->d.simple.address.objectId))
        continue;
    switch (cmd->type) { /* SCT_Simple/AlterTable/... vs Grant vs DefPrivs */ }
    /* last value is PointerGetDatum(cmd) -> type pg_ddl_command */
    tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
}

flowchart TB
  A["EventTriggerBeginCompleteQuery<br/>trackDroppedObjectsNeeded? push state"] --> B["execute DDL"]
  B --> C["simple command<br/>EventTriggerCollectSimpleCommand<br/>SCT_Simple -> commandList"]
  B --> D["ALTER TABLE<br/>AlterTableStart / Relid /<br/>CollectAlterTableSubcmd / AlterTableEnd"]
  B --> E["GRANT, OPFAMILY, OPCLASS,<br/>TS CONFIG, DEF PRIVS<br/>specialized collectors"]
  C --> F["ddl_command_end trigger calls<br/>pg_event_trigger_ddl_commands"]
  D --> F
  E --> F
  F --> G["walk commandList<br/>emit rows incl. pg_ddl_command value"]
  G --> H["extension deparse function<br/>turns pg_ddl_command into SQL text"]

Figure 2 — DDL command collection. During execution each command type appends a CollectedCommand (via the matching EventTriggerCollect* call) to the per-command commandList; a ddl_command_end trigger reads the whole list back through pg_event_trigger_ddl_commands. (Flow from the collection routines in event_trigger.c and the call sites in utility.c.)

The login event and its fast-path flag

The login event fires on every new session, which is a hot path, so PostgreSQL avoids paying the cost of an event-trigger cache build on connections to databases that have no login trigger. A boolean column pg_database.dathasloginevt records whether the current database has any enabled login trigger; EventTriggerOnLogin checks the cached MyDatabaseHasLoginEventTriggers flag and returns immediately if it is false:

// EventTriggerOnLogin (excerpt) — src/backend/commands/event_trigger.c
if (!IsUnderPostmaster || !event_triggers ||
    !OidIsValid(MyDatabaseId) || !MyDatabaseHasLoginEventTriggers)
    return;

StartTransactionCommand();
runlist = EventTriggerCommonSetup(NULL, EVT_Login, "login", &trigdata, false);
if (runlist != NIL)
{
    PushActiveSnapshot(GetTransactionSnapshot());
    EventTriggerInvoke(runlist, &trigdata);
    list_free(runlist);
    PopActiveSnapshot();
}

CreateEventTrigger (and AlterEventTrigger when enabling) sets the dathasloginevt flag via SetDatabaseHasLoginEventTriggers. The flag is sticky on drop: it is not cleared when a login trigger is dropped, because doing so on the drop path would complicate the multiple-trigger case. Instead, EventTriggerOnLogin opportunistically clears the flag when it finds the flag set but no login triggers actually present — taking a conditional lock so it does not block the connection, and skipping the clear entirely on a hot standby (where it cannot take the lock and the flag is fixed up by WAL replay from the primary). This is a self-healing optimization rather than exact bookkeeping.

Source Walkthrough

The event-trigger machinery is almost entirely contained in one file — src/backend/commands/event_trigger.c (~2400 lines) — plus the syscache-backed cache in evtcache.c. The firing call sites, by contrast, are scattered: they live wherever a DDL event semantically happens (utility.c for the DDL command brackets, tablecmds.c for table rewrite, postgres.c for login, dependency.c for drops). This “one library, many hand-placed call sites” shape is the through-line of the whole subsystem; the walkthrough groups symbols by that split.

Catalog DDL — creating and altering event triggers (event_trigger.c)

CreateEventTrigger is the entry point for CREATE EVENT TRIGGER. It enforces superuser, validates the event name against the five legal spellings, validates the WHEN filter list (validate_ddl_tags, which delegates per-tag to command_tag_event_trigger_ok), checks the trigger function’s return type is the event_trigger pseudo-type, and inserts the pg_event_trigger row via insert_event_trigger_tuple. For a login event it additionally calls SetDatabaseHasLoginEventTriggers to set the pg_database.dathasloginevt fast-path flag:

// CreateEventTrigger / insert_event_trigger_tuple (excerpt) — src/backend/commands/event_trigger.c
/* Login event triggers are flagged on the database. */
if (strcmp(stmt->eventname, "login") == 0)
    SetDatabaseHasLoginEventTriggers();

return insert_event_trigger_tuple(stmt->trigname, stmt->eventname,
                                  evtowner, funcoid, tags);

AlterEventTrigger handles ALTER EVENT TRIGGER ... ENABLE/DISABLE, flipping evtenabled between TRIGGER_DISABLED, TRIGGER_FIRES_ON_ORIGIN, TRIGGER_FIRES_ON_REPLICA, and TRIGGER_FIRES_ALWAYS — the same 'D'/'O'/'R'/'A' encoding used for ordinary triggers, which is what filter_event_trigger later tests against SessionReplicationRole. When it enables a login trigger it also sets the database flag.

The firing functions and the common path (event_trigger.c)

The five public EventTrigger* firing functions each do event-specific preamble, then converge on EventTriggerCommonSetup:

• EventTriggerDDLCommandStart — guards against running before the catalogs are ready (bootstrap, standalone) via EventTriggerSupportsObjectType / a currentEventTriggerState check, then fires EVT_DDLCommandStart.
• EventTriggerDDLCommandEnd — fires EVT_DDLCommandEnd; this is the event whose triggers may call pg_event_trigger_ddl_commands.
• EventTriggerSQLDrop — sets in_sql_drop, fires EVT_SQLDrop, and always resets the flag under PG_TRY.
• EventTriggerTableRewrite — stashes table_rewrite_oid / table_rewrite_reason so pg_event_trigger_table_rewrite_oid / _reason can read them, then fires EVT_TableRewrite.
• EventTriggerOnLogin — the early-out hot path described above; it runs in its own transaction because login fires outside the normal statement loop.

EventTriggerCommonSetup (the cache probe + filter + node build) and EventTriggerInvoke (the fmgr loop with CommandCounterIncrement between functions) are the shared core; filter_event_trigger is the per-trigger predicate. EventTriggerGetTag resolves the parse tree to a CommandTag for the filter test (and returns CMDTAG_UNKNOWN for the login event, which has no command).

Per-command state lifecycle (event_trigger.c)

EventTriggerBeginCompleteQuery pushes a fresh EventTriggerQueryState onto the currentEventTriggerState stack (allocating its own MemoryContext) and returns whether a cleanup is owed; EventTriggerEndCompleteQuery pops it and MemoryContextDeletes the context. The push is conditional — it only happens for a complete top-level query, so sub-commands reuse the outer state. The pairing is always inside a PG_TRY/PG_FINALLY at the call site so an error in the command body cannot leak the state:

// EventTriggerBeginCompleteQuery (excerpt) — src/backend/commands/event_trigger.c
/* Currently, we don't allow nested event-trigger query states. */
if (currentEventTriggerState)
    return false;

cxt = AllocSetContextCreate(TopMemoryContext, "event trigger state",
                            ALLOCSET_DEFAULT_SIZES);
state = MemoryContextAllocZero(cxt, sizeof(EventTriggerQueryState));
state->cxt = cxt;
slist_init(&(state->SQLDropList));
state->in_sql_drop = false;
state->table_rewrite_oid = InvalidOid;
state->commandList = NIL;
state->previous = currentEventTriggerState;
currentEventTriggerState = state;
return true;

Side channels read by the SQL functions (event_trigger.c)

The trigger-visible SQL functions are the read side of the two side channels:

• pg_event_trigger_dropped_objects — walks SQLDropList; guarded to the sql_drop event by the in_sql_drop check.
• pg_event_trigger_ddl_commands — walks commandList; guarded to a live currentEventTriggerState.
• pg_event_trigger_table_rewrite_oid / pg_event_trigger_table_rewrite_reason — return the stashed table-rewrite OID/reason; guarded to the table_rewrite event.

The write side is EventTriggerSQLDropAddObject (called from dependency.c per dropped object) and the EventTriggerCollect* family (called from the DDL execution path per affected object).

Command collection (event_trigger.c + deparse_utility.h)

EventTriggerCollectSimpleCommand is the common collector; the ALTER TABLE protocol uses EventTriggerAlterTableStart → EventTriggerAlterTableRelid → EventTriggerCollectAlterTableSubcmd → EventTriggerAlterTableEnd; and the special-shape collectors are EventTriggerCollectGrant, EventTriggerCollectAlterOpFam, EventTriggerCollectCreateOpClass, EventTriggerCollectAlterTSConfig, and EventTriggerCollectAlterDefPrivs. EventTriggerInhibitCommandCollection / EventTriggerUndoInhibitCommandCollection bracket code paths that must not be double-counted. The CollectedCommand tagged union lives in deparse_utility.h.

The cache (evtcache.c)

EventCacheLookup is the only public entry; it lazily calls BuildEventTriggerCache when the per-backend cache is stale. BuildEventTriggerCache scans pg_event_trigger in name order (using systable_beginscan_ordered, which is why a sound index is required and why event triggers are off in standalone mode), skips disabled rows, maps the event name to an EventTriggerEvent, and compiles evttags into a Bitmapset via DecodeTextArrayToBitmapset. InvalidateEventCacheCallback (registered on the pg_event_trigger syscache) flips the cache to ETCS_NEEDS_REBUILD so the next lookup rebuilds it:

// InvalidateEventCacheCallback (excerpt) — src/backend/utils/cache/evtcache.c
static void
InvalidateEventCacheCallback(Datum arg, int cacheid, uint32 hashvalue)
{
    /*
     * If the cache isn't valid, then there might be a rebuild in progress, so
     * we can't immediately blow it away.  But if it is valid, then recursive
     * rebuilds are impossible, so we can immediately reset it.
     */
    if (EventTriggerCacheState == ETCS_VALID)
        EventTriggerCacheState = ETCS_NEEDS_REBUILD;
}

The firing call sites (outside event_trigger.c)

The four “where does the event actually happen?” call sites are the part a reader most easily loses, so they get explicit position hints:

• ProcessUtilitySlow (utility.c) — owns the ddl_command_start / sql_drop / ddl_command_end brackets and most EventTriggerCollect* calls, all inside one PG_TRY.
• ATRewriteTables (tablecmds.c) — the single EventTriggerTableRewrite call, fired once just before the heap rewrite.
• PostgresMain (postgres.c) — the single EventTriggerOnLogin call, after authentication and before the message loop.
• deleteOneObject / drop paths (dependency.c) — call EventTriggerSQLDropAddObject (not re-excerpted here; owned by postgres-ddl-execution.md).

Position hints (as of 2026-06-05, REL_18 273fe94)

Symbol	File	Line
EventTriggerEvent (enum)	src/include/utils/evtcache.h	20
EventTriggerCacheItem	src/include/utils/evtcache.h	29
EventCacheLookup (decl)	src/include/utils/evtcache.h	36
EventTriggerData	src/include/commands/event_trigger.h	24
CALLED_AS_EVENT_TRIGGER	src/include/commands/event_trigger.h	49
CreateEventTrigger	src/backend/commands/event_trigger.c	124
validate_ddl_tags	src/backend/commands/event_trigger.c	216
SetDatabaseHasLoginEventTriggers	src/backend/commands/event_trigger.c	390
AlterEventTrigger	src/backend/commands/event_trigger.c	427
filter_event_trigger	src/backend/commands/event_trigger.c	598
EventTriggerCommonSetup	src/backend/commands/event_trigger.c	638
EventTriggerDDLCommandStart	src/backend/commands/event_trigger.c	725
EventTriggerDDLCommandEnd	src/backend/commands/event_trigger.c	776
EventTriggerSQLDrop	src/backend/commands/event_trigger.c	824
EventTriggerOnLogin	src/backend/commands/event_trigger.c	897
EventTriggerTableRewrite	src/backend/commands/event_trigger.c	1011
EventTriggerInvoke	src/backend/commands/event_trigger.c	1076
EventTriggerBeginCompleteQuery	src/backend/commands/event_trigger.c	1191
EventTriggerEndCompleteQuery	src/backend/commands/event_trigger.c	1235
EventTriggerSQLDropAddObject	src/backend/commands/event_trigger.c	1285
pg_event_trigger_dropped_objects	src/backend/commands/event_trigger.c	1532
EventTriggerCollectSimpleCommand	src/backend/commands/event_trigger.c	1723
EventTriggerAlterTableStart	src/backend/commands/event_trigger.c	1761
EventTriggerAlterTableEnd	src/backend/commands/event_trigger.c	1848
EventTriggerCollectGrant	src/backend/commands/event_trigger.c	1886
pg_event_trigger_ddl_commands	src/backend/commands/event_trigger.c	2063
BuildEventTriggerCache	src/backend/utils/cache/evtcache.c	77
EventCacheLookup	src/backend/utils/cache/evtcache.c	63
EventTriggerBeginCompleteQuery (call)	src/backend/tcop/utility.c	1110
EventTriggerDDLCommandStart (call)	src/backend/tcop/utility.c	1116
EventTriggerSQLDrop (call)	src/backend/tcop/utility.c	1932
EventTriggerDDLCommandEnd (call)	src/backend/tcop/utility.c	1933
EventTriggerEndCompleteQuery (call)	src/backend/tcop/utility.c	1939
EventTriggerTableRewrite (call)	src/backend/commands/tablecmds.c	5962
EventTriggerOnLogin (call)	src/backend/tcop/postgres.c	4373

Source verification (as of 2026-06-05)

Verified facts

• There are exactly five event types, enumerated in evtcache.h. Verified by reading the EventTriggerEvent enum (EVT_DDLCommandStart, EVT_DDLCommandEnd, EVT_SQLDrop, EVT_TableRewrite, EVT_Login) and confirming CreateEventTrigger rejects any eventname other than the five SQL spellings ddl_command_start, ddl_command_end, sql_drop, login, table_rewrite. (No PG19-only events; this set is REL_18.)

• EventTriggerData carries exactly three payload fields plus the NodeTag. Verified in event_trigger.h: event (name), parsetree, and tag; richer data is reached only through the SQL functions. CALLED_AS_EVENT_TRIGGER tests IsA(context, EventTriggerData).

• The DDL brackets fire from ProcessUtilitySlow, not from a generic dispatcher. Verified in utility.c: EventTriggerBeginCompleteQuery at the top, EventTriggerDDLCommandStart guarded by isCompleteQuery, and EventTriggerSQLDrop immediately followed by EventTriggerDDLCommandEnd, with EventTriggerEndCompleteQuery in the PG_FINALLY. sql_drop fires before ddl_command_end (adjacent lines 1932/1933).

• isCompleteQuery suppresses event firing for sub-commands. Verified: isCompleteQuery = (context != PROCESS_UTILITY_SUBCOMMAND), and every firing call in the block is gated on it, so a recursively issued sub-command does not re-fire the brackets.

• table_rewrite fires exactly once from ATRewriteTables. Verified in tablecmds.c: the call is preceded by the comment “And fire it only once” and passes tab->relid and tab->rewrite as the OID and reason.

• login fires once from PostgresMain after authentication. Verified in postgres.c: a single EventTriggerOnLogin() call after the MemoryContextSwitchTo(TopMemoryContext), and EventTriggerOnLogin itself early-returns unless IsUnderPostmaster && event_triggers && OidIsValid(MyDatabaseId) && MyDatabaseHasLoginEventTriggers.

• The common path is cache-probe → filter → invoke. EventTriggerCommonSetup calls EventCacheLookup(event) and returns NIL immediately when no triggers exist; otherwise it filters by SessionReplicationRole and the WHEN tag Bitmapset (filter_event_trigger) and builds the EventTriggerData node. EventTriggerInvoke runs each surviving function with a CommandCounterIncrement between them.

• The cache is per-event, name-ordered, and syscache-invalidated. Verified in evtcache.c: BuildEventTriggerCache scans pg_event_trigger ordered (deterministic firing order), and InvalidateEventCacheCallback flips ETCS_VALID → ETCS_NEEDS_REBUILD rather than freeing in place (to survive a rebuild already in progress).

• The two data side channels are write-during-execution, read-from-trigger. EventTriggerSQLDropAddObject appends to SQLDropList while dependency.c drops objects; EventTriggerCollectSimpleCommand / the ALTER TABLE protocol / the specialized collectors append CollectedCommands to commandList. pg_event_trigger_dropped_objects and pg_event_trigger_ddl_commands read them back, each guarded to its event with an ERRCODE_E_R_I_E_EVENT_TRIGGER_PROTOCOL_VIOLATED error otherwise.

• login triggers have a database-level fast-path flag. Verified: pg_database.dathasloginevt is set by SetDatabaseHasLoginEventTriggers from CreateEventTrigger/ AlterEventTrigger, cached per-backend as MyDatabaseHasLoginEventTriggers, and opportunistically self-healed in EventTriggerOnLogin (conditional-lock clear, skipped on a standby).

Open questions

1. Worst-case cost of the self-healing dathasloginevt clear under contention. EventTriggerOnLogin takes a conditional lock to clear a stale flag and silently skips on failure, so under a storm of concurrent logins to a database that just dropped its last login trigger, the flag may stay set (and every login pays a cache build) for an unbounded number of connections until one wins the conditional lock. The practical frequency is unmeasured here. Investigation path: instrument the clear path under a connection-storm workload.

2. How completely command collection covers exotic DDL. The CollectedCommand union special-cases ALTER TABLE, GRANT, operator families/classes, text-search configs, and default privileges; everything else takes the SCT_Simple path with a single ObjectAddress. Whether every REL_18 utility statement that touches the catalog reliably produces a usable record (vs. an InvalidOid-address no-op that pg_event_trigger_ddl_commands skips) is not exhaustively verified. Investigation path: enumerate ProcessUtilitySlow’s nodeTag cases against the collector calls.

3. Interaction of nested event-trigger states with command collection. EventTriggerBeginCompleteQuery currently refuses to nest (if (currentEventTriggerState) return false;), so a DDL command issued from inside a trigger reuses the outer state rather than pushing a new one — yet EventTriggerQueryState.previous and CollectedCommand.parent both exist as if nesting were supported. The exact circumstances under which the previous/parent chains are actually exercised in REL_18 deserve a focused trace.

Beyond PostgreSQL — Comparative Designs & Research Frontiers

• Oracle system/DDL triggers. Oracle’s BEFORE/AFTER system events (CREATE, ALTER, DROP, LOGON, LOGOFF, SERVERERROR, STARTUP/SHUTDOWN) are the closest analogue, and the LOGON event maps directly onto PostgreSQL’s login. Oracle exposes attribute functions (ora_dict_obj_name, ora_sysevent) that play the role of PostgreSQL’s pg_event_trigger_* set-returning functions. A side-by-side of Oracle’s attribute-function model against PostgreSQL’s side-channel-plus-SRF model would sharpen the tradeoff between “push context into the trigger as pseudo-columns” and “let the trigger pull structured context from per-command state.”

• SQL Server DDL triggers and EVENTDATA(). SQL Server keys DDL triggers on event groups (DDL_TABLE_EVENTS, DDL_LOGIN_EVENTS, …) and hands the trigger an EVENTDATA() XML blob rather than a typed node plus SRFs. PostgreSQL’s choice — a typed EventTriggerData node for the three cheap fields and lazily-materialized SRFs for the expensive lists — is a deliberately different point: it avoids serializing a description the trigger may never inspect.

• DDL replication and logical decoding of schema. Command collection exists to make generic schema replication possible: an extension reads pg_event_trigger_ddl_commands, deparses each pg_ddl_command back to SQL text, and ships it. This is exactly the schema-change-capture gap Kleppmann’s Designing Data-Intensive Applications (ch. 11, change capture; raw/system/textbooks/) names for log-based replication, and the frontier work on logical DDL replication (built into PostgreSQL’s logical-replication roadmap and several extensions) is the natural continuation. The deparse half is owned by deparse_utility.h / ddl_deparse and is out of scope here.

• System R and the catalog-as-tables lineage. Event triggers fire on catalog mutation, and the reason the catalog is mutable-and-observable at all traces to System R’s decision to store metadata in ordinary relations (Astrahan et al. 1976; dbms-papers/systemr.md). An event trigger is, in a sense, a DML trigger on the system catalog expressed at the DDL-command granularity instead of the catalog-row granularity — a framing that connects this doc to the ECA trigger theory in postgres-triggers.md.

• ECA rules and active databases. The event-condition-action model (Silberschatz 7e §5.3) was the subject of a large “active database” research literature in the 1990s (HiPAC, Ariel, Starburst). Event triggers are a narrow, production-hardened slice of that vision — events restricted to DDL and session lifecycle, conditions restricted to a tag filter and replication role. Mapping PostgreSQL’s five events onto the general ECA taxonomy (and noting what it deliberately omits — temporal events, composite events, coupling modes) would be a clean theory companion.

Sources

In-tree source files (REL_18_STABLE, commit 273fe94)

• src/backend/commands/event_trigger.c — the whole subsystem: CreateEventTrigger/AlterEventTrigger, the five EventTrigger* firing functions, EventTriggerCommonSetup/EventTriggerInvoke/ filter_event_trigger, the EventTriggerQueryState lifecycle (Begin/EndCompleteQuery), the drop side channel (EventTriggerSQLDropAddObject, pg_event_trigger_dropped_objects), and command collection (the EventTriggerCollect*/AlterTable* family, pg_event_trigger_ddl_commands).
• src/include/commands/event_trigger.h — EventTriggerData and the CALLED_AS_EVENT_TRIGGER guard macro.
• src/include/utils/evtcache.h — EventTriggerEvent enum, EventTriggerCacheItem, EventCacheLookup declaration.
• src/backend/utils/cache/evtcache.c — EventCacheLookup, BuildEventTriggerCache, DecodeTextArrayToBitmapset, InvalidateEventCacheCallback.
• src/include/tcop/deparse_utility.h — CollectedCommand tagged union and CollectedCommandType.
• src/backend/tcop/utility.c — ProcessUtilitySlow: the DDL bracket call sites and most EventTriggerCollect* calls, inside one PG_TRY.
• src/backend/commands/tablecmds.c — ATRewriteTables: the single EventTriggerTableRewrite call site.
• src/backend/tcop/postgres.c — PostgresMain: the single EventTriggerOnLogin call site.

Papers and textbook chapters

• Database System Concepts (Silberschatz, Korth, Sudarshan, 7e), ch. 5 “Advanced SQL”, §5.3 “Triggers” — the event-condition-action model and the cautions on cascading/implicit triggers (knowledge/research/dbms-general/).
• Astrahan, M. M. et al. (1976). “System R: Relational Approach to Database Management.” ACM TODS 1(2):97-137 — catalog-as-relations, the lineage behind observable catalog mutation (knowledge/research/dbms-papers/systemr.md).
• Kleppmann, M. (2017). Designing Data-Intensive Applications, ch. 11 (change capture) — the schema-change-replication gap that DDL command collection answers (raw/system/textbooks/).

Sibling docs (cross-references — mechanism owned there, not duplicated here)

• postgres-triggers.md — row/statement (DML) triggers: TriggerData, ExecCallTriggerFunc, the SQL-standard BEFORE/AFTER model that event triggers are deliberately not.
• postgres-ddl-execution.md — ProcessUtility/ProcessUtilitySlow dispatch, the per-statement DDL execution paths, and the dependency.c drop machinery that feeds EventTriggerSQLDropAddObject.
• postgres-architecture-overview.md — where utility-command processing and the catalog sit in the overall backend pipeline.