How to Use Event Triggers in PostgreSQL

PostgreSQL provides a very useful event-based trigger system to detect changes to data and automatically perform subsequent tasks. For example, triggers can be used to detect changes on one table and perform related changes on another table; a common form of this usage is a centralized audit table, wherein all changes to relevant tables are monitored and then recorded to the audit table with triggers.

One of the missing features in PostgreSQL’s implementation of triggers was that DDL could not be detected very reliably. With the concept of event triggers introduced in v. 9.3, this is now possible.

The old way: Logging DDL

Prior to the implementation of event triggers, DDL could be detected and monitored by setting “log_statement = ‘ddl’” (or “log_statement = ‘all’”), which would in turn record the DDL statement into the PostgreSQL logs. The primary drawback to this mechanism is that—especially for a very busy database with lots of DML logging—a DDL statement would be buried in the logs. Any attempt to parse the logs would be time-consuming and tedious, not to mention that with log rotation some DDL history could be lost.

Another old way: Tracking commit timestamps

Starting from v. 9.1, “track_commit_timestamp” became an available option for those who wanted to have a timestamp associated with their transactions. This paved the way for DDL tracking by peering into changes to the “pg_class” table:

postgres=# show track_commit_timestamp ;

 track_commit_timestamp

------------------------

 on

(1 row)



postgres=# create table students (id int, name text);

CREATE TABLE



postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = students;

    Pg_xact_commit_timestamp   |  oid  |  relname  

-------------------------------+-------+------------

 2019-05-25 17:00:48.765092+00 | 16655 | students

(1 row)



postgres=# alter table students add column phone_number text;

ALTER TABLE

postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = 'students';

    pg_xact_commit_timestamp   |  oid  |  relname  

-------------------------------+-------+------------

 2019-05-25 17:01:25.200101+00 | 16655 | students

(1 row)

Under the hood, when DDL is performed on a table, the table’s entry in “pg_class” is updated. By checking the “xmin” value of the row belonging to the table, users can see when the last DDL was performed. However, the problem with detecting DDL in this way is that the alterations to the table are tracked based on changes to the “xmin” value, and therefore only the **latest** DDL event is recorded. As such, the “pg_class” table needs to be frequently polled if a running history of changes is to be available; there’s no automated way to generate this table.

Event triggers

With event triggers, there is now a mechanism to update a table when DDL occurs. To leverage this, we first create a table that will store all the DDL history:

CREATE TABLE ddl_history (

  id serial primary key,

  ddl_date timestamptz,

  ddl_tag text,

  object_name text

);

Then, we need two functions: one to track the creation and alteration of objects, and another to track drops of objects:

CREATE OR REPLACE FUNCTION log_ddl()

  RETURNS event_trigger AS $$

DECLARE

  audit_query TEXT;

  r RECORD;

BEGIN

  IF tg_tag <> 'DROP TABLE'

  THEN

    r := pg_event_trigger_ddl_commands();

    INSERT INTO ddl_history (ddl_date, ddl_tag, object_name) VALUES (statement_timestamp(), tg_tag, r.object_identity);

  END IF;

END;

$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION log_ddl_drop()

  RETURNS event_trigger AS $$

DECLARE

  audit_query TEXT;

  r RECORD;

BEGIN

  IF tg_tag = 'DROP TABLE'

  THEN

    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() 

    LOOP

      INSERT INTO ddl_history (ddl_date, ddl_tag, object_name) VALUES (statement_timestamp(), tg_tag, r.object_identity);

    END LOOP;

  END IF;

END;

$$ LANGUAGE plpgsql;

Once these two functions are created, we create the event triggers:

CREATE EVENT TRIGGER log_ddl_info ON ddl_command_end EXECUTE PROCEDURE log_ddl();

CREATE EVENT TRIGGER log_ddl_drop_info ON sql_drop EXECUTE PROCEDURE log_ddl_drop();

Once these triggers are in place, we can proceed to test with our DDL:

postgres=# CREATE TABLE testtable (id int, first_name text);

CREATE TABLE

postgres=# ALTER TABLE testtable ADD COLUMN last_name text;

ALTER TABLE

postgres=# ALTER TABLE testtable ADD COLUMN midlname text;

ALTER TABLE

postgres=# ALTER TABLE testtable RENAME COLUMN midlname TO middle_name;

ALTER TABLE

postgres=# ALTER TABLE testtable DROP COLUMN middle_name;

ALTER TABLE

postgres=# DROP TABLE testtable;

DROP TABLE

postgres=# SELECT * FROM ddl_history;

 id |           ddl_date            |   ddl_tag    |          object_name          

----+-------------------------------+--------------+-------------------------------

  1 | 2019-11-12 17:24:45.34735+00  | CREATE TABLE | public.testtable

  2 | 2019-11-12 17:24:48.259176+00 | ALTER TABLE  | public.testtable

  3 | 2019-11-12 17:24:51.727732+00 | ALTER TABLE  | public.testtable

  4 | 2019-11-12 17:24:54.883063+00 | ALTER TABLE  | public.testtable.middle_name

  5 | 2019-11-12 17:25:01.079002+00 | ALTER TABLE  | public.testtable.middle_name

  6 | 2019-11-12 17:25:01.079002+00 | ALTER TABLE  | public.testtable

  7 | 2019-11-12 17:25:04.077603+00 | DROP TABLE   | public.testtable

  8 | 2019-11-12 17:25:04.077603+00 | DROP TABLE   | public.testtable

  9 | 2019-11-12 17:25:04.077603+00 | DROP TABLE   | public.testtable[]

 10 | 2019-11-12 17:25:04.077603+00 | DROP TABLE   | pg_toast.pg_toast_16399

 11 | 2019-11-12 17:25:04.077603+00 | DROP TABLE   | pg_toast.pg_toast_16399_index

 12 | 2019-11-12 17:25:04.077603+00 | DROP TABLE   | pg_toast.pg_toast_16399

(12 rows)

Conclusion

Event triggers can be a very powerful tool for auditing and security. While the example shown demonstrates how event triggers can be used as an auditing or bookkeeping tool, other possible uses of this feature include:

1. Monitoring DDL performance
2. Restricting certain DDL commands for some users
3. Performing replication of DDL to subscriber nodes in a Logical Replication setup