Here's a new version of this patch, rebased on top of the new
pg_identify_object() stuff. Note that the regression test doesn't work
yet, because I didn't adjust to the new identity output definition (the
docs need work, too). But that's a simple change to do. I'm leaving
that for later.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/doc/src/sgml/event-trigger.sgml
--- b/doc/src/sgml/event-trigger.sgml
***************
*** 36,43 ****
The <literal>ddl_command_start</> event occurs just before the
execution of a <literal>CREATE</>, <literal>ALTER</>, or <literal>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
mechanism does not support these object types.
<literal>ddl_command_start</> also occurs just before the execution of a
<literal>SELECT INTO</literal> command, since this is equivalent to
--- 36,43 ----
The <literal>ddl_command_start</> event occurs just before the
execution of a <literal>CREATE</>, <literal>ALTER</>, or <literal>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
mechanism does not support these object types.
<literal>ddl_command_start</> also occurs just before the execution of a
<literal>SELECT INTO</literal> command, since this is equivalent to
***************
*** 46,51 ****
--- 46,61 ----
</para>
<para>
+ To list all objects that have been deleted as part of executing a
+ command, use the set returning
+ function <literal>pg_event_trigger_dropped_objects()</> from
+ your <literal>ddl_command_end</> 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>
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</> triggers will not be executed. Conversely,
***************
*** 433,438 ****
--- 443,453 ----
<entry align="center"><literal>X</literal></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>
+ </row>
+ <row>
<entry align="left"><literal>DROP RULE</literal></entry>
<entry align="center"><literal>X</literal></entry>
<entry align="center"><literal>X</literal></entry>
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 15980,15983 **** FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
--- 15980,16033 ----
<xref linkend="SQL-CREATETRIGGER">.
</para>
</sect1>
+
+ <sect1 id="functions-event-triggers">
+ <title>Event Trigger Functions</title>
+
+ <indexterm>
+ <primary>pg_event_trigger_dropped_objects</primary>
+ </indexterm>
+
+ <para>
+ Currently <productname>PostgreSQL</> provides one built-in event trigger
+ helper function, <function>pg_event_trigger_dropped_objects</>, which
+ lists all object dropped by the command in whose <literal>ddl_command_end</>
+ event it is called. If the function is run in a context other than a
+ <literal>ddl_command_end</> event trigger function, or if it's run in the
+ <literal>ddl_command_end</> event of a command that does not drop objects,
+ it will return the empty set.
+ </para>
+
+ <para>
+ The <function>pg_event_trigger_dropped_objects</> function can be used
+ in an event trigger like this:
+ <programlisting>
+ 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.subobject_name;
+ END LOOP;
+ END
+ $$;
+ CREATE EVENT TRIGGER test_event_trigger_for_drops
+ ON ddl_command_end
+ EXECUTE PROCEDURE test_event_trigger_for_drops();
+ </programlisting>
+ </para>
+
+ <para>
+ For more information about event triggers,
+ see <xref linkend="event-triggers">.
+ </para>
+ </sect1>
+
</chapter>
*** a/src/backend/catalog/dependency.c
--- b/src/backend/catalog/dependency.c
***************
*** 257,262 **** performDeletion(const ObjectAddress *object,
--- 257,268 ----
{
ObjectAddress *thisobj = targetObjects->refs + i;
+ if ((!(flags & PERFORM_DELETION_INTERNAL)) &&
+ EventTriggerSupportsObjectType(getObjectClass(thisobj)))
+ {
+ evtrig_sqldrop_add_object(thisobj);
+ }
+
deleteOneObject(thisobj, &depRel, flags);
}
***************
*** 339,344 **** performMultipleDeletions(const ObjectAddresses *objects,
--- 345,356 ----
{
ObjectAddress *thisobj = targetObjects->refs + i;
+ if ((!(flags & PERFORM_DELETION_INTERNAL)) &&
+ EventTriggerSupportsObjectType(getObjectClass(thisobj)))
+ {
+ evtrig_sqldrop_add_object(thisobj);
+ }
+
deleteOneObject(thisobj, &depRel, flags);
}
***************
*** 356,361 **** performMultipleDeletions(const ObjectAddresses *objects,
--- 368,377 ----
* This is currently used only to clean out the contents of a schema
* (namespace): the passed object is a namespace. We normally want this
* to be done silently, so there's an option to suppress NOTICE messages.
+ *
+ * Note we don't fire object drop event triggers here; it would be wrong to do
+ * so for the current only use of this function, but if more callers are added
+ * this might need to be reconsidered.
*/
void
deleteWhatDependsOn(const ObjectAddress *object,
*** a/src/backend/commands/event_trigger.c
--- b/src/backend/commands/event_trigger.c
***************
*** 19,30 ****
--- 19,32 ----
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_event_trigger.h"
+ #include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
#include "commands/event_trigger.h"
#include "commands/trigger.h"
+ #include "funcapi.h"
#include "parser/parse_func.h"
#include "pgstat.h"
#include "miscadmin.h"
***************
*** 39,44 ****
--- 41,50 ----
#include "utils/syscache.h"
#include "tcop/utility.h"
+ /* -- Globally visible state -- */
+
+ EventTriggerQueryState *currentEventTriggerState = NULL;
+
typedef struct
{
const char *obtypename;
***************
*** 89,94 **** static event_trigger_support_data event_trigger_support[] = {
--- 95,110 ----
{ NULL, false }
};
+ /* Support for dropped objects */
+ typedef struct SQLDropObject
+ {
+ ObjectAddress address;
+ char *objidentity;
+ char *schemaname;
+ char *objecttype;
+ slist_node next;
+ } SQLDropObject;
+
static void AlterEventTriggerOwner_internal(Relation rel,
HeapTuple tup,
Oid newOwnerId);
***************
*** 151,158 **** CreateEventTrigger(CreateEventTrigStmt *stmt)
}
/* Validate tag list, if any. */
! if (strcmp(stmt->eventname, "ddl_command_start") == 0 && tags != NULL)
validate_ddl_tags("tag", tags);
/*
* Give user a nice error message if an event trigger of the same name
--- 167,178 ----
}
/* Validate tag list, if any. */
! if ((strcmp(stmt->eventname, "ddl_command_start") == 0 ||
! strcmp(stmt->eventname, "ddl_command_end") == 0)
! && tags != NULL)
! {
validate_ddl_tags("tag", tags);
+ }
/*
* Give user a nice error message if an event trigger of the same name
***************
*** 220,226 **** check_ddl_tag(const char *tag)
pg_strcasecmp(tag, "SELECT INTO") == 0 ||
pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 ||
pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 ||
! pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0)
return EVENT_TRIGGER_COMMAND_TAG_OK;
/*
--- 240,247 ----
pg_strcasecmp(tag, "SELECT INTO") == 0 ||
pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 ||
pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 ||
! pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0 ||
! pg_strcasecmp(tag, "DROP OWNED") == 0)
return EVENT_TRIGGER_COMMAND_TAG_OK;
/*
***************
*** 832,834 **** EventTriggerSupportsObjectType(ObjectType obtype)
--- 853,1086 ----
}
return true;
}
+
+ /*
+ * Prepare event trigger for running a new query.
+ */
+ EventTriggerQueryState *
+ EventTriggerBeginCompleteQuery(void)
+ {
+ EventTriggerQueryState *prevstate;
+ EventTriggerQueryState *state;
+ MemoryContext cxt;
+
+ prevstate = currentEventTriggerState;
+
+ cxt = AllocSetContextCreate(TopMemoryContext,
+ "event trigger state",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+ state = MemoryContextAlloc(cxt, sizeof(EventTriggerQueryState));
+ state->cxt = cxt;
+ slist_init(&(state->SQLDropList));
+
+ currentEventTriggerState = state;
+
+ return prevstate;
+ }
+
+ /*
+ * Query completed (or errored out) -- clean up local state
+ *
+ * XXX we currently don't use "abort" for anything ...
+ */
+ void
+ EventTriggerEndCompleteQuery(EventTriggerQueryState *prevstate, bool abort)
+ {
+ /* this avoids the need for retail pfree of SQLDropList items: */
+ MemoryContextDelete(currentEventTriggerState->cxt);
+
+ currentEventTriggerState = prevstate;
+ }
+
+ /*
+ * Support for dropped objects information on event trigger functions.
+ *
+ * We keep the list of objects dropped by the current command in current
+ * state's SQLDropList (comprising SQLDropObject items). Each time a new
+ * command is to start, a clean EventTriggerQueryState is created; commands
+ * that drop objects do the dependency.c dance to drop objects, which
+ * populates the current state's SQLDropList; when the event triggers are
+ * invoked they can consume the list via pg_event_trigger_dropped_objects().
+ * When the command finishes, the EventTriggerQueryState is cleared, and
+ * the one from the previous command is restored (when no command is in
+ * execution, the current state is NULL).
+ *
+ * All this lets us support the case that an event trigger function drops
+ * objects "reentrantly".
+ */
+
+ /*
+ * Register one object as being dropped by the current command.
+ */
+ void
+ evtrig_sqldrop_add_object(ObjectAddress *object)
+ {
+ SQLDropObject *obj;
+ MemoryContext oldcxt;
+
+ if (!currentEventTriggerState)
+ return;
+
+ Assert(EventTriggerSupportsObjectType(getObjectClass(object)));
+
+ /* don't report temp schemas */
+ if (object->classId == NamespaceRelationId &&
+ isAnyTempNamespace(object->objectId))
+ return;
+
+ oldcxt = MemoryContextSwitchTo(currentEventTriggerState->cxt);
+
+ obj = palloc0(sizeof(SQLDropObject));
+ obj->address = *object;
+
+ /*
+ * Obtain schema names from the object's catalog tuple, if one exists;
+ * this lets us skip objects in temp schemas. We trust that ObjectProperty
+ * contains all object classes that can be schema-qualified.
+ */
+ if (is_objectclass_supported(object->classId))
+ {
+ Relation catalog;
+ HeapTuple tuple;
+
+ catalog = heap_open(obj->address.classId, AccessShareLock);
+ tuple = get_catalog_object_by_oid(catalog, obj->address.objectId);
+
+ if (tuple)
+ {
+ AttrNumber attnum;
+ Datum datum;
+ bool isnull;
+
+ attnum = get_object_attnum_namespace(obj->address.classId);
+ if (attnum != InvalidAttrNumber)
+ {
+ datum = heap_getattr(tuple, attnum,
+ RelationGetDescr(catalog), &isnull);
+ if (!isnull)
+ {
+ Oid namespaceId;
+
+ namespaceId = DatumGetObjectId(datum);
+ /* Don't report objects in temp namespaces */
+ if (isAnyTempNamespace(namespaceId))
+ {
+ pfree(obj);
+ heap_close(catalog, AccessShareLock);
+ MemoryContextSwitchTo(oldcxt);
+ return;
+ }
+
+ obj->schemaname = get_namespace_name(namespaceId);
+ }
+ }
+ }
+
+ heap_close(catalog, AccessShareLock);
+ }
+
+ /* object name */
+ obj->objidentity = getObjectIdentity(&obj->address);
+
+ /* and object type, too */
+ obj->objecttype = getObjectTypeDescription(&obj->address);
+
+ slist_push_head(&(currentEventTriggerState->SQLDropList), &obj->next);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /*
+ * pg_event_trigger_dropped_objects
+ *
+ * Make the list of dropped objects available to the user function run by the
+ * Event Trigger.
+ */
+ Datum
+ pg_event_trigger_dropped_objects(PG_FUNCTION_ARGS)
+ {
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+ slist_iter iter;
+
+ /* XXX can this actually happen? */
+ if (!currentEventTriggerState)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s can only be called when there's a command in execution",
+ "pg_event_trigger_dropped_objects()")));
+
+ /* check to see if caller supports us returning a tuplestore */
+ if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+ if (!(rsinfo->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not allowed in this context")));
+
+ /* Build a tuple descriptor for our result type */
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ elog(ERROR, "return type must be a row type");
+
+ /* Build tuplestore to hold the result rows */
+ per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo->returnMode = SFRM_Materialize;
+ rsinfo->setResult = tupstore;
+ rsinfo->setDesc = tupdesc;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ slist_foreach(iter, &(currentEventTriggerState->SQLDropList))
+ {
+ SQLDropObject *obj;
+ int i = 0;
+ Datum values[6];
+ bool nulls[6];
+
+ obj = slist_container(SQLDropObject, next, iter.cur);
+
+ MemSet(values, 0, sizeof(values));
+ MemSet(nulls, 0, sizeof(nulls));
+
+ /* classid */
+ values[i++] = ObjectIdGetDatum(obj->address.classId);
+
+ /* objid */
+ values[i++] = ObjectIdGetDatum(obj->address.objectId);
+
+ /* objsubid */
+ values[i++] = Int32GetDatum(obj->address.objectSubId);
+
+ /* object_type */
+ values[i++] = CStringGetTextDatum(obj->objecttype);
+
+ /* schema_name */
+ if (obj->schemaname)
+ values[i++] = CStringGetTextDatum(obj->schemaname);
+ else
+ nulls[i++] = true;
+
+ /* object_identity */
+ if (obj->objidentity)
+ values[i++] = CStringGetTextDatum(obj->objidentity);
+ else
+ nulls[i++] = true;
+
+ tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+ }
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+
+ return (Datum) 0;
+ }
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
***************
*** 370,375 **** ProcessUtility(Node *parsetree,
--- 370,413 ----
} \
} while (0)
+ /*
+ * UTILITY_BEGIN_QUERY and UTILITY_END_QUERY are a pair of macros to enclose
+ * execution of a single DDL command, to ensure the event trigger environment
+ * is appropriately set up before starting, and tore down after completion or
+ * error.
+ */
+ #define UTILITY_BEGIN_QUERY(isComplete) \
+ do { \
+ bool _isComplete = isComplete; \
+ EventTriggerQueryState *_prevstate = NULL; \
+ \
+ if (_isComplete) \
+ { \
+ _prevstate = EventTriggerBeginCompleteQuery(); \
+ } \
+ \
+ PG_TRY(); \
+ { \
+ /* avoid empty statement when followed by a semicolon */ \
+ (void) 0
+
+ #define UTILITY_END_QUERY() \
+ } \
+ PG_CATCH(); \
+ { \
+ if (_isComplete) \
+ { \
+ EventTriggerEndCompleteQuery(_prevstate, true); \
+ } \
+ PG_RE_THROW(); \
+ } \
+ PG_END_TRY(); \
+ if (_isComplete) \
+ { \
+ EventTriggerEndCompleteQuery(_prevstate, false); \
+ } \
+ } while (0)
+
void
standard_ProcessUtility(Node *parsetree,
const char *queryString,
***************
*** 386,391 **** standard_ProcessUtility(Node *parsetree,
--- 424,431 ----
if (completionTag)
completionTag[0] = '\0';
+ UTILITY_BEGIN_QUERY(isCompleteQuery);
+
switch (nodeTag(parsetree))
{
/*
***************
*** 856,861 **** standard_ProcessUtility(Node *parsetree,
--- 896,904 ----
ereport(NOTICE,
(errmsg("relation \"%s\" does not exist, skipping",
atstmt->relation->relname)));
+
+ if (isCompleteQuery)
+ EventTriggerDDLCommandEnd(parsetree);
}
break;
***************
*** 1248,1255 **** standard_ProcessUtility(Node *parsetree,
break;
case T_DropOwnedStmt:
! /* no event triggers for global objects */
! DropOwnedObjects((DropOwnedStmt *) parsetree);
break;
case T_ReassignOwnedStmt:
--- 1291,1299 ----
break;
case T_DropOwnedStmt:
! InvokeDDLCommandEventTriggers(
! parsetree,
! DropOwnedObjects((DropOwnedStmt *) parsetree));
break;
case T_ReassignOwnedStmt:
***************
*** 1372,1377 **** standard_ProcessUtility(Node *parsetree,
--- 1416,1423 ----
(int) nodeTag(parsetree));
break;
}
+
+ UTILITY_END_QUERY();
}
/*
*** a/src/backend/utils/adt/regproc.c
--- b/src/backend/utils/adt/regproc.c
***************
*** 345,351 **** format_procedure_internal(Oid procedure_oid, bool force_qualify)
/*
* Would this proc be found (given the right args) by regprocedurein?
! * If not, we need to qualify it.
*/
if (!force_qualify && FunctionIsVisible(procedure_oid))
nspname = NULL;
--- 345,351 ----
/*
* Would this proc be found (given the right args) by regprocedurein?
! * If not, we need to qualify it -- unless caller wants it bare.
*/
if (!force_qualify && FunctionIsVisible(procedure_oid))
nspname = NULL;
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4693,4698 **** DATA(insert OID = 3473 ( spg_range_quad_leaf_consistent PGNSP PGUID 12 1 0 0 0
--- 4693,4701 ----
DESCR("SP-GiST support for quad tree over range");
+ /* event triggers */
+ DATA(insert OID = 3566 ( pg_event_trigger_dropped_objects PGNSP PGUID 12 10 100 0 0 f f f f t t s 0 0 2249 "" "{26,26,26,25,25,25}" "{o,o,o,o,o,o}" "{classid, objid, objsubid, object_type, schema_name, object_identity}" _null_ pg_event_trigger_dropped_objects _null_ _null_ _null_ ));
+ DESCR("list objects dropped by the current command");
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
*** a/src/include/commands/event_trigger.h
--- b/src/include/commands/event_trigger.h
***************
*** 13,21 ****
--- 13,29 ----
#ifndef EVENT_TRIGGER_H
#define EVENT_TRIGGER_H
+ #include "catalog/objectaddress.h"
#include "catalog/pg_event_trigger.h"
+ #include "lib/ilist.h"
#include "nodes/parsenodes.h"
+ typedef struct EventTriggerQueryState
+ {
+ slist_head SQLDropList;
+ MemoryContext cxt;
+ } EventTriggerQueryState;
+
typedef struct EventTriggerData
{
NodeTag type;
***************
*** 43,46 **** extern bool EventTriggerSupportsObjectType(ObjectType obtype);
--- 51,59 ----
extern void EventTriggerDDLCommandStart(Node *parsetree);
extern void EventTriggerDDLCommandEnd(Node *parsetree);
+ extern EventTriggerQueryState *EventTriggerBeginCompleteQuery(void);
+ extern void EventTriggerEndCompleteQuery(EventTriggerQueryState *prevstate,
+ bool abort);
+ extern void evtrig_sqldrop_add_object(ObjectAddress *object);
+
#endif /* EVENT_TRIGGER_H */
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 1151,1156 **** extern Datum pg_identify_object(PG_FUNCTION_ARGS);
--- 1151,1159 ----
/* commands/constraint.c */
extern Datum unique_key_recheck(PG_FUNCTION_ARGS);
+ /* commands/event_trigger.c */
+ extern Datum pg_event_trigger_dropped_objects(PG_FUNCTION_ARGS);
+
/* commands/extension.c */
extern Datum pg_available_extensions(PG_FUNCTION_ARGS);
extern Datum pg_available_extension_versions(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/event_trigger.out
--- b/src/test/regress/expected/event_trigger.out
***************
*** 93,103 **** ERROR: event trigger "regress_event_trigger" does not exist
drop role regression_bob;
ERROR: role "regression_bob" cannot be dropped because some objects depend on it
DETAIL: owner of event trigger regress_event_trigger3
-- these are all OK; the second one should emit a NOTICE
drop event trigger if exists regress_event_trigger2;
drop event trigger if exists regress_event_trigger2;
NOTICE: event trigger "regress_event_trigger2" does not exist, skipping
drop event trigger regress_event_trigger3;
drop event trigger regress_event_trigger_end;
! drop function test_event_trigger();
drop role regression_bob;
--- 93,224 ----
drop role regression_bob;
ERROR: role "regression_bob" cannot be dropped because some objects depend on it
DETAIL: owner of event trigger regress_event_trigger3
+ -- cleanup before next test
-- these are all OK; the second one should emit a NOTICE
drop event trigger if exists regress_event_trigger2;
drop event trigger if exists regress_event_trigger2;
NOTICE: event trigger "regress_event_trigger2" does not exist, skipping
drop event trigger regress_event_trigger3;
drop event trigger regress_event_trigger_end;
! -- test support for dropped objects
! CREATE SCHEMA schema_one authorization regression_bob;
! CREATE SCHEMA schema_two authorization regression_bob;
! CREATE SCHEMA audit_tbls authorization regression_bob;
! SET SESSION AUTHORIZATION regression_bob;
! CREATE TABLE schema_one.table_one(a int);
! CREATE TABLE schema_one.table_two(a int);
! CREATE TABLE schema_one.table_three(a int);
! CREATE TABLE audit_tbls.table_two(the_value schema_one.table_two);
! CREATE TABLE schema_two.table_two(a int);
! CREATE TABLE schema_two.table_three(a int, b text);
! CREATE TABLE audit_tbls.table_three(the_value schema_two.table_three);
! CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql
! CALLED ON NULL INPUT
! AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$;
! CREATE AGGREGATE schema_two.newton
! (BASETYPE = int, SFUNC = schema_two.add, STYPE = int);
! RESET SESSION AUTHORIZATION;
! CREATE TABLE dropped_objects (type text,
! schema text,
! object text,
! subobject text,
! curr_user text,
! sess_user text);
! CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger
! LANGUAGE plpgsql AS $$
! DECLARE
! obj record;
! BEGIN
! FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
! LOOP
! IF obj.object_type = 'table' THEN
! EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%s',
! obj.object_identity);
! END IF;
!
! INSERT INTO dropped_objects
! (type, schema, object, curr_user, sess_user) VALUES
! (obj.object_type, obj.schema_name, obj.object_identity,
! current_user, session_user);
! END LOOP;
! END
! $$;
! CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON ddl_command_end
! WHEN TAG IN ('drop table', 'drop function', 'drop view',
! 'drop owned', 'drop schema', 'alter table')
! EXECUTE PROCEDURE test_evtrig_dropped_objects();
! ALTER TABLE schema_one.table_one DROP COLUMN a;
! DROP SCHEMA schema_one, schema_two CASCADE;
! NOTICE: drop cascades to 9 other objects
! DETAIL: drop cascades to table schema_two.table_two
! drop cascades to table schema_two.table_three
! drop cascades to table audit_tbls.table_three column the_value
! drop cascades to function schema_two.add(integer,integer)
! drop cascades to function schema_two.newton(integer)
! drop cascades to table schema_one.table_one
! drop cascades to table schema_one.table_two
! drop cascades to table audit_tbls.table_two column the_value
! drop cascades to table schema_one.table_three
! NOTICE: table "table_two" does not exist, skipping
! CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.table_two"
! PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
! SQL statement "DROP TABLE IF EXISTS audit_tbls.table_two"
! PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
! NOTICE: table "table_three" does not exist, skipping
! CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.table_three"
! PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
! SQL statement "DROP TABLE IF EXISTS audit_tbls.table_three"
! PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
! NOTICE: table "table_one" does not exist, skipping
! CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.table_one"
! PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
! NOTICE: table "table_two" does not exist, skipping
! CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.table_two"
! PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
! NOTICE: table "table_three" does not exist, skipping
! CONTEXT: SQL statement "DROP TABLE IF EXISTS audit_tbls.table_three"
! PL/pgSQL function test_evtrig_dropped_objects() line 8 at EXECUTE statement
! SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast';
! type | schema | object | subobject | curr_user | sess_user
! --------------+------------+--------------------------+-----------+-----------+-----------
! table column | schema_one | table_one.a | | alvherre | alvherre
! schema | | schema_two | | alvherre | alvherre
! table | audit_tbls | table_two | | alvherre | alvherre
! type | audit_tbls | audit_tbls.table_two | | alvherre | alvherre
! type | audit_tbls | audit_tbls.table_two[] | | alvherre | alvherre
! table | schema_two | table_two | | alvherre | alvherre
! type | schema_two | schema_two.table_two | | alvherre | alvherre
! type | schema_two | schema_two.table_two[] | | alvherre | alvherre
! table | audit_tbls | table_three | | alvherre | alvherre
! type | audit_tbls | audit_tbls.table_three | | alvherre | alvherre
! type | audit_tbls | audit_tbls.table_three[] | | alvherre | alvherre
! table | schema_two | table_three | | alvherre | alvherre
! type | schema_two | schema_two.table_three | | alvherre | alvherre
! type | schema_two | schema_two.table_three[] | | alvherre | alvherre
! table column | audit_tbls | table_three.the_value | | alvherre | alvherre
! function | schema_two | add(integer,integer) | | alvherre | alvherre
! function | schema_two | newton(integer) | | alvherre | alvherre
! schema | | schema_one | | alvherre | alvherre
! table | schema_one | table_one | | alvherre | alvherre
! type | schema_one | schema_one.table_one | | alvherre | alvherre
! type | schema_one | schema_one.table_one[] | | alvherre | alvherre
! table | schema_one | table_two | | alvherre | alvherre
! type | schema_one | schema_one.table_two | | alvherre | alvherre
! type | schema_one | schema_one.table_two[] | | alvherre | alvherre
! table column | audit_tbls | table_two.the_value | | alvherre | alvherre
! table | schema_one | table_three | | alvherre | alvherre
! type | schema_one | schema_one.table_three | | alvherre | alvherre
! type | schema_one | schema_one.table_three[] | | alvherre | alvherre
! (28 rows)
!
! drop owned by regression_bob;
! SELECT * FROM dropped_objects WHERE type = 'schema';
! type | schema | object | subobject | curr_user | sess_user
! --------+--------+------------+-----------+-----------+-----------
! schema | | schema_two | | alvherre | alvherre
! schema | | schema_one | | alvherre | alvherre
! schema | | audit_tbls | | alvherre | alvherre
! (3 rows)
!
drop role regression_bob;
+ DROP EVENT TRIGGER regress_event_trigger_drop_objects;
*** a/src/test/regress/sql/event_trigger.sql
--- b/src/test/regress/sql/event_trigger.sql
***************
*** 97,106 **** drop event trigger regress_event_trigger;
-- should fail, regression_bob owns regress_event_trigger2/3
drop role regression_bob;
-- these are all OK; the second one should emit a NOTICE
drop event trigger if exists regress_event_trigger2;
drop event trigger if exists regress_event_trigger2;
drop event trigger regress_event_trigger3;
drop event trigger regress_event_trigger_end;
! drop function test_event_trigger();
drop role regression_bob;
--- 97,172 ----
-- should fail, regression_bob owns regress_event_trigger2/3
drop role regression_bob;
+ -- cleanup before next test
-- these are all OK; the second one should emit a NOTICE
drop event trigger if exists regress_event_trigger2;
drop event trigger if exists regress_event_trigger2;
drop event trigger regress_event_trigger3;
drop event trigger regress_event_trigger_end;
!
! -- test support for dropped objects
! CREATE SCHEMA schema_one authorization regression_bob;
! CREATE SCHEMA schema_two authorization regression_bob;
! CREATE SCHEMA audit_tbls authorization regression_bob;
! SET SESSION AUTHORIZATION regression_bob;
!
! CREATE TABLE schema_one.table_one(a int);
! CREATE TABLE schema_one.table_two(a int);
! CREATE TABLE schema_one.table_three(a int);
! CREATE TABLE audit_tbls.table_two(the_value schema_one.table_two);
!
! CREATE TABLE schema_two.table_two(a int);
! CREATE TABLE schema_two.table_three(a int, b text);
! CREATE TABLE audit_tbls.table_three(the_value schema_two.table_three);
!
! CREATE OR REPLACE FUNCTION schema_two.add(int, int) RETURNS int LANGUAGE plpgsql
! CALLED ON NULL INPUT
! AS $$ BEGIN RETURN coalesce($1,0) + coalesce($2,0); END; $$;
! CREATE AGGREGATE schema_two.newton
! (BASETYPE = int, SFUNC = schema_two.add, STYPE = int);
!
! RESET SESSION AUTHORIZATION;
!
! CREATE TABLE dropped_objects (type text,
! schema text,
! object text,
! subobject text,
! curr_user text,
! sess_user text);
!
! CREATE OR REPLACE FUNCTION test_evtrig_dropped_objects() RETURNS event_trigger
! LANGUAGE plpgsql AS $$
! DECLARE
! obj record;
! BEGIN
! FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
! LOOP
! IF obj.object_type = 'table' THEN
! EXECUTE format('DROP TABLE IF EXISTS audit_tbls.%s',
! obj.object_identity);
! END IF;
!
! INSERT INTO dropped_objects
! (type, schema, object, curr_user, sess_user) VALUES
! (obj.object_type, obj.schema_name, obj.object_identity,
! current_user, session_user);
! END LOOP;
! END
! $$;
!
! CREATE EVENT TRIGGER regress_event_trigger_drop_objects ON ddl_command_end
! WHEN TAG IN ('drop table', 'drop function', 'drop view',
! 'drop owned', 'drop schema', 'alter table')
! EXECUTE PROCEDURE test_evtrig_dropped_objects();
!
! ALTER TABLE schema_one.table_one DROP COLUMN a;
! DROP SCHEMA schema_one, schema_two CASCADE;
!
! SELECT * FROM dropped_objects WHERE schema IS NULL OR schema <> 'pg_toast';
!
! drop owned by regression_bob;
! SELECT * FROM dropped_objects WHERE type = 'schema';
!
drop role regression_bob;
+
+ DROP EVENT TRIGGER regress_event_trigger_drop_objects;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
