From 473ab40c8bb3fcb1a7645f6a7443a0424d70fbaf Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Wed, 27 Mar 2013 16:02:10 -0300 Subject: Add sql_drop event for event triggers MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This event takes place just before ddl_command_end, and is fired if and only if at least one object has been dropped by the command. (For instance, DROP TABLE IF EXISTS of a table that does not in fact exist will not lead to such a trigger firing). Commands that drop multiple objects (such as DROP SCHEMA or DROP OWNED BY) will cause a single event to fire. Some firings might be surprising, such as ALTER TABLE DROP COLUMN. The trigger is fired after the drop has taken place, because that has been deemed the safest design, to avoid exposing possibly-inconsistent internal state (system catalogs as well as current transaction) to the user function code. This means that careful tracking of object identification is required during the object removal phase. Like other currently existing events, there is support for tag filtering. To support the new event, add a new pg_event_trigger_dropped_objects() set-returning function, which returns a set of rows comprising the objects affected by the command. This is to be used within the user function code, and is mostly modelled after the recently introduced pg_identify_object() function. Catalog version bumped due to the new function. Dimitri Fontaine and Álvaro Herrera Review by Robert Haas, Tom Lane --- doc/src/sgml/event-trigger.sgml | 110 ++++++++++++++++++++++++++++++++++++-- doc/src/sgml/func.sgml | 113 ++++++++++++++++++++++++++++++++++++++++ 2 files changed, 218 insertions(+), 5 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index 71241c82245..36928413d15 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -27,17 +27,19 @@ 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 - and ddl_command_end. Support for additional events may be - added in future releases. + supported events are + ddl_command_start, + ddl_command_end + 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, or DROP command. As an exception, however, this event does not occur for - DDL commands targeting shared objects - databases, roles, and tablespaces - - or for command targeting event triggers themselves. The event trigger + DDL commands targeting shared objects — databases, roles, and tablespaces + — or for command 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 @@ -45,6 +47,17 @@ event occurs just after the execution of this same set of commands. + + 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 your + sql_drop event trigger code (see + ). 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. + + Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated @@ -99,6 +112,7 @@ command tag ddl_command_start ddl_command_end + sql_drop @@ -106,401 +120,487 @@ ALTER AGGREGATE X X + - ALTER COLLATION X X + - ALTER CONVERSION X X + - ALTER DOMAIN 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 OPERATOR X X + - ALTER OPERATOR CLASS X X + - ALTER OPERATOR FAMILY X X + - ALTER SCHEMA X X + - ALTER SEQUENCE X X + - ALTER SERVER X X + - ALTER TABLE X 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 + - ALTER USER MAPPING X X + - ALTER VIEW 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 OPERATOR X X + - CREATE OPERATOR CLASS X X + - CREATE OPERATOR FAMILY X X + - CREATE RULE X X + - CREATE SCHEMA X X + - CREATE SEQUENCE X X + - CREATE SERVER X X + - CREATE TABLE X X + - CREATE TABLE AS 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 MAPPING X X + - CREATE VIEW 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 OPERATOR X X + X DROP OPERATOR CLASS X X + X DROP OPERATOR FAMILY X X + X + + + DROP OWNED + X + X + X DROP RULE X X + X DROP SCHEMA X X + X DROP SEQUENCE X X + X DROP SERVER X X + X DROP TABLE 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 MAPPING X X + X DROP VIEW X X + X SELECT INTO X X + - diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 490d7106435..db4e33f871d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -15980,4 +15980,117 @@ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); . + + + Event Trigger Functions + + + pg_event_trigger_dropped_objects + + + + Currently PostgreSQL provides one built-in event trigger + helper function, pg_event_trigger_dropped_objects. + + + + pg_event_trigger_dropped_objects returns a list of all object + dropped by the command in whose sql_drop event it is called. + If called in any other context, + pg_event_trigger_dropped_objects raises an error. + pg_event_trigger_dropped_objects returns the following columns: + + + + + + Name + Type + Description + + + + + + classid + Oid + OID of catalog the object belonged in + + + objid + Oid + OID the object had within the catalog + + + objsubid + int32 + Object sub-id (e.g. attribute number for columns) + + + 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 an 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 and every + identifier present in the identity is quoted if necessary. + + + + + + + + + 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 LANGUAGE plpgsql 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 +$$; +CREATE EVENT TRIGGER test_event_trigger_for_drops + ON sql_drop + EXECUTE PROCEDURE test_event_trigger_for_drops(); + + + + + For more information about event triggers, + see . + + + -- cgit v1.2.3