Event Triggers
==============

event trigger

To supplement the trigger mechanism discussed in [???](#triggers),
PostgreSQL also provides event triggers. Unlike regular triggers, which
are attached to a single table and capture only DML events, event
triggers are global to a particular database and are capable of
capturing DDL events.

Like regular triggers, event triggers can be written in any procedural
language that includes event trigger support, or in C, but not in plain
SQL.

Overview of Event Trigger Behavior {#event-trigger-definition}
==================================

An event trigger fires whenever the event with which it is associated
occurs in the database in which it is defined. Currently, the only
supported events are `ddl_command_start`, `ddl_command_end`,
`table_rewrite` and `sql_drop`. Support for additional events may be
added in future releases.

The `ddl_command_start` event occurs just before the execution of a
`CREATE`, `ALTER`, `DROP`, `SECURITY LABEL`, `COMMENT`, `GRANT` or
`REVOKE` command. No check whether the affected object exists or
doesn\'t exist is performed before the event trigger fires. As an
exception, however, this event does not occur for DDL commands targeting
shared objects MDASH databases, roles, and tablespaces MDASH or for
commands targeting event triggers themselves. The event trigger
mechanism does not support these object types. `ddl_command_start` also
occurs just before the execution of a `SELECT INTO` command, since this
is equivalent to `CREATE TABLE AS`.

The `ddl_command_end` event occurs just after the execution of this same
set of commands. To obtain more details on the DDL operations that took
place, use the set-returning function `pg_event_trigger_ddl_commands()`
from the `ddl_command_end` event trigger code (see
[???](#functions-event-triggers)). Note that the trigger fires after the
actions have taken place (but before the transaction commits), and thus
the system catalogs can be read as already changed.

The `sql_drop` event occurs just before the `ddl_command_end` event
trigger for any operation that drops database objects. To list the
objects that have been dropped, use the set-returning function
`pg_event_trigger_dropped_objects()` from the `sql_drop` event trigger
code (see [???](#functions-event-triggers)). Note that the trigger is
executed after the objects have been deleted from the system catalogs,
so it\'s not possible to look them up anymore.

The `table_rewrite` event occurs just before a table is rewritten by
some actions of the commands `ALTER TABLE` and `ALTER TYPE`. While other
control statements are available to rewrite a table, like `CLUSTER` and
`VACUUM`, the `table_rewrite` event is not triggered by them.

Event triggers (like other functions) cannot be executed in an aborted
transaction. Thus, if a DDL command fails with an error, any associated
`ddl_command_end` triggers will not be executed. Conversely, if a
`ddl_command_start` trigger fails with an error, no further event
triggers will fire, and no attempt will be made to execute the command
itself. Similarly, if a `ddl_command_end` trigger fails with an error,
the effects of the DDL statement will be rolled back, just as they would
be in any other case where the containing transaction aborts.

For a complete list of commands supported by the event trigger
mechanism, see [Event Trigger Firing Matrix](#event-trigger-matrix).

Event triggers are created using the command
[???](#sql-createeventtrigger). In order to create an event trigger, you
must first create a function with the special return type
`event_trigger`. This function need not (and may not) return a value;
the return type serves merely as a signal that the function is to be
invoked as an event trigger.

If more than one event trigger is defined for a particular event, they
will fire in alphabetical order by trigger name.

A trigger definition can also specify a `WHEN` condition so that, for
example, a `ddl_command_start` trigger can be fired only for particular
commands which the user wishes to intercept. A common use of such
triggers is to restrict the range of DDL operations which users may
perform.

Event Trigger Firing Matrix {#event-trigger-matrix}
===========================

[table\_title](#event-trigger-by-command-tag) lists all commands for
which event triggers are supported.

  ------------------------------------------------------------------------------------------------------------------------------------------------
  Command Tag                          `ddl_ZWSPcommand_ZWSPstart`   `ddl_ZWSPcommand_ZWSPend`   `sql_ZWSPdrop`   `table_ZWSPrewrite`   Notes
  ------------------------------------ ----------------------------- --------------------------- ---------------- --------------------- ----------
  `ALTER AGGREGATE`                    `X`                           `X`                         `-`              `-`                   

  `ALTER COLLATION`                    `X`                           `X`                         `-`              `-`                   

  `ALTER CONVERSION`                   `X`                           `X`                         `-`              `-`                   

  `ALTER DOMAIN`                       `X`                           `X`                         `-`              `-`                   

  `ALTER DEFAULT PRIVILEGES`           `X`                           `X`                         `-`              `-`                   

  `ALTER EXTENSION`                    `X`                           `X`                         `-`              `-`                   

  `ALTER FOREIGN DATA WRAPPER`         `X`                           `X`                         `-`              `-`                   

  `ALTER FOREIGN TABLE`                `X`                           `X`                         `X`              `-`                   

  `ALTER FUNCTION`                     `X`                           `X`                         `-`              `-`                   

  `ALTER LANGUAGE`                     `X`                           `X`                         `-`              `-`                   

  `ALTER LARGE OBJECT`                 `X`                           `X`                         `-`              `-`                   

  `ALTER MATERIALIZED VIEW`            `X`                           `X`                         `-`              `-`                   

  `ALTER OPERATOR`                     `X`                           `X`                         `-`              `-`                   

  `ALTER OPERATOR CLASS`               `X`                           `X`                         `-`              `-`                   

  `ALTER OPERATOR FAMILY`              `X`                           `X`                         `-`              `-`                   

  `ALTER POLICY`                       `X`                           `X`                         `-`              `-`                   

  `ALTER PROCEDURE`                    `X`                           `X`                         `-`              `-`                   

  `ALTER PUBLICATION`                  `X`                           `X`                         `-`              `-`                   

  `ALTER ROUTINE`                      `X`                           `X`                         `-`              `-`                   

  `ALTER SCHEMA`                       `X`                           `X`                         `-`              `-`                   

  `ALTER SEQUENCE`                     `X`                           `X`                         `-`              `-`                   

  `ALTER SERVER`                       `X`                           `X`                         `-`              `-`                   

  `ALTER STATISTICS`                   `X`                           `X`                         `-`              `-`                   

  `ALTER SUBSCRIPTION`                 `X`                           `X`                         `-`              `-`                   

  `ALTER TABLE`                        `X`                           `X`                         `X`              `X`                   

  `ALTER TEXT SEARCH CONFIGURATION`    `X`                           `X`                         `-`              `-`                   

  `ALTER TEXT SEARCH DICTIONARY`       `X`                           `X`                         `-`              `-`                   

  `ALTER TEXT SEARCH PARSER`           `X`                           `X`                         `-`              `-`                   

  `ALTER TEXT SEARCH TEMPLATE`         `X`                           `X`                         `-`              `-`                   

  `ALTER TRIGGER`                      `X`                           `X`                         `-`              `-`                   

  `ALTER TYPE`                         `X`                           `X`                         `-`              `X`                   

  `ALTER USER MAPPING`                 `X`                           `X`                         `-`              `-`                   

  `ALTER VIEW`                         `X`                           `X`                         `-`              `-`                   

  `COMMENT`                            `X`                           `X`                         `-`              `-`                   Only for
                                                                                                                                        local
                                                                                                                                        objects

  `CREATE ACCESS METHOD`               `X`                           `X`                         `-`              `-`                   

  `CREATE AGGREGATE`                   `X`                           `X`                         `-`              `-`                   

  `CREATE CAST`                        `X`                           `X`                         `-`              `-`                   

  `CREATE COLLATION`                   `X`                           `X`                         `-`              `-`                   

  `CREATE CONVERSION`                  `X`                           `X`                         `-`              `-`                   

  `CREATE DOMAIN`                      `X`                           `X`                         `-`              `-`                   

  `CREATE EXTENSION`                   `X`                           `X`                         `-`              `-`                   

  `CREATE FOREIGN DATA WRAPPER`        `X`                           `X`                         `-`              `-`                   

  `CREATE FOREIGN TABLE`               `X`                           `X`                         `-`              `-`                   

  `CREATE FUNCTION`                    `X`                           `X`                         `-`              `-`                   

  `CREATE INDEX`                       `X`                           `X`                         `-`              `-`                   

  `CREATE LANGUAGE`                    `X`                           `X`                         `-`              `-`                   

  `CREATE MATERIALIZED VIEW`           `X`                           `X`                         `-`              `-`                   

  `CREATE OPERATOR`                    `X`                           `X`                         `-`              `-`                   

  `CREATE OPERATOR CLASS`              `X`                           `X`                         `-`              `-`                   

  `CREATE OPERATOR FAMILY`             `X`                           `X`                         `-`              `-`                   

  `CREATE POLICY`                      `X`                           `X`                         `-`              `-`                   

  `CREATE PROCEDURE`                   `X`                           `X`                         `-`              `-`                   

  `CREATE PUBLICATION`                 `X`                           `X`                         `-`              `-`                   

  `CREATE RULE`                        `X`                           `X`                         `-`              `-`                   

  `CREATE SCHEMA`                      `X`                           `X`                         `-`              `-`                   

  `CREATE SEQUENCE`                    `X`                           `X`                         `-`              `-`                   

  `CREATE SERVER`                      `X`                           `X`                         `-`              `-`                   

  `CREATE STATISTICS`                  `X`                           `X`                         `-`              `-`                   

  `CREATE SUBSCRIPTION`                `X`                           `X`                         `-`              `-`                   

  `CREATE TABLE`                       `X`                           `X`                         `-`              `-`                   

  `CREATE TABLE AS`                    `X`                           `X`                         `-`              `-`                   

  `CREATE TEXT SEARCH CONFIGURATION`   `X`                           `X`                         `-`              `-`                   

  `CREATE TEXT SEARCH DICTIONARY`      `X`                           `X`                         `-`              `-`                   

  `CREATE TEXT SEARCH PARSER`          `X`                           `X`                         `-`              `-`                   

  `CREATE TEXT SEARCH TEMPLATE`        `X`                           `X`                         `-`              `-`                   

  `CREATE TRIGGER`                     `X`                           `X`                         `-`              `-`                   

  `CREATE TYPE`                        `X`                           `X`                         `-`              `-`                   

  `CREATE USER MAPPING`                `X`                           `X`                         `-`              `-`                   

  `CREATE VIEW`                        `X`                           `X`                         `-`              `-`                   

  `DROP ACCESS METHOD`                 `X`                           `X`                         `X`              `-`                   

  `DROP AGGREGATE`                     `X`                           `X`                         `X`              `-`                   

  `DROP CAST`                          `X`                           `X`                         `X`              `-`                   

  `DROP COLLATION`                     `X`                           `X`                         `X`              `-`                   

  `DROP CONVERSION`                    `X`                           `X`                         `X`              `-`                   

  `DROP DOMAIN`                        `X`                           `X`                         `X`              `-`                   

  `DROP EXTENSION`                     `X`                           `X`                         `X`              `-`                   

  `DROP FOREIGN DATA WRAPPER`          `X`                           `X`                         `X`              `-`                   

  `DROP FOREIGN TABLE`                 `X`                           `X`                         `X`              `-`                   

  `DROP FUNCTION`                      `X`                           `X`                         `X`              `-`                   

  `DROP INDEX`                         `X`                           `X`                         `X`              `-`                   

  `DROP LANGUAGE`                      `X`                           `X`                         `X`              `-`                   

  `DROP MATERIALIZED VIEW`             `X`                           `X`                         `X`              `-`                   

  `DROP OPERATOR`                      `X`                           `X`                         `X`              `-`                   

  `DROP OPERATOR CLASS`                `X`                           `X`                         `X`              `-`                   

  `DROP OPERATOR FAMILY`               `X`                           `X`                         `X`              `-`                   

  `DROP OWNED`                         `X`                           `X`                         `X`              `-`                   

  `DROP POLICY`                        `X`                           `X`                         `X`              `-`                   

  `DROP PROCEDURE`                     `X`                           `X`                         `X`              `-`                   

  `DROP PUBLICATION`                   `X`                           `X`                         `X`              `-`                   

  `DROP ROUTINE`                       `X`                           `X`                         `X`              `-`                   

  `DROP RULE`                          `X`                           `X`                         `X`              `-`                   

  `DROP SCHEMA`                        `X`                           `X`                         `X`              `-`                   

  `DROP SEQUENCE`                      `X`                           `X`                         `X`              `-`                   

  `DROP SERVER`                        `X`                           `X`                         `X`              `-`                   

  `DROP STATISTICS`                    `X`                           `X`                         `X`              `-`                   

  `DROP SUBSCRIPTION`                  `X`                           `X`                         `X`              `-`                   

  `DROP TABLE`                         `X`                           `X`                         `X`              `-`                   

  `DROP TEXT SEARCH CONFIGURATION`     `X`                           `X`                         `X`              `-`                   

  `DROP TEXT SEARCH DICTIONARY`        `X`                           `X`                         `X`              `-`                   

  `DROP TEXT SEARCH PARSER`            `X`                           `X`                         `X`              `-`                   

  `DROP TEXT SEARCH TEMPLATE`          `X`                           `X`                         `X`              `-`                   

  `DROP TRIGGER`                       `X`                           `X`                         `X`              `-`                   

  `DROP TYPE`                          `X`                           `X`                         `X`              `-`                   

  `DROP USER MAPPING`                  `X`                           `X`                         `X`              `-`                   

  `DROP VIEW`                          `X`                           `X`                         `X`              `-`                   

  `GRANT`                              `X`                           `X`                         `-`              `-`                   Only for
                                                                                                                                        local
                                                                                                                                        objects

  `IMPORT FOREIGN SCHEMA`              `X`                           `X`                         `-`              `-`                   

  `REFRESH MATERIALIZED VIEW`          `X`                           `X`                         `-`              `-`                   

  `REVOKE`                             `X`                           `X`                         `-`              `-`                   Only for
                                                                                                                                        local
                                                                                                                                        objects

  `SECURITY LABEL`                     `X`                           `X`                         `-`              `-`                   Only for
                                                                                                                                        local
                                                                                                                                        objects

  `SELECT INTO`                        `X`                           `X`                         `-`              `-`                   
  ------------------------------------------------------------------------------------------------------------------------------------------------

  : Event Trigger Support by Command Tag

Writing Event Trigger Functions in C {#event-trigger-interface}
====================================

event trigger

in C

This section describes the low-level details of the interface to an
event trigger function. This information is only needed when writing
event trigger functions in C. If you are using a higher-level language
then these details are handled for you. In most cases you should
consider using a procedural language before writing your event triggers
in C. The documentation of each procedural language explains how to
write an event trigger in that language.

Event trigger functions must use the "version 1" function manager
interface.

When a function is called by the event trigger manager, it is not passed
any normal arguments, but it is passed a "context" pointer pointing to a
EventTriggerData structure. C functions can check whether they were
called from the event trigger manager or not by executing the macro:

    CALLED_AS_EVENT_TRIGGER(fcinfo)

which expands to:

    ((fcinfo)->context != NULL && IsA((fcinfo)->context, EventTriggerData))

If this returns true, then it is safe to cast `fcinfo->context` to type
`EventTriggerData
    *` and make use of the pointed-to EventTriggerData structure. The
function must *not* alter the EventTriggerData structure or any of the
data it points to.

struct EventTriggerData is defined in `commands/event_trigger.h`:

    typedef struct EventTriggerData
    {
        NodeTag     type;
        const char *event;      /* event name */
        Node       *parsetree;  /* parse tree */
        CommandTag  tag;        /* command tag */
    } EventTriggerData;

where the members are defined as follows:

type

:   Always `T_EventTriggerData`.

event

:   Describes the event for which the function is called, one of
    `"ddl_command_start"`, `"ddl_command_end"`, `"sql_drop"`,
    `"table_rewrite"`. See [Overview of Event Trigger
    Behavior](#event-trigger-definition) for the meaning of these
    events.

parsetree

:   A pointer to the parse tree of the command. Check the PostgreSQL
    source code for details. The parse tree structure is subject to
    change without notice.

tag

:   The command tag associated with the event for which the event
    trigger is run, for example `"CREATE FUNCTION"`.

An event trigger function must return a `NULL` pointer (*not* an SQL
null value, that is, do not set `isNull` true).

A Complete Event Trigger Example {#event-trigger-example}
================================

Here is a very simple example of an event trigger function written in C.
(Examples of triggers written in procedural languages can be found in
the documentation of the procedural languages.)

The function `noddl` raises an exception each time it is called. The
event trigger definition associated the function with the
`ddl_command_start` event. The effect is that all DDL commands (with the
exceptions mentioned in [Overview of Event Trigger
Behavior](#event-trigger-definition)) are prevented from running.

This is the source code of the trigger function:

    #include "postgres.h"
    #include "commands/event_trigger.h"


    PG_MODULE_MAGIC;

    PG_FUNCTION_INFO_V1(noddl);

    Datum
    noddl(PG_FUNCTION_ARGS)
    {
        EventTriggerData *trigdata;

        if (!CALLED_AS_EVENT_TRIGGER(fcinfo))  /* internal error */
            elog(ERROR, "not fired by event trigger manager");

        trigdata = (EventTriggerData *) fcinfo->context;

        ereport(ERROR,
            (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                     errmsg("command \"%s\" denied", trigdata->tag)));

        PG_RETURN_NULL();
    }

After you have compiled the source code (see [???](#dfunc)), declare the
function and the triggers:

    CREATE FUNCTION noddl() RETURNS event_trigger
        AS 'noddl' LANGUAGE C;

    CREATE EVENT TRIGGER noddl ON ddl_command_start
        EXECUTE FUNCTION noddl();

Now you can test the operation of the trigger:

    =# \dy
                         List of event triggers
     Name  |       Event       | Owner | Enabled | Function | Tags
    -------+-------------------+-------+---------+----------+------
     noddl | ddl_command_start | dim   | enabled | noddl    |
    (1 row)

    =# CREATE TABLE foo(id serial);
    ERROR:  command "CREATE TABLE" denied

In this situation, in order to be able to run some DDL commands when you
need to do so, you have to either drop the event trigger or disable it.
It can be convenient to disable the trigger for only the duration of a
transaction:

    BEGIN;
    ALTER EVENT TRIGGER noddl DISABLE;
    CREATE TABLE foo (id serial);
    ALTER EVENT TRIGGER noddl ENABLE;
    COMMIT;

(Recall that DDL commands on event triggers themselves are not affected
by event triggers.)

A Table Rewrite Event Trigger Example {#event-trigger-table-rewrite-example}
=====================================

Thanks to the `table_rewrite` event, it is possible to implement a table
rewriting policy only allowing the rewrite in maintenance windows.

Here\'s an example implementing such a policy.

    CREATE OR REPLACE FUNCTION no_rewrite()
     RETURNS event_trigger
     LANGUAGE plpgsql AS
    $$
    ---
    --- Implement local Table Rewriting policy:
    ---   public.foo is not allowed rewriting, ever
    ---   other tables are only allowed rewriting between 1am and 6am
    ---   unless they have more than 100 blocks
    ---
    DECLARE
      table_oid oid := pg_event_trigger_table_rewrite_oid();
      current_hour integer := extract('hour' from current_time);
      pages integer;
      max_pages integer := 100;
    BEGIN
      IF pg_event_trigger_table_rewrite_oid() = 'public.foo'::regclass
      THEN
            RAISE EXCEPTION 'you''re not allowed to rewrite the table %',
                            table_oid::regclass;
      END IF;

      SELECT INTO pages relpages FROM pg_class WHERE oid = table_oid;
      IF pages > max_pages
      THEN
            RAISE EXCEPTION 'rewrites only allowed for table with less than % pages',
                            max_pages;
      END IF;

      IF current_hour NOT BETWEEN 1 AND 6
      THEN
            RAISE EXCEPTION 'rewrites only allowed between 1am and 6am';
      END IF;
    END;
    $$;

    CREATE EVENT TRIGGER no_rewrite_allowed
                      ON table_rewrite
       EXECUTE FUNCTION no_rewrite();
