Event Triggers
Redrock Postgres 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, but not in plain SQL.
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 — databases — 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 Event Trigger Functions). 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 Event Trigger Functions). 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 triggers are created using the command CREATE EVENT TRIGGER. 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.
Table 1 lists all commands for which event triggers are supported.
Table 1. Event Trigger Support by Command Tag
Command Tag | ddl_command_start |
ddl_command_end |
sql_drop |
table_rewrite |
---|---|---|---|---|
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 ROLE |
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 TABLESPACE |
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 |
X |
X |
- |
- |
ALTER USER MAPPING |
X |
X |
- |
- |
ALTER VIEW |
X |
X |
- |
- |
COMMENT |
X |
X |
- |
- |
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 ROLE |
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 TABLESPACE |
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 |
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 ROLE |
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 TABLESPACE |
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 |
X |
X |
X |
- |
DROP USER MAPPING |
X |
X |
X |
- |
DROP VIEW |
X |
X |
X |
- |
GRANT |
X |
X |
- |
- |
IMPORT FOREIGN SCHEMA |
X |
X |
- |
- |
REFRESH MATERIALIZED VIEW |
X |
X |
- |
- |
REVOKE |
X |
X |
- |
- |
SECURITY LABEL |
X |
X |
- |
- |
SELECT INTO |
X |
X |
- |
- |
Event triggers can not capture DDL commands targeting database objects, including:CREATE DATABASE
,ALTER DATABASE
,DROP DATABASE
,COMMENT ON DATABASE
,GRANT ON DATABASE
,REVOKE ON DATABASE
.
Redrock Postgres provides these helper functions to retrieve information from event triggers.
pg_event_trigger_ddl_commands () → setof record
pg_event_trigger_ddl_commands
returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end
event trigger. If called in any other context, an error is raised. pg_event_trigger_ddl_commands
returns one row for each base command executed; some commands that are a single SQL sentence may return more than one row. This function returns the following columns:
Name | Type | Description |
---|---|---|
classid |
oid |
OID of catalog the object belongs in |
objid |
oid |
OID of the object itself |
objsubid |
integer |
Sub-object ID (e.g., attribute number for a column) |
command_tag |
text |
Command tag |
object_type |
text |
Type of the object |
schema_name |
text |
Name of the schema the object belongs in, if any; otherwise NULL . No quoting is applied. |
object_identity |
text |
Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary. |
in_extension |
bool |
True if the command is part of an extension script |
command |
pg_ddl_command |
A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command. |
pg_event_trigger_dropped_objects () → setof record
pg_event_trigger_dropped_objects
returns a list of all objects dropped by the command in whose sql_drop
event it is called. If called in any other context, an error is raised. This function returns the following columns:
Name | Type | Description |
---|---|---|
classid |
oid |
OID of catalog the object belonged in |
objid |
oid |
OID of the object itself |
objsubid |
integer |
Sub-object ID (e.g., attribute number for a column) |
original |
bool |
True if this was one of the root object(s) of the deletion |
normal |
bool |
True if there was a normal dependency relationship in the dependency graph leading to this object |
is_temporary |
bool |
True if this was a temporary object |
object_type |
text |
Type of the object |
schema_name |
text |
Name of the schema the object belonged in, if any; otherwise NULL . No quoting is applied. |
object_name |
text |
Name of the object, if the combination of schema and name can be used as a unique identifier for the object; otherwise NULL . No quoting is applied, and name is never schema-qualified. |
object_identity |
text |
Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary. |
address_names |
text[] |
An array that, together with object_type and address_args , can be used by the pg_get_object_address() function to recreate the object address in a remote server containing an identically named object of the same kind |
address_args |
text[] |
Complement for address_names |
The pg_event_trigger_dropped_objects
function can be used in an event trigger like this:
CREATE FUNCTION test_event_trigger_for_drops()
RETURNS event_trigger AS $$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
RAISE NOTICE '% dropped object: % %.% %',
tg_tag,
obj.object_type,
obj.schema_name,
obj.object_name,
obj.object_identity;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER test_event_trigger_for_drops
ON sql_drop
EXECUTE PROCEDURE test_event_trigger_for_drops();
The functions shown in Table 2 provide information about a table for which a table_rewrite
event has just been called. If called in any other context, an error is raised.
Table 2. Table Rewrite Information
Name | Description |
---|---|
pg_event_trigger_table_rewrite_oid () → oid | The OID of the table about to be rewritten. |
pg_event_trigger_table_rewrite_reason () → integer | The reason code(s) explaining the reason for rewriting. The exact meaning of the codes is release dependent. |
The pg_event_trigger_table_rewrite_oid
function can be used in an event trigger like this:
CREATE FUNCTION test_event_trigger_table_rewrite_oid()
RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'rewriting table % for reason %',
pg_event_trigger_table_rewrite_oid()::regclass,
pg_event_trigger_table_rewrite_reason();
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER test_table_rewrite_oid
ON table_rewrite
EXECUTE PROCEDURE test_event_trigger_table_rewrite_oid();
pg_ddl_command_deparse ( pg_ddl_command ) → text
pg_ddl_command_deparse
deparses a command from internal format of type pg_ddl_command
, and returns a SQL statement in textual representation of type text
. The function can be used in an event trigger like this:
CREATE TABLE ddl_history(
ord int,
op_time timestamp,
username text,
command_tag text,
object_type text,
schema_name text,
object_identity text,
command text
);
CREATE OR REPLACE FUNCTION ddl_trigger_func()
RETURNS event_trigger AS $$
BEGIN
INSERT INTO ddl_history
SELECT ordinality, now(), current_user, command_tag,
object_type, schema_name, object_identity,
pg_ddl_command_deparse(command)
FROM pg_event_trigger_ddl_commands() WITH ORDINALITY;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER deparse_event_trigger
ON ddl_command_end
EXECUTE PROCEDURE ddl_trigger_func();