PostgreSQL 9.3 introduced a special type of triggers to complement the trigger mechanism we discussed in the preceding chapter. These triggers, are database-specific and are not attached to a specific table. Unlike regular triggers, event triggers capture DDL events. Event triggers can be the BEFORE and AFTER triggers, and the trigger function can be written in any language except SQL. Other popular database vendors such as Oracle and SQL Server also provide a similar functionality.
One can think of several use cases for DDL triggers. The most popular one among the DBAs normally is to do an audit trail. You, as a DBA, might want to audit the users and the DDL commands. Schema changes, in a production environment, should be done very carefully; hence, the need for an audit trail is apparent. Event triggers are disabled in the single user mode and can only be created by a superuser.
In this chapter, we will cover the following topics:
- Use cases for event triggers
- A full audit trail example using PL/pgsql
- Preventing schema changes using event triggers
In addition to an audit trail, the following can be valid use cases for an event trigger:
- Replicating DDL changes to a remote location
- Cascading a DDL
- Preventing changes to tables, except during a predefined window
- Providing limited DDL capability to developers / support staff using security definer functions
- Disabling certain DDL commands based on a criteria
- Performance analysis to see how long a command takes between
ddl_command_startandddl_command_end
Note that the event trigger support for PL/pgsql is not yet complete in 9.3. There are several features that are being worked upon and will be available in future PostgreSQL versions, hopefully. These are a few of the most notable features that are missing so far:
- There's no information about the object that a DDL targets
- There's no access to the command string
- There's no support for the generated commands
A command such as CREATE TABLE foo (id serial PRIMARY KEY) will also result in the CREATE SEQUENCE and CREATE INDEX commands to be executed internally. However, the current DDL triggers will not be able to log these generated commands.