Event Triggers

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
m (Main advantages over ProcessUtility hooks)
m (Expected Features)
Line 17: Line 17:
=== Expected Features ===
=== Expected Features ===
The first release of Event Triggers in PostgreSQL 9.3 and we will have a restricted set of features in that relase.
The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.
==== Use Cases ====
==== Use Cases ====

Revision as of 15:56, 30 January 2013


Event Triggers

The Event Trigger patch series main goal is to allow for users to tweak DDL commands without having to hook in ProcessUtility(). The design has been made more general so that we can support important use cases.

Main advantages over ProcessUtility hooks

The first version of Event Triggers has very few technical capabilities to offer that we don't already have available in the form of ProcessUtility hooks, so, what's the deal here?

  • You can write event triggers functions in PL/pgSQL, no need to write them in C, build an extension, ship it to the server's file system and finally install it;
  • You can actually list the currently installed Event Triggers by doing \dy in psql;
  • In single user mode the Event Triggers are disabled, staying out of the way while you're busy fixing your production services;
  • It's possible to install more than one Event Trigger then decide as the DBA in which order to run the Event Triggers without having to decipher how the code is implemented and in which order the shared object librairies are going to be _Init()ialized.

Expected Features

The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.

Use Cases

We attempt at being able to solve those use cases:

  • Logical Replication
  • Auditing
  • DDL support for Extensions


So we want to provide those features:

  • Event Triggers that run either before or after a DDL command
  • User Functions should be provided with detailed information:
    • event name
    • command tag
    • operation (CREATE, ALTER or DROP)
    • object kind (TABLE, FUNCTION, VIEW, etc)
    • object OID
    • object name
    • schema name where the object lives
  • support for DROP CASCADE
  • support for dropping multiple objects
  • support for generated commands

Commited patches from the series

Here's the list of what we already have:

= Missing features for 9.3

The main missing features are:

  • No specific information about the objects that the command target
  • No access to the parse tree or the command string, raw or normalized
  • No support for multiple targets per command
    • only DROP can target more than one object at a time
    • and DROP CASCADE is another special case of that
  • No support for generated commands


Some of the features are still under discussion in order to find the best design we can implement.

As a general note it's useful to recall that most of those features have already been coded at least once (some of them received one or more redesign and refactoring following review in the 9.2 and 9.3 cycles). So it's not about adding new features that we didn't see coming before, it's about finishing a patch series to match a subset of the features proposed a couple of years ago.

The goal is to refine which subset we are ready to accept and to evaluate if we can ship with that subset as something that makes sense on its own.

For example, with no information available at all about the specifics of the objects that a command is targeting, it's hard to see which use case you can actually solve with Event Triggers.

generated and internal commands

For context, the following command will actually run several operations:

CREATE TABLE foo(id serial primary key, f1 text);

We will have CREATE SEQUENCE then a new CREATE TABLE statement using the name of the sequence we just created, then ALTER SEQUENCE and finally a CREATE INDEX command. The current implementation of PostgreSQL backend is to form a parse node from scratch and send that to ProcessUtility() again, in a recursive way.

The current patch proposal is to expose those with a CONTEXT of GENERATED that is not matched by default by any Event Trigger, and allow the user to opt-in when he's interested into that level of implementation detail.

It might not be very wise to make that implementation detail visible to users because that would make us unable to clean the mess later, should any developer has round tuits or motivation to go about that some day.

One idea is to only expose users who are willing to code their event trigger in C, and another idea is then to only expose them via a hook. The problem with the hook approach is that you need to build and expose the exact same amount of information.


A patch has been sent to allow to call an Event Trigger for each object that is dropped as part of a command: http://www.postgresql.org/message-id/m2fw1ieq5x.fsf@2ndQuadrant.fr

How to expose Information to Event Triggers Functions

The current proposal is to propose a mix of magic PL variables. The already commited code exposes TG_EVENT and TG_TAG.

We're proposing to add to that the most common pieces of information as variables, adding to them those that are really cheap to build (it's a constant string):

  • Variables
  • Accessors
    • pg_get_event_command_string()

About TG_CONTEXT, see previous point.

Command String Normalisation

The main use case for the command string is Logical Replication. In that use case, it's very important to know off hand in which schemas the objects are created (resp. dropped, altered) and to some extend which names are given to automatically named objects (indexes, constraints, sequences).

Distributed Sequences

In the Logical Replication use case, it's important to be able, from an event trigger, to make it so that a CREATE SEQUENCE will in fact create a distributed sequence, in certain cases determined by the logical replication system (not known in the backend).

Features For Next Releases

Some features we intended to provide already in 9.2 will have to wait until we are ready, which will not be the case in the 9.3 timeframe.


The idea is to be able to install an Event Trigger that takes control over an existing command, to be able to re-define it, maybe in terms of the command itself:

create event trigger my_create_extension instead of 'create extension'
   execute procedure my_create_extension();
create function my_create_extension()
        returns event_trigger
       language plpgsql
as $$
   alter event trigger my_create_extension disable;
   -- do some stuff here
   create extension tg_objectid;
   -- do some more stuff here, presumably

Unfortunately it's already clear that the INSTEAD OF feature implementation is too hard to get right from the first release of Event Triggers, because the call points in the backend code must be really carefully placed.


The idea is to provide an event called table_rewrite that will fire any time a command that will rewrite the whole of the table is to be executed, so that the DBA can install a local policy about that (accept the rewrite only at night when it's not a full moon, say).

create table on insert

Some developers are getting used to schema less databases nowadays, and want to be have the behaviour that when they do

INSERT INTO look_me_i_dont_exist(key, value) VALUES(1, 'foo');

The table look_me_i_dont_exist is automatically created by PostgreSQL with two columns key of type integer (presumably) and value of type text here.

Personal tools