Allow on-the-fly capture of DDL event details

This feature lets user code inspect and take action on DDL events.
Whenever a ddl_command_end event trigger is installed, DDL actions
executed are saved to a list which can be inspected during execution of
a function attached to ddl_command_end.

The set-returning function pg_event_trigger_ddl_commands can be used to
list actions so captured; it returns data about the type of command
executed, as well as the affected object.  This is sufficient for many
uses of this feature.  For the cases where it is not, we also provide a
"command" column of a new pseudo-type pg_ddl_command, which is a
pointer to a C structure that can be accessed by C code.  The struct
contains all the info necessary to completely inspect and even
reconstruct the executed command.

There is no actual deparse code here; that's expected to come later.
What we have is enough infrastructure that the deparsing can be done in
an external extension.  The intention is that we will add some deparsing
code in a later release, as an in-core extension.

A new test module is included.  It's probably insufficient as is, but it
should be sufficient as a starting point for a more complete and
future-proof approach.

Authors: Álvaro Herrera, with some help from Andres Freund, Ian Barwick,
Abhijit Menon-Sen.

Reviews by Andres Freund, Robert Haas, Amit Kapila, Michael Paquier,
Craig Ringer, David Steele.
Additional input from Chris Browne, Dimitri Fontaine, Stephen Frost,
Petr Jelínek, Tom Lane, Jim Nasby, Steven Singer, Pavel Stěhule.

Based on original work by Dimitri Fontaine, though I didn't use his
code.

Discussion:
  https://www.postgresql.org/message-id/[email protected]
  https://www.postgresql.org/message-id/[email protected]
  https://www.postgresql.org/message-id/[email protected]

1 files changed, 195 insertions, 79 deletions

diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 59f09dc93af..78bfd349a77 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -912,7 +912,9 @@ ProcessUtilitySlow(Node *parsetree,
 	bool		isTopLevel = (context == PROCESS_UTILITY_TOPLEVEL);
 	bool		isCompleteQuery = (context <= PROCESS_UTILITY_QUERY);
 	bool		needCleanup;
+	bool		commandCollected = false;
 	ObjectAddress address;
+	ObjectAddress secondaryObject = InvalidObjectAddress;
 
 	/* All event trigger calls are done only when isCompleteQuery is true */
 	needCleanup = isCompleteQuery && EventTriggerBeginCompleteQuery();
@@ -931,6 +933,11 @@ ProcessUtilitySlow(Node *parsetree,
 			case T_CreateSchemaStmt:
 				CreateSchemaCommand((CreateSchemaStmt *) parsetree,
 									queryString);
+				/*
+				 * EventTriggerCollectSimpleCommand called by
+				 * CreateSchemaCommand
+				 */
+				commandCollected = true;
 				break;
 
 			case T_CreateStmt:
@@ -957,6 +964,9 @@ ProcessUtilitySlow(Node *parsetree,
 							address = DefineRelation((CreateStmt *) stmt,
 													 RELKIND_RELATION,
 													 InvalidOid, NULL);
+							EventTriggerCollectSimpleCommand(address,
+															 secondaryObject,
+															 stmt);
 
 							/*
 							 * Let NewRelationCreateToastTable decide if this
@@ -989,10 +999,17 @@ ProcessUtilitySlow(Node *parsetree,
 													 InvalidOid, NULL);
 							CreateForeignTable((CreateForeignTableStmt *) stmt,
 											   address.objectId);
+							EventTriggerCollectSimpleCommand(address,
+															 secondaryObject,
+															 stmt);
 						}
 						else
 						{
-							/* Recurse for anything else */
+							/*
+							 * Recurse for anything else.  Note the recursive
+							 * call will stash the objects so created into our
+							 * event trigger context.
+							 */
 							ProcessUtility(stmt,
 										   queryString,
 										   PROCESS_UTILITY_SUBCOMMAND,
@@ -1005,6 +1022,12 @@ ProcessUtilitySlow(Node *parsetree,
 						if (lnext(l) != NULL)
 							CommandCounterIncrement();
 					}
+
+					/*
+					 * The multiple commands generated here are stashed
+					 * individually, so disable collection below.
+					 */
+					commandCollected = true;
 				}
 				break;
 
@@ -1031,6 +1054,10 @@ ProcessUtilitySlow(Node *parsetree,
 						stmts = transformAlterTableStmt(relid, atstmt,
 														queryString);
 
+						/* ... ensure we have an event trigger context ... */
+						EventTriggerAlterTableStart(parsetree);
+						EventTriggerAlterTableRelid(relid);
+
 						/* ... and do it */
 						foreach(l, stmts)
 						{
@@ -1044,25 +1071,41 @@ ProcessUtilitySlow(Node *parsetree,
 							}
 							else
 							{
-								/* Recurse for anything else */
+								/*
+								 * Recurse for anything else.  If we need to do
+								 * so, "close" the current complex-command set,
+								 * and start a new one at the bottom; this is
+								 * needed to ensure the ordering of queued
+								 * commands is consistent with the way they are
+								 * executed here.
+								 */
+								EventTriggerAlterTableEnd();
 								ProcessUtility(stmt,
 											   queryString,
 											   PROCESS_UTILITY_SUBCOMMAND,
 											   params,
 											   None_Receiver,
 											   NULL);
+								EventTriggerAlterTableStart(parsetree);
+								EventTriggerAlterTableRelid(relid);
 							}
 
 							/* Need CCI between commands */
 							if (lnext(l) != NULL)
 								CommandCounterIncrement();
 						}
+
+						/* done */
+						EventTriggerAlterTableEnd();
 					}
 					else
 						ereport(NOTICE,
 						  (errmsg("relation \"%s\" does not exist, skipping",
 								  atstmt->relation->relname)));
 				}
+
+				/* ALTER TABLE stashes commands internally */
+				commandCollected = true;
 				break;
 
 			case T_AlterDomainStmt:
@@ -1081,31 +1124,37 @@ ProcessUtilitySlow(Node *parsetree,
 							 * Recursively alter column default for table and,
 							 * if requested, for descendants
 							 */
-							AlterDomainDefault(stmt->typeName,
-											   stmt->def);
+							address =
+								AlterDomainDefault(stmt->typeName,
+												   stmt->def);
 							break;
 						case 'N':		/* ALTER DOMAIN DROP NOT NULL */
-							AlterDomainNotNull(stmt->typeName,
-											   false);
+							address =
+								AlterDomainNotNull(stmt->typeName,
+												   false);
 							break;
 						case 'O':		/* ALTER DOMAIN SET NOT NULL */
-							AlterDomainNotNull(stmt->typeName,
-											   true);
+							address =
+								AlterDomainNotNull(stmt->typeName,
+												   true);
 							break;
 						case 'C':		/* ADD CONSTRAINT */
-							AlterDomainAddConstraint(stmt->typeName,
-													 stmt->def,
-													 NULL);
+							address =
+								AlterDomainAddConstraint(stmt->typeName,
+														 stmt->def,
+														 &secondaryObject);
 							break;
 						case 'X':		/* DROP CONSTRAINT */
-							AlterDomainDropConstraint(stmt->typeName,
-													  stmt->name,
-													  stmt->behavior,
-													  stmt->missing_ok);
+							address =
+								AlterDomainDropConstraint(stmt->typeName,
+														  stmt->name,
+														  stmt->behavior,
+														  stmt->missing_ok);
 							break;
 						case 'V':		/* VALIDATE CONSTRAINT */
-							AlterDomainValidateConstraint(stmt->typeName,
-														  stmt->name);
+							address =
+								AlterDomainValidateConstraint(stmt->typeName,
+															  stmt->name);
 							break;
 						default:		/* oops */
 							elog(ERROR, "unrecognized alter domain type: %d",
@@ -1125,41 +1174,46 @@ ProcessUtilitySlow(Node *parsetree,
 					switch (stmt->kind)
 					{
 						case OBJECT_AGGREGATE:
-							DefineAggregate(stmt->defnames, stmt->args,
-											stmt->oldstyle, stmt->definition,
-											queryString);
+							address =
+								DefineAggregate(stmt->defnames, stmt->args,
+												stmt->oldstyle,
+												stmt->definition, queryString);
 							break;
 						case OBJECT_OPERATOR:
 							Assert(stmt->args == NIL);
-							DefineOperator(stmt->defnames, stmt->definition);
+							address = DefineOperator(stmt->defnames,
+													  stmt->definition);
 							break;
 						case OBJECT_TYPE:
 							Assert(stmt->args == NIL);
-							DefineType(stmt->defnames, stmt->definition);
+							address = DefineType(stmt->defnames,
+												  stmt->definition);
 							break;
 						case OBJECT_TSPARSER:
 							Assert(stmt->args == NIL);
-							DefineTSParser(stmt->defnames, stmt->definition);
+							address = DefineTSParser(stmt->defnames,
+													  stmt->definition);
 							break;
 						case OBJECT_TSDICTIONARY:
 							Assert(stmt->args == NIL);
-							DefineTSDictionary(stmt->defnames,
-											   stmt->definition);
+							address = DefineTSDictionary(stmt->defnames,
+														  stmt->definition);
 							break;
 						case OBJECT_TSTEMPLATE:
 							Assert(stmt->args == NIL);
-							DefineTSTemplate(stmt->defnames,
-											 stmt->definition);
+							address = DefineTSTemplate(stmt->defnames,
+														stmt->definition);
 							break;
 						case OBJECT_TSCONFIGURATION:
 							Assert(stmt->args == NIL);
-							DefineTSConfiguration(stmt->defnames,
-												  stmt->definition,
-												  NULL);
+							address = DefineTSConfiguration(stmt->defnames,
+															 stmt->definition,
+															 &secondaryObject);
 							break;
 						case OBJECT_COLLATION:
 							Assert(stmt->args == NIL);
-							DefineCollation(stmt->defnames, stmt->definition);
+							address = DefineCollation(stmt->defnames,
+													   stmt->definition);
 							break;
 						default:
 							elog(ERROR, "unrecognized define stmt type: %d",
@@ -1200,143 +1254,184 @@ ProcessUtilitySlow(Node *parsetree,
 					stmt = transformIndexStmt(relid, stmt, queryString);
 
 					/* ... and do it */
-					DefineIndex(relid,	/* OID of heap relation */
-								stmt,
-								InvalidOid,		/* no predefined OID */
-								false,	/* is_alter_table */
-								true,	/* check_rights */
-								false,	/* skip_build */
-								false); /* quiet */
+					EventTriggerAlterTableStart(parsetree);
+					address =
+						DefineIndex(relid,	/* OID of heap relation */
+									stmt,
+									InvalidOid,		/* no predefined OID */
+									false,	/* is_alter_table */
+									true,	/* check_rights */
+									false,	/* skip_build */
+									false); /* quiet */
+					/*
+					 * Add the CREATE INDEX node itself to stash right away; if
+					 * there were any commands stashed in the ALTER TABLE code,
+					 * we need them to appear after this one.
+					 */
+					EventTriggerCollectSimpleCommand(address, secondaryObject,
+													 parsetree);
+					commandCollected = true;
+					EventTriggerAlterTableEnd();
 				}
 				break;
 
 			case T_CreateExtensionStmt:
-				CreateExtension((CreateExtensionStmt *) parsetree);
+				address = CreateExtension((CreateExtensionStmt *) parsetree);
 				break;
 
 			case T_AlterExtensionStmt:
-				ExecAlterExtensionStmt((AlterExtensionStmt *) parsetree);
+				address = ExecAlterExtensionStmt((AlterExtensionStmt *) parsetree);
 				break;
 
 			case T_AlterExtensionContentsStmt:
-				ExecAlterExtensionContentsStmt((AlterExtensionContentsStmt *) parsetree,
-											   NULL);
+				address = ExecAlterExtensionContentsStmt((AlterExtensionContentsStmt *) parsetree,
+														  &secondaryObject);
 				break;
 
 			case T_CreateFdwStmt:
-				CreateForeignDataWrapper((CreateFdwStmt *) parsetree);
+				address = CreateForeignDataWrapper((CreateFdwStmt *) parsetree);
 				break;
 
 			case T_AlterFdwStmt:
-				AlterForeignDataWrapper((AlterFdwStmt *) parsetree);
+				address = AlterForeignDataWrapper((AlterFdwStmt *) parsetree);
 				break;
 
 			case T_CreateForeignServerStmt:
-				CreateForeignServer((CreateForeignServerStmt *) parsetree);
+				address = CreateForeignServer((CreateForeignServerStmt *) parsetree);
 				break;
 
 			case T_AlterForeignServerStmt:
-				AlterForeignServer((AlterForeignServerStmt *) parsetree);
+				address = AlterForeignServer((AlterForeignServerStmt *) parsetree);
 				break;
 
 			case T_CreateUserMappingStmt:
-				CreateUserMapping((CreateUserMappingStmt *) parsetree);
+				address = CreateUserMapping((CreateUserMappingStmt *) parsetree);
 				break;
 
 			case T_AlterUserMappingStmt:
-				AlterUserMapping((AlterUserMappingStmt *) parsetree);
+				address = AlterUserMapping((AlterUserMappingStmt *) parsetree);
 				break;
 
 			case T_DropUserMappingStmt:
 				RemoveUserMapping((DropUserMappingStmt *) parsetree);
+				/* no commands stashed for DROP */
+				commandCollected = true;
 				break;
 
 			case T_ImportForeignSchemaStmt:
 				ImportForeignSchema((ImportForeignSchemaStmt *) parsetree);
+				/* commands are stashed inside ImportForeignSchema */
+				commandCollected = true;
 				break;
 
 			case T_CompositeTypeStmt:	/* CREATE TYPE (composite) */
 				{
 					CompositeTypeStmt *stmt = (CompositeTypeStmt *) parsetree;
 
-					DefineCompositeType(stmt->typevar, stmt->coldeflist);
+					address = DefineCompositeType(stmt->typevar,
+												  stmt->coldeflist);
 				}
 				break;
 
 			case T_CreateEnumStmt:		/* CREATE TYPE AS ENUM */
-				DefineEnum((CreateEnumStmt *) parsetree);
+				address = DefineEnum((CreateEnumStmt *) parsetree);
 				break;
 
 			case T_CreateRangeStmt:		/* CREATE TYPE AS RANGE */
-				DefineRange((CreateRangeStmt *) parsetree);
+				address = DefineRange((CreateRangeStmt *) parsetree);
 				break;
 
 			case T_AlterEnumStmt:		/* ALTER TYPE (enum) */
-				AlterEnum((AlterEnumStmt *) parsetree, isTopLevel);
+				address = AlterEnum((AlterEnumStmt *) parsetree, isTopLevel);
 				break;
 
 			case T_ViewStmt:	/* CREATE VIEW */
-				DefineView((ViewStmt *) parsetree, queryString);
+				EventTriggerAlterTableStart(parsetree);
+				address = DefineView((ViewStmt *) parsetree, queryString);
+				EventTriggerCollectSimpleCommand(address, secondaryObject,
+												 parsetree);
+				/* stashed internally */
+				commandCollected = true;
+				EventTriggerAlterTableEnd();
 				break;
 
 			case T_CreateFunctionStmt:	/* CREATE FUNCTION */
-				CreateFunction((CreateFunctionStmt *) parsetree, queryString);
+				address = CreateFunction((CreateFunctionStmt *) parsetree, queryString);
 				break;
 
 			case T_AlterFunctionStmt:	/* ALTER FUNCTION */
-				AlterFunction((AlterFunctionStmt *) parsetree);
+				address = AlterFunction((AlterFunctionStmt *) parsetree);
 				break;
 
 			case T_RuleStmt:	/* CREATE RULE */
-				DefineRule((RuleStmt *) parsetree, queryString);
+				address = DefineRule((RuleStmt *) parsetree, queryString);
 				break;
 
 			case T_CreateSeqStmt:
-				DefineSequence((CreateSeqStmt *) parsetree);
+				address = DefineSequence((CreateSeqStmt *) parsetree);
 				break;
 
 			case T_AlterSeqStmt:
-				AlterSequence((AlterSeqStmt *) parsetree);
+				address = AlterSequence((AlterSeqStmt *) parsetree);
 				break;
 
 			case T_CreateTableAsStmt:
-				ExecCreateTableAs((CreateTableAsStmt *) parsetree,
+				address = ExecCreateTableAs((CreateTableAsStmt *) parsetree,
 								  queryString, params, completionTag);
 				break;
 
 			case T_RefreshMatViewStmt:
-				ExecRefreshMatView((RefreshMatViewStmt *) parsetree,
-								   queryString, params, completionTag);
+				/*
+				 * REFRSH CONCURRENTLY executes some DDL commands internally.
+				 * Inhibit DDL command collection here to avoid those commands
+				 * from showing up in the deparsed command queue.  The refresh
+				 * command itself is queued, which is enough.
+				 */
+				EventTriggerInhibitCommandCollection();
+				PG_TRY();
+				{
+					address = ExecRefreshMatView((RefreshMatViewStmt *) parsetree,
+												 queryString, params, completionTag);
+				}
+				PG_CATCH();
+				{
+					EventTriggerUndoInhibitCommandCollection();
+					PG_RE_THROW();
+				}
+				PG_END_TRY();
+				EventTriggerUndoInhibitCommandCollection();
 				break;
 
 			case T_CreateTrigStmt:
-				(void) CreateTrigger((CreateTrigStmt *) parsetree, queryString,
-									 InvalidOid, InvalidOid, InvalidOid,
-									 InvalidOid, false);
+				address = CreateTrigger((CreateTrigStmt *) parsetree,
+										 queryString, InvalidOid, InvalidOid,
+										 InvalidOid, InvalidOid, false);
 				break;
 
 			case T_CreatePLangStmt:
-				CreateProceduralLanguage((CreatePLangStmt *) parsetree);
+				address = CreateProceduralLanguage((CreatePLangStmt *) parsetree);
 				break;
 
 			case T_CreateDomainStmt:
-				DefineDomain((CreateDomainStmt *) parsetree);
+				address = DefineDomain((CreateDomainStmt *) parsetree);
 				break;
 
 			case T_CreateConversionStmt:
-				CreateConversionCommand((CreateConversionStmt *) parsetree);
+				address = CreateConversionCommand((CreateConversionStmt *) parsetree);
 				break;
 
 			case T_CreateCastStmt:
-				CreateCast((CreateCastStmt *) parsetree);
+				address = CreateCast((CreateCastStmt *) parsetree);
 				break;
 
 			case T_CreateOpClassStmt:
 				DefineOpClass((CreateOpClassStmt *) parsetree);
+				/* command is stashed in DefineOpClass */
+				commandCollected = true;
 				break;
 
 			case T_CreateOpFamilyStmt:
-				DefineOpFamily((CreateOpFamilyStmt *) parsetree);
+				address = DefineOpFamily((CreateOpFamilyStmt *) parsetree);
 				break;
 
 			case T_CreateTransformStmt:
@@ -1345,63 +1440,76 @@ ProcessUtilitySlow(Node *parsetree,
 
 			case T_AlterOpFamilyStmt:
 				AlterOpFamily((AlterOpFamilyStmt *) parsetree);
+				/* commands are stashed in AlterOpFamily */
+				commandCollected = true;
 				break;
 
 			case T_AlterTSDictionaryStmt:
-				AlterTSDictionary((AlterTSDictionaryStmt *) parsetree);
+				address = AlterTSDictionary((AlterTSDictionaryStmt *) parsetree);
 				break;
 
 			case T_AlterTSConfigurationStmt:
-				AlterTSConfiguration((AlterTSConfigurationStmt *) parsetree);
+				address = AlterTSConfiguration((AlterTSConfigurationStmt *) parsetree);
 				break;
 
 			case T_AlterTableMoveAllStmt:
 				AlterTableMoveAll((AlterTableMoveAllStmt *) parsetree);
+				/* commands are stashed in AlterTableMoveAll */
+				commandCollected = true;
 				break;
 
 			case T_DropStmt:
 				ExecDropStmt((DropStmt *) parsetree, isTopLevel);
+				/* no commands stashed for DROP */
+				commandCollected = true;
 				break;
 
 			case T_RenameStmt:
-				ExecRenameStmt((RenameStmt *) parsetree);
+				address = ExecRenameStmt((RenameStmt *) parsetree);
 				break;
 
 			case T_AlterObjectSchemaStmt:
-				ExecAlterObjectSchemaStmt((AlterObjectSchemaStmt *) parsetree,
-										  NULL);
+				address =
+					ExecAlterObjectSchemaStmt((AlterObjectSchemaStmt *) parsetree,
+											  &secondaryObject);
 				break;
 
 			case T_AlterOwnerStmt:
-				ExecAlterOwnerStmt((AlterOwnerStmt *) parsetree);
+				address = ExecAlterOwnerStmt((AlterOwnerStmt *) parsetree);
 				break;
 
 			case T_CommentStmt:
-				CommentObject((CommentStmt *) parsetree);
+				address = CommentObject((CommentStmt *) parsetree);
 				break;
 
 			case T_GrantStmt:
 				ExecuteGrantStmt((GrantStmt *) parsetree);
+				/* commands are stashed in ExecGrantStmt_oids */
+				commandCollected = true;
 				break;
 
 			case T_DropOwnedStmt:
 				DropOwnedObjects((DropOwnedStmt *) parsetree);
+				/* no commands stashed for DROP */
+				commandCollected = true;
 				break;
 
 			case T_AlterDefaultPrivilegesStmt:
 				ExecAlterDefaultPrivilegesStmt((AlterDefaultPrivilegesStmt *) parsetree);
+				EventTriggerCollectAlterDefPrivs((AlterDefaultPrivilegesStmt *) parsetree);
+				commandCollected = true;
 				break;
 
 			case T_CreatePolicyStmt:	/* CREATE POLICY */
-				CreatePolicy((CreatePolicyStmt *) parsetree);
+				address = CreatePolicy((CreatePolicyStmt *) parsetree);
 				break;
 
 			case T_AlterPolicyStmt:		/* ALTER POLICY */
-				AlterPolicy((AlterPolicyStmt *) parsetree);
+				address = AlterPolicy((AlterPolicyStmt *) parsetree);
 				break;
 
 			case T_SecLabelStmt:
-				ExecSecLabelStmt((SecLabelStmt *) parsetree);
+				address = ExecSecLabelStmt((SecLabelStmt *) parsetree);
 				break;
 
 			default:
@@ -1410,6 +1518,14 @@ ProcessUtilitySlow(Node *parsetree,
 				break;
 		}
 
+		/*
+		 * Remember the object so that ddl_command_end event triggers have
+		 * access to it.
+		 */
+		if (!commandCollected)
+			EventTriggerCollectSimpleCommand(address, secondaryObject,
+											 parsetree);
+
 		if (isCompleteQuery)
 		{
 			EventTriggerSQLDrop(parsetree);