diff options
Diffstat (limited to 'doc/src/sgml/event-trigger.sgml')
| -rw-r--r-- | doc/src/sgml/event-trigger.sgml | 1303 |
1 files changed, 1303 insertions, 0 deletions
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml new file mode 100644 index 0000000..3b6a536 --- /dev/null +++ b/doc/src/sgml/event-trigger.sgml @@ -0,0 +1,1303 @@ +<!-- doc/src/sgml/event-trigger.sgml --> + + <chapter id="event-triggers"> + <title>Event Triggers</title> + + <indexterm zone="event-triggers"> + <primary>event trigger</primary> + </indexterm> + + <para> + To supplement the trigger mechanism discussed in <xref linkend="triggers"/>, + <productname>PostgreSQL</productname> 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. + </para> + + <para> + 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. + </para> + + <sect1 id="event-trigger-definition"> + <title>Overview of Event Trigger Behavior</title> + + <para> + 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 + <literal>ddl_command_start</literal>, + <literal>ddl_command_end</literal>, + <literal>table_rewrite</literal> + and <literal>sql_drop</literal>. + Support for additional events may be added in future releases. + </para> + + <para> + The <literal>ddl_command_start</literal> event occurs just before the + execution of a <literal>CREATE</literal>, <literal>ALTER</literal>, <literal>DROP</literal>, + <literal>SECURITY LABEL</literal>, + <literal>COMMENT</literal>, <literal>GRANT</literal> or <literal>REVOKE</literal> + 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, roles, and tablespaces + — or for commands targeting event triggers themselves. The event trigger + mechanism does not support these object types. + <literal>ddl_command_start</literal> also occurs just before the execution of a + <literal>SELECT INTO</literal> command, since this is equivalent to + <literal>CREATE TABLE AS</literal>. + </para> + + <para> + The <literal>ddl_command_end</literal> event occurs just after the execution of + this same set of commands. To obtain more details on the <acronym>DDL</acronym> + operations that took place, use the set-returning function + <literal>pg_event_trigger_ddl_commands()</literal> from the + <literal>ddl_command_end</literal> event trigger code (see + <xref linkend="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. + </para> + + <para> + The <literal>sql_drop</literal> event occurs just before the + <literal>ddl_command_end</literal> event trigger for any operation that drops + database objects. To list the objects that have been dropped, use the + set-returning function <literal>pg_event_trigger_dropped_objects()</literal> from the + <literal>sql_drop</literal> event trigger code (see + <xref linkend="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. + </para> + + <para> + The <literal>table_rewrite</literal> event occurs just before a table is + rewritten by some actions of the commands <literal>ALTER TABLE</literal> and + <literal>ALTER TYPE</literal>. While other + control statements are available to rewrite a table, + like <literal>CLUSTER</literal> and <literal>VACUUM</literal>, + the <literal>table_rewrite</literal> event is not triggered by them. + </para> + + <para> + Event triggers (like other functions) cannot be executed in an aborted + transaction. Thus, if a DDL command fails with an error, any associated + <literal>ddl_command_end</literal> triggers will not be executed. Conversely, + if a <literal>ddl_command_start</literal> 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 <literal>ddl_command_end</literal> 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. + </para> + + <para> + For a complete list of commands supported by the event trigger mechanism, + see <xref linkend="event-trigger-matrix"/>. + </para> + + <para> + Event triggers are created using the command <xref linkend="sql-createeventtrigger"/>. + In order to create an event trigger, you must first create a function with + the special return type <literal>event_trigger</literal>. 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. + </para> + + <para> + If more than one event trigger is defined for a particular event, they will + fire in alphabetical order by trigger name. + </para> + + <para> + A trigger definition can also specify a <literal>WHEN</literal> + condition so that, for example, a <literal>ddl_command_start</literal> + 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. + </para> + </sect1> + + <sect1 id="event-trigger-matrix"> + <title>Event Trigger Firing Matrix</title> + + <para> + <xref linkend="event-trigger-by-command-tag"/> lists all commands + for which event triggers are supported. + </para> + + <table id="event-trigger-by-command-tag"> + <title>Event Trigger Support by Command Tag</title> + <tgroup cols="6"> + <colspec colname="col1" colwidth="2*"/> + <colspec colname="col2" colwidth="1*"/> + <colspec colname="col3" colwidth="1*"/> + <colspec colname="col4" colwidth="1*"/> + <colspec colname="col5" colwidth="1*"/> + <colspec colname="col6" colwidth="1*"/> + <thead> + <row> + <entry>Command Tag</entry> + <entry><literal>ddl_&zwsp;command_&zwsp;start</literal></entry> + <entry><literal>ddl_&zwsp;command_&zwsp;end</literal></entry> + <entry><literal>sql_&zwsp;drop</literal></entry> + <entry><literal>table_&zwsp;rewrite</literal></entry> + <entry>Notes</entry> + </row> + </thead> + <tbody> + <row> + <entry align="left"><literal>ALTER AGGREGATE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER COLLATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER CONVERSION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER DOMAIN</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER DEFAULT PRIVILEGES</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER EXTENSION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER FOREIGN DATA WRAPPER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER FOREIGN TABLE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER FUNCTION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER LANGUAGE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER LARGE OBJECT</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER MATERIALIZED VIEW</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER OPERATOR</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER OPERATOR CLASS</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER OPERATOR FAMILY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER POLICY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER PROCEDURE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER PUBLICATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER ROUTINE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER SCHEMA</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER SEQUENCE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER SERVER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER STATISTICS</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER SUBSCRIPTION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER TABLE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER TEXT SEARCH CONFIGURATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER TEXT SEARCH DICTIONARY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER TEXT SEARCH PARSER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER TEXT SEARCH TEMPLATE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER TRIGGER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER TYPE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER USER MAPPING</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>ALTER VIEW</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>COMMENT</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left">Only for local objects</entry> + </row> + <row> + <entry align="left"><literal>CREATE ACCESS METHOD</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE AGGREGATE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE CAST</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE COLLATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE CONVERSION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE DOMAIN</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE EXTENSION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE FOREIGN DATA WRAPPER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE FOREIGN TABLE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE FUNCTION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE INDEX</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE LANGUAGE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE MATERIALIZED VIEW</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE OPERATOR</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE OPERATOR CLASS</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE OPERATOR FAMILY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE POLICY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE PROCEDURE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE PUBLICATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE RULE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE SCHEMA</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE SEQUENCE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE SERVER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE STATISTICS</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE SUBSCRIPTION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TABLE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TABLE AS</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TEXT SEARCH CONFIGURATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TEXT SEARCH DICTIONARY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TEXT SEARCH PARSER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TEXT SEARCH TEMPLATE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TRIGGER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE TYPE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE USER MAPPING</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>CREATE VIEW</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP ACCESS METHOD</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP AGGREGATE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP CAST</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP COLLATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP CONVERSION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP DOMAIN</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP EXTENSION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP FOREIGN DATA WRAPPER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP FOREIGN TABLE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP FUNCTION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP INDEX</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP LANGUAGE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP MATERIALIZED VIEW</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP OPERATOR</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP OPERATOR CLASS</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP OPERATOR FAMILY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP OWNED</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP POLICY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP PROCEDURE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP PUBLICATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP ROUTINE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP RULE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP SCHEMA</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP SEQUENCE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP SERVER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP STATISTICS</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP SUBSCRIPTION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP TABLE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP TEXT SEARCH CONFIGURATION</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP TEXT SEARCH DICTIONARY</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP TEXT SEARCH PARSER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP TEXT SEARCH TEMPLATE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP TRIGGER</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP TYPE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP USER MAPPING</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>DROP VIEW</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>GRANT</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left">Only for local objects</entry> + </row> + <row> + <entry align="left"><literal>IMPORT FOREIGN SCHEMA</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>REFRESH MATERIALIZED VIEW</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + <row> + <entry align="left"><literal>REVOKE</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left">Only for local objects</entry> + </row> + <row> + <entry align="left"><literal>SECURITY LABEL</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left">Only for local objects</entry> + </row> + <row> + <entry align="left"><literal>SELECT INTO</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>X</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="center"><literal>-</literal></entry> + <entry align="left"></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="event-trigger-interface"> + <title>Writing Event Trigger Functions in C</title> + + <indexterm zone="event-trigger-interface"> + <primary>event trigger</primary> + <secondary>in C</secondary> + </indexterm> + + <para> + 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. + </para> + + <para> + Event trigger functions must use the <quote>version 1</quote> function + manager interface. + </para> + + <para> + When a function is called by the event trigger manager, it is not passed + any normal arguments, but it is passed a <quote>context</quote> pointer + pointing to a <structname>EventTriggerData</structname> structure. C functions can + check whether they were called from the event trigger manager or not by + executing the macro: +<programlisting> +CALLED_AS_EVENT_TRIGGER(fcinfo) +</programlisting> + which expands to: +<programlisting> +((fcinfo)->context != NULL && IsA((fcinfo)->context, EventTriggerData)) +</programlisting> + If this returns true, then it is safe to cast + <literal>fcinfo->context</literal> to type <literal>EventTriggerData + *</literal> and make use of the pointed-to + <structname>EventTriggerData</structname> structure. The function must + <emphasis>not</emphasis> alter the <structname>EventTriggerData</structname> + structure or any of the data it points to. + </para> + + <para> + <structname>struct EventTriggerData</structname> is defined in + <filename>commands/event_trigger.h</filename>: + +<programlisting> +typedef struct EventTriggerData +{ + NodeTag type; + const char *event; /* event name */ + Node *parsetree; /* parse tree */ + CommandTag tag; /* command tag */ +} EventTriggerData; +</programlisting> + + where the members are defined as follows: + + <variablelist> + <varlistentry> + <term><structfield>type</structfield></term> + <listitem> + <para> + Always <literal>T_EventTriggerData</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><structfield>event</structfield></term> + <listitem> + <para> + Describes the event for which the function is called, one of + <literal>"ddl_command_start"</literal>, <literal>"ddl_command_end"</literal>, + <literal>"sql_drop"</literal>, <literal>"table_rewrite"</literal>. + See <xref linkend="event-trigger-definition"/> for the meaning of these + events. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><structfield>parsetree</structfield></term> + <listitem> + <para> + 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><structfield>tag</structfield></term> + <listitem> + <para> + The command tag associated with the event for which the event trigger + is run, for example <literal>"CREATE FUNCTION"</literal>. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + An event trigger function must return a <symbol>NULL</symbol> pointer + (<emphasis>not</emphasis> an SQL null value, that is, do not + set <parameter>isNull</parameter> true). + </para> + </sect1> + + <sect1 id="event-trigger-example"> + <title>A Complete Event Trigger Example</title> + + <para> + 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.) + </para> + + <para> + The function <function>noddl</function> raises an exception each time it is called. + The event trigger definition associated the function with + the <literal>ddl_command_start</literal> event. The effect is that all DDL + commands (with the exceptions mentioned + in <xref linkend="event-trigger-definition"/>) are prevented from running. + </para> + + <para> + This is the source code of the trigger function: +<programlisting><![CDATA[ +#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", + GetCommandTagName(trigdata->tag)))); + + PG_RETURN_NULL(); +} +]]></programlisting> + </para> + + <para> + After you have compiled the source code (see <xref linkend="dfunc"/>), + declare the function and the triggers: +<programlisting> +CREATE FUNCTION noddl() RETURNS event_trigger + AS 'noddl' LANGUAGE C; + +CREATE EVENT TRIGGER noddl ON ddl_command_start + EXECUTE FUNCTION noddl(); +</programlisting> + </para> + + <para> + Now you can test the operation of the trigger: +<screen> +=# \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 +</screen> + </para> + + <para> + 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: +<programlisting> +BEGIN; +ALTER EVENT TRIGGER noddl DISABLE; +CREATE TABLE foo (id serial); +ALTER EVENT TRIGGER noddl ENABLE; +COMMIT; +</programlisting> + (Recall that DDL commands on event triggers themselves are not affected by + event triggers.) + </para> + </sect1> + + <sect1 id="event-trigger-table-rewrite-example"> + <title>A Table Rewrite Event Trigger Example</title> + + <para> + Thanks to the <literal>table_rewrite</literal> event, it is possible to implement + a table rewriting policy only allowing the rewrite in maintenance windows. + </para> + + <para> + Here's an example implementing such a policy. +<programlisting> +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(); +</programlisting> + </para> + </sect1> +</chapter> |