I'm using Postgres 11.5 and have been checking out
event_trigger. Amazing. For the uninitiated, event triggers are like table triggers, except for (most) DDL operations. So, CREATE FUNCTION, ALTER FUNCTION, DROP TABLE, and much more. Given that DDL operations are transactional in Postgres, I guess is was manageable to add global traps for these operations.
With an event trigger, you can capture and respond to various DDL events, with limits. So, pretty interesting if, for example, you're trying to track changes to some part of the catalog.
There's a built-in system view named pg_event_trigger that shows what's defined. Like most things pg_catalog, the columns have short, dense names and list the old values of reference objects in other catalog tables. I tend to create my own views for this sort of thing and stash them in a schema named dba. In case it saves someone else a few minutes down the track, here's one version for event triggers:
select evtname as event_trigger_name,
evtevent as event_name,
evtowner::regrole as event_owner,
evtfoid::regproc as function_name,
case when evtenabled = 'A' then 'Always'
when evtenabled = 'D' then 'Disabled'
when evtenabled = 'O' then 'Origin and Local'
when evtenabled = 'R' then 'Replica'
end as session_replication_role_mode,
coalesce(evttags::text,'No TAG filter') as event_tags
from pg_event_trigger;
That's just the query part of what goes into the view.
Asked by Morris de Oryx
(939 rep)
Feb 9, 2020, 10:20 PM