0

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.

2
  • Be warned that a view on system catalogs is one of the things that prevents you from upgrading with pg_upgrade. It would be better to use a table function for that (and perhaps a view on that function). Feb 10, 2020 at 14:52
  • That's a great warning, thanks. I think I ran into this moving from PG 10 to PG 11, and didn't know why. I've got several views like this, I'll go back in and change them to set-returning functions. Feb 10, 2020 at 23:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.