Alvaro Herrera escribió:

> Hmm, maybe I should be considering a pair of macros instead --
> UTILITY_START_DROP and UTILITY_END_DROP.  I'll give this a try.  Other
> ideas are welcome.

This seems to work.  See attached; I like the result because there's no
clutter and it supports all three cases without a problem.

I also added a new output column to pg_event_trigger_dropped_objects,
"subobject_name", which is NULL except when a column is being dropped,
in which case it contains the column name.  Also, the "object type"
column now says "table column" instead of "table" when dropping a
column.

Another question arose in testing: this reports dropping of temp
objects, too, but of course not always: particularly not when temp
objects are dropped at the end of a session (or the beginning of a
session that reuses a previously used temp schema).  I find this rather
inconsistent and I wonder if we should instead suppress reporting of
temp objects.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 71241c8..1e67d86 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -36,8 +36,8 @@
      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
+     DDL commands targeting shared objects &mdash; databases, roles, and tablespaces
+     &mdash; 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,6 +46,16 @@
    </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,6 +443,11 @@
         <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>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9b7e967..5721d1b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15702,4 +15702,54 @@ FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
         <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>
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 32f05bb..a91111b 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -198,6 +198,7 @@ static bool stack_address_present_add_flags(const ObjectAddress *object,
 								ObjectAddressStack *stack);
 static void getRelationDescription(StringInfo buffer, Oid relid);
 static void getOpFamilyDescription(StringInfo buffer, Oid opfid);
+static void getRelationTypeDescription(StringInfo buffer, Oid relid, int32 objectSubId);
 
 
 /*
@@ -267,6 +268,12 @@ performDeletion(const ObjectAddress *object,
 	{
 		ObjectAddress *thisobj = targetObjects->refs + i;
 
+		if ((!(flags & PERFORM_DELETION_INTERNAL)) &&
+			EventTriggerSupportsObjectType(getObjectClass(thisobj)))
+		{
+			evtrig_sqldrop_add_object(thisobj);
+		}
+
 		deleteOneObject(thisobj, &depRel, flags);
 	}
 
@@ -349,6 +356,12 @@ performMultipleDeletions(const ObjectAddresses *objects,
 	{
 		ObjectAddress *thisobj = targetObjects->refs + i;
 
+		if ((!(flags & PERFORM_DELETION_INTERNAL)) &&
+			EventTriggerSupportsObjectType(getObjectClass(thisobj)))
+		{
+			evtrig_sqldrop_add_object(thisobj);
+		}
+
 		deleteOneObject(thisobj, &depRel, flags);
 	}
 
@@ -366,6 +379,10 @@ performMultipleDeletions(const ObjectAddresses *objects,
  * 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,
@@ -3107,3 +3124,199 @@ pg_describe_object(PG_FUNCTION_ARGS)
 	description = getObjectDescription(&address);
 	PG_RETURN_TEXT_P(cstring_to_text(description));
 }
+
+char *
+getObjectTypeDescription(const ObjectAddress *object)
+{
+	StringInfoData buffer;
+
+	initStringInfo(&buffer);
+
+	switch (getObjectClass(object))
+	{
+		case OCLASS_CLASS:
+			getRelationTypeDescription(&buffer, object->objectId,
+									   object->objectSubId);
+			break;
+
+		case OCLASS_PROC:
+			appendStringInfo(&buffer, "function");
+			break;
+
+		case OCLASS_TYPE:
+			appendStringInfo(&buffer, "type");
+			break;
+
+		case OCLASS_CAST:
+			appendStringInfo(&buffer, "cast");
+			break;
+
+		case OCLASS_COLLATION:
+			appendStringInfo(&buffer, "collation");
+			break;
+
+		case OCLASS_CONSTRAINT:
+			appendStringInfo(&buffer, "constraint");
+			break;
+
+		case OCLASS_CONVERSION:
+			appendStringInfo(&buffer, "conversion");
+			break;
+
+		case OCLASS_DEFAULT:
+			appendStringInfo(&buffer, "default value");
+			break;
+
+		case OCLASS_LANGUAGE:
+			appendStringInfo(&buffer, "language");
+			break;
+
+		case OCLASS_LARGEOBJECT:
+			appendStringInfo(&buffer, "large object");
+			break;
+
+		case OCLASS_OPERATOR:
+			appendStringInfo(&buffer, "operator");
+			break;
+
+		case OCLASS_OPCLASS:
+			appendStringInfo(&buffer, "operator class");
+			break;
+
+		case OCLASS_OPFAMILY:
+			appendStringInfo(&buffer, "operator family");
+			break;
+
+		case OCLASS_AMOP:
+			appendStringInfo(&buffer, "operator of access method");
+			break;
+
+		case OCLASS_AMPROC:
+			appendStringInfo(&buffer, "function of access method");
+			break;
+
+		case OCLASS_REWRITE:
+			appendStringInfo(&buffer, "rule");
+			break;
+
+		case OCLASS_TRIGGER:
+			appendStringInfo(&buffer, "trigger");
+			break;
+
+		case OCLASS_SCHEMA:
+			appendStringInfo(&buffer, "schema");
+			break;
+
+		case OCLASS_TSPARSER:
+			appendStringInfo(&buffer, "text search parser");
+			break;
+
+		case OCLASS_TSDICT:
+			appendStringInfo(&buffer, "text search dictionary");
+			break;
+
+		case OCLASS_TSTEMPLATE:
+			appendStringInfo(&buffer, "text search template");
+			break;
+
+		case OCLASS_TSCONFIG:
+			appendStringInfo(&buffer, "text search configuration");
+			break;
+
+		case OCLASS_ROLE:
+			appendStringInfo(&buffer, "role");
+			break;
+
+		case OCLASS_DATABASE:
+			appendStringInfo(&buffer, "database");
+			break;
+
+		case OCLASS_TBLSPACE:
+			appendStringInfo(&buffer, "tablespace");
+			break;
+
+		case OCLASS_FDW:
+			appendStringInfo(&buffer, "foreign-data wrapper");
+			break;
+
+		case OCLASS_FOREIGN_SERVER:
+			appendStringInfo(&buffer, "server");
+			break;
+
+		case OCLASS_USER_MAPPING:
+			appendStringInfo(&buffer, "user mapping");
+			break;
+
+		case OCLASS_DEFACL:
+			/* XXX do we need more detail here? */
+			appendStringInfo(&buffer, "default ACL");
+			break;
+
+		case OCLASS_EXTENSION:
+			appendStringInfo(&buffer, "extension");
+			break;
+
+		case OCLASS_EVENT_TRIGGER:
+			appendStringInfo(&buffer, "event trigger");
+			break;
+
+		default:
+			appendStringInfo(&buffer, "unrecognized object type");
+			break;
+	}
+
+	return buffer.data;
+}
+
+/*
+ * subroutine for getObjectTypeDescription: describe a relation type
+ */
+static void
+getRelationTypeDescription(StringInfo buffer, Oid relid, int32 objectSubId)
+{
+	HeapTuple	relTup;
+	Form_pg_class relForm;
+
+	relTup = SearchSysCache1(RELOID,
+							 ObjectIdGetDatum(relid));
+	if (!HeapTupleIsValid(relTup))
+		elog(ERROR, "cache lookup failed for relation %u", relid);
+	relForm = (Form_pg_class) GETSTRUCT(relTup);
+
+	switch (relForm->relkind)
+	{
+		case RELKIND_RELATION:
+			appendStringInfo(buffer, "table");
+			break;
+		case RELKIND_INDEX:
+			appendStringInfo(buffer, "index");
+			break;
+		case RELKIND_SEQUENCE:
+			appendStringInfo(buffer, "sequence");
+			break;
+		case RELKIND_TOASTVALUE:
+			appendStringInfo(buffer, "toast table");
+			break;
+		case RELKIND_VIEW:
+			appendStringInfo(buffer, "view");
+			break;
+		case RELKIND_MATVIEW:
+			appendStringInfo(buffer, "materialized view");
+			break;
+		case RELKIND_COMPOSITE_TYPE:
+			appendStringInfo(buffer, "composite type");
+			break;
+		case RELKIND_FOREIGN_TABLE:
+			appendStringInfo(buffer, "foreign table");
+			break;
+		default:
+			/* shouldn't get here */
+			appendStringInfo(buffer, "relation");
+			break;
+	}
+
+	if (objectSubId != 0)
+		appendStringInfo(buffer, " column");
+
+	ReleaseSysCache(relTup);
+}
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 6f60d7c..04f6893 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -1345,6 +1345,24 @@ get_object_aclkind(Oid class_id)
 }
 
 /*
+ * Return whether we have useful data for the given object class in the
+ * ObjectProperty table.
+ */
+bool
+is_objectclass_supported(Oid class_id)
+{
+	int			index;
+
+	for (index = 0; index < lengthof(ObjectProperty); index++)
+	{
+		if (ObjectProperty[index].class_oid == class_id)
+			return true;
+	}
+
+	return false;
+}
+
+/*
  * Find ObjectProperty structure by class_id.
  */
 static ObjectPropertyType *
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index 416a068..8a88c91 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -748,58 +748,6 @@ ExecAlterOwnerStmt(AlterOwnerStmt *stmt)
 }
 
 /*
- * Return a copy of the tuple for the object with the given object OID, from
- * the given catalog (which must have been opened by the caller and suitably
- * locked).  NULL is returned if the OID is not found.
- *
- * We try a syscache first, if available.
- *
- * XXX this function seems general in possible usage.  Given sufficient callers
- * elsewhere, we should consider moving it to a more appropriate place.
- */
-static HeapTuple
-get_catalog_object_by_oid(Relation catalog, Oid objectId)
-{
-	HeapTuple	tuple;
-	Oid			classId = RelationGetRelid(catalog);
-	int			oidCacheId = get_object_catcache_oid(classId);
-
-	if (oidCacheId > 0)
-	{
-		tuple = SearchSysCacheCopy1(oidCacheId, ObjectIdGetDatum(objectId));
-		if (!HeapTupleIsValid(tuple))  /* should not happen */
-			return NULL;
-	}
-	else
-	{
-		Oid			oidIndexId = get_object_oid_index(classId);
-		SysScanDesc	scan;
-		ScanKeyData	skey;
-
-		Assert(OidIsValid(oidIndexId));
-
-		ScanKeyInit(&skey,
-					ObjectIdAttributeNumber,
-					BTEqualStrategyNumber, F_OIDEQ,
-					ObjectIdGetDatum(objectId));
-
-		scan = systable_beginscan(catalog, oidIndexId, true,
-								  SnapshotNow, 1, &skey);
-		tuple = systable_getnext(scan);
-		if (!HeapTupleIsValid(tuple))
-		{
-			systable_endscan(scan);
-			return NULL;
-		}
-		tuple = heap_copytuple(tuple);
-
-		systable_endscan(scan);
-	}
-
-	return tuple;
-}
-
-/*
  * Generic function to change the ownership of a given object, for simple
  * cases (won't work for tables, nor other cases where we need to do more than
  * change the ownership column of a single catalog entry).
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 596178f..d467fb8 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -25,6 +25,7 @@
 #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,6 +40,10 @@
 #include "utils/syscache.h"
 #include "tcop/utility.h"
 
+/* -- Globally visible state -- */
+/* list of objects dropped by current command */
+slist_head	SQLDropList = SLIST_STATIC_INIT(SQLDropList);
+
 typedef struct
 {
 	const char	   *obtypename;
@@ -89,6 +94,17 @@ static event_trigger_support_data event_trigger_support[] = {
 	{ NULL, false }
 };
 
+/* Support for dropped objects */
+typedef struct SQLDropObject
+{
+	ObjectAddress	address;
+	char		   *objname;
+	char		   *subobjname;
+	char		   *schemaname;
+	char		   *objecttype;
+	slist_node		next;
+} SQLDropObject;
+
 static void AlterEventTriggerOwner_internal(Relation rel,
 											HeapTuple tup,
 											Oid newOwnerId);
@@ -151,8 +167,12 @@ CreateEventTrigger(CreateEventTrigStmt *stmt)
 	}
 
 	/* Validate tag list, if any. */
-	if (strcmp(stmt->eventname, "ddl_command_start") == 0 && tags != NULL)
+	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,7 +240,8 @@ 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)
+		pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0 ||
+		pg_strcasecmp(tag, "DROP OWNED") == 0)
 		return EVENT_TRIGGER_COMMAND_TAG_OK;
 
 	/*
@@ -827,3 +848,215 @@ EventTriggerSupportsObjectType(ObjectType obtype)
 	}
 	return true;
 }
+
+/*
+ * Support for dropped objects information on event trigger functions.
+ *
+ * We keep the list of objects dropped by the current command in SQLDropList
+ * (comprising SQLDropObject items).  Each command that might drop objects
+ * saves the current list in a local variable, initializes a new empty list and
+ * does the dependency.c dance to drop objects, which populates the list; when
+ * the event triggers are invoked they can consume the list via
+ * pg_event_trigger_dropped_objects().  When the command finishes, the list is
+ * cleared and the original list is restored.  This is to support the case that
+ * an event trigger function drops objects "reentrantly".
+ */
+
+/*
+ * Initialize state of objects dropped
+ */
+void
+EventTriggerInitializeDrop(slist_head *save_objlist)
+{
+	/* save previous state in local vars of caller, for later restore */
+	*save_objlist = SQLDropList;
+
+	slist_init(&SQLDropList);
+}
+
+/*
+ * Restore state after running a command that drops objects; free memory from a
+ * list we may have created.
+ */
+void
+EventTriggerFinalizeDrop(slist_head save_objlist)
+{
+	slist_mutable_iter	iter;
+
+	slist_foreach_modify(iter, &SQLDropList)
+	{
+		SQLDropObject  *obj = slist_container(SQLDropObject, next, iter.cur);
+
+		if (obj->objname)
+			pfree(obj->objname);
+		if (obj->subobjname)
+			pfree(obj->subobjname);
+		if (obj->schemaname)
+			pfree(obj->schemaname);
+		pfree(obj);
+	}
+
+	SQLDropList = save_objlist;
+}
+
+/*
+ * Register one object as being dropped by the current command.
+ *
+ * XXX do we need to think about memory context these things are stored in?
+ */
+void
+evtrig_sqldrop_add_object(ObjectAddress *object)
+{
+	SQLDropObject  *obj;
+
+	Assert(EventTriggerSupportsObjectType(getObjectClass(object)));
+
+	obj = palloc0(sizeof(SQLDropObject));
+	obj->address = *object;
+
+	/*
+	 * Obtain object and schema names from the object's catalog tuple, if one
+	 * exists.
+	 */
+	if (is_objectclass_supported(obj->address.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_name(obj->address.classId);
+			if (attnum != InvalidAttrNumber)
+			{
+				datum = heap_getattr(tuple, attnum,
+									 RelationGetDescr(catalog), &isnull);
+				if (!isnull)
+					obj->objname = pstrdup(NameStr(*DatumGetName(datum)));
+			}
+
+			attnum = get_object_attnum_namespace(obj->address.classId);
+			if (attnum != InvalidAttrNumber)
+			{
+				datum = heap_getattr(tuple, attnum,
+									 RelationGetDescr(catalog), &isnull);
+				if (!isnull)
+					obj->schemaname = get_namespace_name(DatumGetObjectId(datum));
+			}
+		}
+
+		/* add a column name, if present */
+		if (obj->address.objectSubId != 0)
+		{
+			/* must be a relation of some kind */
+			Assert(RelationGetRelid(catalog) == RelationRelationId);
+
+			obj->subobjname = get_attname(obj->address.objectId,
+										  obj->address.objectSubId);
+		}
+
+		heap_close(catalog, AccessShareLock);
+	}
+
+	/* and object type, too */
+	obj->objecttype = getObjectTypeDescription(&obj->address);
+
+	slist_push_head(&SQLDropList, &obj->next);
+}
+
+/*
+ * 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;
+
+	/* 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, &SQLDropList)
+	{
+		SQLDropObject *obj;
+		int			i = 0;
+		Datum		values[7];
+		bool		nulls[7];
+
+		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);
+
+		/* objname */
+		if (obj->objname)
+			values[i++] = CStringGetTextDatum(obj->objname);
+		else
+			nulls[i++] = true;
+
+		/* subobjname */
+		if (obj->subobjname)
+			values[i++] = CStringGetTextDatum(obj->subobjname);
+		else
+			nulls[i++] = true;
+
+		/* schemaname */
+		if (obj->schemaname)
+			values[i++] = CStringGetTextDatum(obj->schemaname);
+		else
+			nulls[i++] = true;
+
+		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+	}
+
+	/* clean up and return the tuplestore */
+	tuplestore_donestoring(tupstore);
+
+	return (Datum) 0;
+}
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index a1c03f1..66839d8 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -370,6 +370,52 @@ ProcessUtility(Node *parsetree,
 		} \
 	} while (0)
 
+/*
+ * UTILITY_BEGIN_DROP and UTILITY_END_DROP are a pair of macros to enclose a
+ * code fragment that executes a command that may drop database objects, so
+ * that the event trigger environment is appropriately setup.
+ *
+ * Note these macros will call EventTriggerDDLCommandEnd if the object type is
+ * supported; caller must make sure to call EventTriggerDDLCommandStart by
+ * itself.
+ */
+#define UTILITY_BEGIN_DROP(isCompleteQuery, has_objtype, objtype) \
+	do { \
+		slist_head	_save_objlist; \
+		bool		_supported; \
+		bool		_isComplete = isCompleteQuery; \
+		\
+		_supported = has_objtype ? EventTriggerSupportsObjectType(objtype) : true; \
+		\
+		if (_isComplete) \
+		{ \
+			EventTriggerInitializeDrop(&_save_objlist); \
+		} \
+		PG_TRY(); \
+		{
+
+#define UTILITY_END_DROP(parsetree) \
+		} \
+		if (_isComplete && _supported) \
+		{ \
+			EventTriggerDDLCommandEnd(parsetree); \
+		} \
+		\
+		PG_CATCH(); \
+		{ \
+			if (_isComplete && _supported) \
+			{ \
+				EventTriggerFinalizeDrop(_save_objlist); \
+			} \
+			PG_RE_THROW(); \
+		} \
+		PG_END_TRY(); \
+		if (_isComplete && _supported) \
+		{ \
+			EventTriggerFinalizeDrop(_save_objlist); \
+		} \
+	} while (0)
+
 void
 standard_ProcessUtility(Node *parsetree,
 						const char *queryString,
@@ -704,6 +750,8 @@ standard_ProcessUtility(Node *parsetree,
 					&& EventTriggerSupportsObjectType(stmt->removeType))
 					EventTriggerDDLCommandStart(parsetree);
 
+				UTILITY_BEGIN_DROP(isCompleteQuery, true, stmt->removeType);
+
 				switch (stmt->removeType)
 				{
 					case OBJECT_INDEX:
@@ -724,9 +772,7 @@ standard_ProcessUtility(Node *parsetree,
 						break;
 				}
 
-				if (isCompleteQuery
-					&& EventTriggerSupportsObjectType(stmt->removeType))
-					EventTriggerDDLCommandEnd(parsetree);
+				UTILITY_END_DROP(parsetree);
 
 				break;
 			}
@@ -826,6 +872,8 @@ standard_ProcessUtility(Node *parsetree,
 					/* Run parse analysis ... */
 					stmts = transformAlterTableStmt(atstmt, queryString);
 
+					UTILITY_BEGIN_DROP(isCompleteQuery, true, OBJECT_TABLE);
+
 					/* ... and do it */
 					foreach(l, stmts)
 					{
@@ -851,6 +899,8 @@ standard_ProcessUtility(Node *parsetree,
 						if (lnext(l) != NULL)
 							CommandCounterIncrement();
 					}
+
+					UTILITY_END_DROP(parsetree);
 				}
 				else
 					ereport(NOTICE,
@@ -1248,8 +1298,12 @@ standard_ProcessUtility(Node *parsetree,
 			break;
 
 		case T_DropOwnedStmt:
-			/* no event triggers for global objects */
+			if (isCompleteQuery)
+				EventTriggerDDLCommandStart(parsetree);
+
+			UTILITY_BEGIN_DROP(isCompleteQuery, false, 0);
 			DropOwnedObjects((DropOwnedStmt *) parsetree);
+			UTILITY_END_DROP(parsetree);
 			break;
 
 		case T_ReassignOwnedStmt:
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 5865962..7f25de4 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -18,6 +18,7 @@
 #include "access/hash.h"
 #include "access/htup_details.h"
 #include "access/nbtree.h"
+#include "access/sysattr.h"
 #include "bootstrap/bootstrap.h"
 #include "catalog/pg_amop.h"
 #include "catalog/pg_amproc.h"
@@ -40,6 +41,7 @@
 #include "utils/lsyscache.h"
 #include "utils/rel.h"
 #include "utils/syscache.h"
+#include "utils/tqual.h"
 #include "utils/typcache.h"
 
 /* Hook for plugins to get control in get_attavgwidth() */
@@ -2926,3 +2928,54 @@ get_range_subtype(Oid rangeOid)
 	else
 		return InvalidOid;
 }
+
+/*				------------- GENERIC --------------				 */
+
+/*
+ * Return a copy of the tuple for the object with the given object OID, from
+ * the given catalog (which must have been opened by the caller and suitably
+ * locked).  NULL is returned if the OID is not found.
+ *
+ * We try a syscache first, if available.
+ */
+HeapTuple
+get_catalog_object_by_oid(Relation catalog, Oid objectId)
+{
+	HeapTuple	tuple;
+	Oid			classId = RelationGetRelid(catalog);
+	int			oidCacheId = get_object_catcache_oid(classId);
+
+	if (oidCacheId > 0)
+	{
+		tuple = SearchSysCacheCopy1(oidCacheId, ObjectIdGetDatum(objectId));
+		if (!HeapTupleIsValid(tuple))  /* should not happen */
+			return NULL;
+	}
+	else
+	{
+		Oid			oidIndexId = get_object_oid_index(classId);
+		SysScanDesc	scan;
+		ScanKeyData	skey;
+
+		Assert(OidIsValid(oidIndexId));
+
+		ScanKeyInit(&skey,
+					ObjectIdAttributeNumber,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(objectId));
+
+		scan = systable_beginscan(catalog, oidIndexId, true,
+								  SnapshotNow, 1, &skey);
+		tuple = systable_getnext(scan);
+		if (!HeapTupleIsValid(tuple))
+		{
+			systable_endscan(scan);
+			return NULL;
+		}
+		tuple = heap_copytuple(tuple);
+
+		systable_endscan(scan);
+	}
+
+	return tuple;
+}
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 8e0837f..106d8fb 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -179,6 +179,8 @@ extern ObjectClass getObjectClass(const ObjectAddress *object);
 extern char *getObjectDescription(const ObjectAddress *object);
 extern char *getObjectDescriptionOids(Oid classid, Oid objid);
 
+extern char *getObjectTypeDescription(const ObjectAddress *object);
+
 extern ObjectAddresses *new_object_addresses(void);
 
 extern void add_exact_object_address(const ObjectAddress *object,
diff --git a/src/include/catalog/objectaddress.h b/src/include/catalog/objectaddress.h
index ffaf4ea..8a34b50 100644
--- a/src/include/catalog/objectaddress.h
+++ b/src/include/catalog/objectaddress.h
@@ -38,6 +38,7 @@ extern void check_object_ownership(Oid roleid,
 
 extern Oid	get_object_namespace(const ObjectAddress *address);
 
+extern bool				is_objectclass_supported(Oid class_id);
 extern Oid				get_object_oid_index(Oid class_id);
 extern int				get_object_catcache_oid(Oid class_id);
 extern int				get_object_catcache_name(Oid class_id);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 0e26ebf..09b5e61 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4682,6 +4682,9 @@ DATA(insert OID = 3473 (  spg_range_quad_leaf_consistent	PGNSP PGUID 12 1 0 0 0
 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,25}" "{o,o,o,o,o,o,o}" "{classid, objid, objsubid, object_type, object_name, subobject_name, schema_name}" _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,
diff --git a/src/include/commands/event_trigger.h b/src/include/commands/event_trigger.h
index 74c150b..2730e7a 100644
--- a/src/include/commands/event_trigger.h
+++ b/src/include/commands/event_trigger.h
@@ -13,7 +13,10 @@
 #ifndef EVENT_TRIGGER_H
 #define EVENT_TRIGGER_H
 
+#include "catalog/dependency.h"
+#include "catalog/objectaddress.h"
 #include "catalog/pg_event_trigger.h"
+#include "lib/ilist.h"
 #include "nodes/parsenodes.h"
 
 typedef struct EventTriggerData
@@ -43,4 +46,8 @@ extern bool EventTriggerSupportsObjectType(ObjectType obtype);
 extern void EventTriggerDDLCommandStart(Node *parsetree);
 extern void EventTriggerDDLCommandEnd(Node *parsetree);
 
+extern void EventTriggerInitializeDrop(slist_head *save_objlist);
+extern void EventTriggerFinalizeDrop(slist_head save_objlist);
+extern void evtrig_sqldrop_add_object(ObjectAddress *object);
+
 #endif   /* EVENT_TRIGGER_H */
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index c0debe4..785d1de 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1147,6 +1147,9 @@ extern Datum pg_describe_object(PG_FUNCTION_ARGS);
 /* 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);
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 49f459a..dfa6eb7 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -16,6 +16,7 @@
 #include "access/attnum.h"
 #include "access/htup.h"
 #include "nodes/pg_list.h"
+#include "utils/relcache.h"
 
 /* Result list element for get_op_btree_interpretation */
 typedef struct OpBtreeInterpretation
@@ -152,6 +153,7 @@ extern void free_attstatsslot(Oid atttype,
 				  float4 *numbers, int nnumbers);
 extern char *get_namespace_name(Oid nspid);
 extern Oid	get_range_subtype(Oid rangeOid);
+extern HeapTuple get_catalog_object_by_oid(Relation catalog, Oid objectId);
 
 #define type_is_array(typid)  (get_element_type(typid) != InvalidOid)
 /* type_is_array_domain accepts both plain arrays and domains over arrays */
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index bf020de..264bbb3 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -93,11 +93,129 @@ 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
+-- 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;
-drop function test_event_trigger();
+\dy
+              List of event triggers
+ Name | Event | Owner | Enabled | Procedure | Tags 
+------+-------+-------+---------+-----------+------
+(0 rows)
+
+-- 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);
+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_name);
+        END IF;
+
+	INSERT INTO dropped_objects
+		(type, schema, object, subobject, curr_user, sess_user) VALUES
+		(obj.object_type, obj.schema_name, obj.object_name,
+		obj.subobject_name, 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 7 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 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 | table_two    |           | alvherre  | alvherre
+ type         | audit_tbls | _table_two   |           | alvherre  | alvherre
+ table        | schema_two | table_two    |           | alvherre  | alvherre
+ type         | schema_two | table_two    |           | alvherre  | alvherre
+ type         | schema_two | _table_two   |           | alvherre  | alvherre
+ table        | audit_tbls | table_three  |           | alvherre  | alvherre
+ type         | audit_tbls | table_three  |           | alvherre  | alvherre
+ type         | audit_tbls | _table_three |           | alvherre  | alvherre
+ table        | schema_two | table_three  |           | alvherre  | alvherre
+ type         | schema_two | table_three  |           | alvherre  | alvherre
+ type         | schema_two | _table_three |           | alvherre  | alvherre
+ table column | audit_tbls | table_three  | the_value | alvherre  | alvherre
+ schema       |            | schema_one   |           | alvherre  | alvherre
+ table        | schema_one | table_one    |           | alvherre  | alvherre
+ type         | schema_one | table_one    |           | alvherre  | alvherre
+ type         | schema_one | _table_one   |           | alvherre  | alvherre
+ table        | schema_one | table_two    |           | alvherre  | alvherre
+ type         | schema_one | table_two    |           | alvherre  | alvherre
+ type         | 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 | table_three  |           | alvherre  | alvherre
+ type         | schema_one | _table_three |           | alvherre  | alvherre
+(26 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;
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index a07dcd7..0c5e7db 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -97,10 +97,71 @@ drop event trigger regress_event_trigger;
 -- 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;
-drop function test_event_trigger();
+\dy
+
+-- 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);
+
+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_name);
+        END IF;
+
+	INSERT INTO dropped_objects
+		(type, schema, object, subobject, curr_user, sess_user) VALUES
+		(obj.object_type, obj.schema_name, obj.object_name,
+		obj.subobject_name, 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

Reply via email to