From PostgreSQL wiki
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.
The first release of Event Triggers is landing in PostgreSQL 9.3 and we will have a restricted set of features in that relase.
We attempt at being able to solve those use cases:
- Logical Replication
- 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:
- Syntax support and documentation for event triggers.
- that was the first step, catalogs and grammar
- Make new event trigger facility actually do something.
- and now you can actually have your function called
- it will only know about tg_event and tg_tag
- and you can only write it in PLpgSQL
- Adjust many backend functions to return OID rather than void.
- that's a preparing step so that we can expose the object OID in the event trigger
- not yet exposed though
- Add ddl_command_end support for event triggers.
- so that you can call your event trigger when the object has already been CREATEd
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 them.
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.
DROP CASCADE support
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 expose some 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):
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).
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 $$ begin alter event trigger my_create_extension disable; -- do some stuff here create extension tg_objectid; -- do some more stuff here, presumably end; $$;
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.