PATCH: Add REINDEX tag to event triggers

From ff0488f43db0febc1bcefb5236bbd79db64a506c Mon Sep 17 00:00:00 2001
From: Garrett Thornburg <garrett.thornburg@hey.com>
Date: Thu, 20 Jul 2023 20:35:26 -0600
Subject: [PATCH v2] Add REINDEX tag to event triggers

This turns on the event tag REINDEX. This will now return a record for
each index that was modified during as part of start/end ddl commands.

For concurrent reindex, this will return the OID for the new index. This
includes concurrently reindexing tables and databases. It will only
report the new index records and not the ones destroyed by the
concurrent reindex process.
---
 doc/src/sgml/event-trigger.sgml             |   8 ++
 src/backend/commands/indexcmds.c            |  83 +++++++++++++
 src/backend/tcop/utility.c                  |  12 +-
 src/include/tcop/cmdtaglist.h               |   2 +-
 src/test/regress/expected/event_trigger.out | 126 ++++++++++++++++++++
 src/test/regress/sql/event_trigger.sql      |  99 +++++++++++++++
 6 files changed, 327 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 3b6a5361b3..aa1b359f35 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1013,6 +1013,14 @@
         <entry align="center"><literal>-</literal></entry>
         <entry align="left"></entry>
        </row>
+       <row>
+        <entry align="left"><literal>REINDEX</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="left"></entry>
+       </row>
        <row>
         <entry align="left"><literal>REVOKE</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index baf3e6e57a..2dc74225ed 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -94,6 +94,8 @@ static char *ChooseIndexNameAddition(List *colnames);
 static List *ChooseIndexColumnNames(List *indexElems);
 static void ReindexIndex(RangeVar *indexRelation, ReindexParams *params,
 						 bool isTopLevel);
+static void reindex_event_trigger_collect(Oid oid);
+static void reindex_event_trigger_collect_relation(Oid oid);
 static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
 											Oid relId, Oid oldRelId, void *arg);
 static Oid	ReindexTable(RangeVar *relation, ReindexParams *params,
@@ -110,6 +112,8 @@ static bool ReindexRelationConcurrently(Oid relationOid,
 static void update_relispartition(Oid relationId, bool newval);
 static inline void set_indexsafe_procflags(void);
 
+static ReindexStmt *currentReindexStatement = NULL;
+
 /*
  * callback argument type for RangeVarCallbackForReindexIndex()
  */
@@ -2699,6 +2703,12 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
 
+	/*
+	 * Make current stmt available for event triggers without directly passing
+	 * the context to every subsequent call.
+	 */
+	currentReindexStatement = stmt;
+
 	/* Parse option list */
 	foreach(lc, stmt->params)
 	{
@@ -2779,6 +2789,12 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 				 (int) stmt->kind);
 			break;
 	}
+
+	/*
+	 * Clear the reindex stmt global reference now that triggers should have
+	 * completed
+	 */
+	currentReindexStatement = NULL;
 }
 
 /*
@@ -2830,6 +2846,8 @@ ReindexIndex(RangeVar *indexRelation, ReindexParams *params, bool isTopLevel)
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
 		reindex_index(indOid, false, persistence, &newparams);
+		/* Add the index to event trigger */
+		reindex_event_trigger_collect(indOid);
 	}
 }
 
@@ -2953,6 +2971,9 @@ ReindexTable(RangeVar *relation, ReindexParams *params, bool isTopLevel)
 			ereport(NOTICE,
 					(errmsg("table \"%s\" has no indexes to reindex",
 							relation->relname)));
+
+		/* Create even for the indexes being modified */
+		reindex_event_trigger_collect_relation(heapOid);
 	}
 
 	return heapOid;
@@ -3369,6 +3390,8 @@ ReindexMultipleInternal(List *relids, ReindexParams *params)
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
 			reindex_index(relid, false, relpersistence, &newparams);
+			/* Add the index to event trigger */
+			reindex_event_trigger_collect(relid);
 			PopActiveSnapshot();
 			/* reindex_index() does the verbose output */
 		}
@@ -3390,6 +3413,9 @@ ReindexMultipleInternal(List *relids, ReindexParams *params)
 								get_namespace_name(get_rel_namespace(relid)),
 								get_rel_name(relid))));
 
+			/* Create even for the indexes being modified */
+			reindex_event_trigger_collect_relation(relid);
+
 			PopActiveSnapshot();
 		}
 
@@ -3837,6 +3863,9 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 
 		newIndexIds = lappend(newIndexIds, newidx);
 
+		/* Add the index to event trigger */
+		reindex_event_trigger_collect(newIndexId);
+
 		/*
 		 * Save lockrelid to protect each relation from drop then close
 		 * relations. The lockrelid on parent relation is not taken here to
@@ -4441,3 +4470,57 @@ set_indexsafe_procflags(void)
 	ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
 	LWLockRelease(ProcArrayLock);
 }
+
+static void
+reindex_event_trigger_collect(Oid oid)
+{
+	ObjectAddress address;
+
+	if (currentReindexStatement == NULL)
+		return;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) currentReindexStatement);
+}
+
+static void
+reindex_event_trigger_collect_relation(Oid relid)
+{
+	Relation	rel;
+	List	   *indexIds = NULL;
+	ListCell   *indexId = NULL;
+
+	/*
+	 * Open and lock the relation.  ShareLock is sufficient since we only need
+	 * to prevent schema and data changes in it.  The lock level used here
+	 * should match catalog's reindex_relation().
+	 */
+	rel = try_table_open(relid, ShareLock);
+
+	/* if relation is gone, leave */
+	if (!rel)
+		return;
+
+	/*
+	 * Get the list of index OIDs for this relation.  (We trust to the
+	 * relcache to get this with a sequential scan if ignoring system
+	 * indexes.)
+	 */
+	indexIds = RelationGetIndexList(rel);
+
+	/*
+	 * Get the list of index OIDs for this relation.
+	 */
+	foreach(indexId, indexIds)
+	{
+		Oid			indexOid = lfirst_oid(indexId);
+
+		reindex_event_trigger_collect(indexOid);
+	}
+
+	table_close(rel, ShareLock);
+}
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7f7..a269b5b6dc 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -961,7 +961,9 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_ReindexStmt:
-			ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+			ProcessUtilitySlow(pstate, pstmt, queryString,
+							   context, params, queryEnv,
+							   dest, qc);
 			break;
 
 			/*
@@ -1574,6 +1576,12 @@ ProcessUtilitySlow(ParseState *pstate,
 				}
 				break;
 
+			case T_ReindexStmt:
+				ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+				/* no commands stashed for REINDEX */
+				commandCollected = true;
+				break;
+
 			case T_CreateExtensionStmt:
 				address = CreateExtension(pstate, (CreateExtensionStmt *) parsetree);
 				break;
@@ -3620,7 +3628,7 @@ GetCommandLogLevel(Node *parsetree)
 			break;
 
 		case T_ReindexStmt:
-			lev = LOGSTMT_ALL;	/* should this be DDL? */
+			lev = LOGSTMT_DDL;
 			break;
 
 		case T_CreateConversionStmt:
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index e738ac1c09..d0e0b18609 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -193,7 +193,7 @@ PG_CMDTAG(CMDTAG_PREPARE, "PREPARE", false, false, false)
 PG_CMDTAG(CMDTAG_PREPARE_TRANSACTION, "PREPARE TRANSACTION", false, false, false)
 PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
 PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false)
-PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", false, false, false)
+PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false)
 PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
 PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false)
 PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false)
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 5a10958df5..f9bad789c6 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -554,6 +554,132 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+-- test Reindex Event Trigger
+CREATE OR REPLACE FUNCTION reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+  RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_start_command();
+CREATE OR REPLACE FUNCTION reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+  obj record;
+BEGIN
+  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+      RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_command();
+-- test REINDEX event for a standard table
+DROP SCHEMA IF EXISTS reindex_test CASCADE;
+NOTICE:  schema "reindex_test" does not exist, skipping
+CREATE SCHEMA reindex_test;
+CREATE TABLE reindex_test.reindex_tester1 (a int);
+CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 (a);
+CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 (a);
+CREATE TABLE reindex_test.reindex_tester2 (a int);
+CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 (a);
+REINDEX INDEX reindex_test.reindex_test1_idx1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 USING btree (a)
+REINDEX TABLE reindex_test.reindex_tester1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 USING btree (a)
+REINDEX INDEX CONCURRENTLY reindex_test.reindex_test2_idx1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 USING btree (a)
+REINDEX TABLE CONCURRENTLY reindex_test.reindex_tester2;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 USING btree (a)
+-- test REINDEX event for a partitioned table
+CREATE TABLE reindex_test.ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE reindex_test.ptif_test0 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test01 PARTITION OF reindex_test.ptif_test0 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test1 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test11 PARTITION OF reindex_test.ptif_test1 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test2 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (100) TO (200);
+-- This partitioned table should remain with no partitions.
+CREATE TABLE reindex_test.ptif_test3 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+CREATE INDEX ptif_test_index ON ONLY reindex_test.ptif_test (a);
+CREATE INDEX ptif_test0_index ON ONLY reindex_test.ptif_test0 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test0_index;
+CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 (a);
+ALTER INDEX reindex_test.ptif_test0_index ATTACH PARTITION reindex_test.ptif_test01_index;
+CREATE INDEX ptif_test1_index ON ONLY reindex_test.ptif_test1 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test1_index;
+CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 (a);
+ALTER INDEX reindex_test.ptif_test1_index ATTACH PARTITION reindex_test.ptif_test11_index;
+CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test2_index;
+CREATE INDEX ptif_test3_index ON reindex_test.ptif_test3 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test3_index;
+-- reindex at the top level table to recursively reindex each partition
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- ptif_test0 is partitioned table so it will index partition: ptif_test01_index
+-- event trigger will log ptif_test01_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test0_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- ptif_test1_index is partitioned index so it will index partition: ptif_test11_index
+-- event trigger will effect on partion index:ptif_test11_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test1_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- ptif_test2 is a partition so event trigger will log ptif_test2_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test2_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+-- no partitions: event trigger won't do anything
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test3_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+-- reindex at the top level table to recursively reindex each partition
+REINDEX TABLE reindex_test.ptif_test;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- will direct to ptif_test01
+REINDEX TABLE reindex_test.ptif_test0;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test01;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test11;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test2;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+-- no partion, index won't do anything
+REINDEX TABLE reindex_test.ptif_test3;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+DROP EVENT TRIGGER start_reindex_command;
+DROP EVENT TRIGGER end_reindex_command;
+DROP SCHEMA reindex_test CASCADE;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table reindex_test.reindex_tester1
+drop cascades to table reindex_test.reindex_tester2
+drop cascades to table reindex_test.ptif_test
+DROP FUNCTION reindex_end_command;
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 1aeaddbe71..d5d51bd9db 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -418,6 +418,105 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+-- test Reindex Event Trigger
+CREATE OR REPLACE FUNCTION reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+  RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_start_command();
+
+CREATE OR REPLACE FUNCTION reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+  obj record;
+BEGIN
+  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+      RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_command();
+
+-- test REINDEX event for a standard table
+DROP SCHEMA IF EXISTS reindex_test CASCADE;
+CREATE SCHEMA reindex_test;
+CREATE TABLE reindex_test.reindex_tester1 (a int);
+CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 (a);
+CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 (a);
+CREATE TABLE reindex_test.reindex_tester2 (a int);
+CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 (a);
+
+REINDEX INDEX reindex_test.reindex_test1_idx1;
+REINDEX TABLE reindex_test.reindex_tester1;
+REINDEX INDEX CONCURRENTLY reindex_test.reindex_test2_idx1;
+REINDEX TABLE CONCURRENTLY reindex_test.reindex_tester2;
+
+-- test REINDEX event for a partitioned table
+CREATE TABLE reindex_test.ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE reindex_test.ptif_test0 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test01 PARTITION OF reindex_test.ptif_test0 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test1 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test11 PARTITION OF reindex_test.ptif_test1 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test2 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (100) TO (200);
+-- This partitioned table should remain with no partitions.
+CREATE TABLE reindex_test.ptif_test3 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+
+CREATE INDEX ptif_test_index ON ONLY reindex_test.ptif_test (a);
+CREATE INDEX ptif_test0_index ON ONLY reindex_test.ptif_test0 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test0_index;
+CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 (a);
+ALTER INDEX reindex_test.ptif_test0_index ATTACH PARTITION reindex_test.ptif_test01_index;
+CREATE INDEX ptif_test1_index ON ONLY reindex_test.ptif_test1 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test1_index;
+CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 (a);
+ALTER INDEX reindex_test.ptif_test1_index ATTACH PARTITION reindex_test.ptif_test11_index;
+CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test2_index;
+CREATE INDEX ptif_test3_index ON reindex_test.ptif_test3 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test3_index;
+
+-- reindex at the top level table to recursively reindex each partition
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test_index;
+-- ptif_test0 is partitioned table so it will index partition: ptif_test01_index
+-- event trigger will log ptif_test01_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test0_index;
+-- ptif_test1_index is partitioned index so it will index partition: ptif_test11_index
+-- event trigger will effect on partion index:ptif_test11_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test1_index;
+-- ptif_test2 is a partition so event trigger will log ptif_test2_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test2_index;
+-- no partitions: event trigger won't do anything
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test3_index;
+
+-- reindex at the top level table to recursively reindex each partition
+REINDEX TABLE reindex_test.ptif_test;
+-- will direct to ptif_test01
+REINDEX TABLE reindex_test.ptif_test0;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test01;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test11;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test2;
+-- no partion, index won't do anything
+REINDEX TABLE reindex_test.ptif_test3;
+
+DROP EVENT TRIGGER start_reindex_command;
+DROP EVENT TRIGGER end_reindex_command;
+DROP SCHEMA reindex_test CASCADE;
+DROP FUNCTION reindex_end_command;
+
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
-- 
2.38.1

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 3b6a5361..aa1b359f 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1013,6 +1013,14 @@
         <entry align="center"><literal>-</literal></entry>
         <entry align="left"></entry>
        </row>
+       <row>
+        <entry align="left"><literal>REINDEX</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="left"></entry>
+       </row>
        <row>
         <entry align="left"><literal>REVOKE</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index ab8b81b3..b78f1d87 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -95,6 +95,8 @@ static char *ChooseIndexNameAddition(const List *colnames);
 static List *ChooseIndexColumnNames(const List *indexElems);
 static void ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params,
 						 bool isTopLevel);
+static void reindex_event_trigger_collect(Oid oid);
+static void reindex_event_trigger_collect_relation(Oid oid);
 static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
 											Oid relId, Oid oldRelId, void *arg);
 static Oid	ReindexTable(const RangeVar *relation, const ReindexParams *params,
@@ -111,6 +113,8 @@ static bool ReindexRelationConcurrently(Oid relationOid,
 static void update_relispartition(Oid relationId, bool newval);
 static inline void set_indexsafe_procflags(void);
 
+static ReindexStmt *currentReindexStatement = NULL;
+
 /*
  * callback argument type for RangeVarCallbackForReindexIndex()
  */
@@ -2696,6 +2700,12 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
 
+	/*
+	 * Make current stmt available for event triggers without directly passing
+	 * the context to every subsequent call.
+	 */
+	currentReindexStatement = stmt;
+
 	/* Parse option list */
 	foreach(lc, stmt->params)
 	{
@@ -2776,6 +2786,12 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 				 (int) stmt->kind);
 			break;
 	}
+
+	/*
+	 * Clear the reindex stmt global reference now that triggers should have
+	 * completed
+	 */
+	currentReindexStatement = NULL;
 }
 
 /*
@@ -2827,6 +2843,8 @@ ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool is
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
 		reindex_index(indOid, false, persistence, &newparams);
+		/* Add the index to event trigger */
+		reindex_event_trigger_collect(indOid);
 	}
 }
 
@@ -2950,6 +2968,9 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 			ereport(NOTICE,
 					(errmsg("table \"%s\" has no indexes to reindex",
 							relation->relname)));
+
+		/* Create even for the indexes being modified */
+		reindex_event_trigger_collect_relation(heapOid);
 	}
 
 	return heapOid;
@@ -3366,6 +3387,8 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
 			reindex_index(relid, false, relpersistence, &newparams);
+			/* Add the index to event trigger */
+			reindex_event_trigger_collect(relid);
 			PopActiveSnapshot();
 			/* reindex_index() does the verbose output */
 		}
@@ -3387,6 +3410,9 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 								get_namespace_name(get_rel_namespace(relid)),
 								get_rel_name(relid))));
 
+			/* Create even for the indexes being modified */
+			reindex_event_trigger_collect_relation(relid);
+
 			PopActiveSnapshot();
 		}
 
@@ -3833,6 +3859,9 @@ ReindexRelationConcurrently(Oid relationOid, const ReindexParams *params)
 
 		newIndexIds = lappend(newIndexIds, newidx);
 
+		/* Add the index to event trigger */
+		reindex_event_trigger_collect(newIndexId);
+
 		/*
 		 * Save lockrelid to protect each relation from drop then close
 		 * relations. The lockrelid on parent relation is not taken here to
@@ -4436,3 +4465,57 @@ set_indexsafe_procflags(void)
 	ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
 	LWLockRelease(ProcArrayLock);
 }
+
+static void
+reindex_event_trigger_collect(Oid oid)
+{
+	ObjectAddress address;
+
+	if (currentReindexStatement == NULL)
+		return;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) currentReindexStatement);
+}
+
+static void
+reindex_event_trigger_collect_relation(Oid relid)
+{
+	Relation	rel;
+	List	   *indexIds = NULL;
+	ListCell   *indexId = NULL;
+
+	/*
+	 * Open and lock the relation.  ShareLock is sufficient since we only need
+	 * to prevent schema and data changes in it.  The lock level used here
+	 * should match catalog's reindex_relation().
+	 */
+	rel = try_table_open(relid, ShareLock);
+
+	/* if relation is gone, leave */
+	if (!rel)
+		return;
+
+	/*
+	 * Get the list of index OIDs for this relation.  (We trust to the
+	 * relcache to get this with a sequential scan if ignoring system
+	 * indexes.)
+	 */
+	indexIds = RelationGetIndexList(rel);
+
+	/*
+	 * Get the list of index OIDs for this relation.
+	 */
+	foreach(indexId, indexIds)
+	{
+		Oid			indexOid = lfirst_oid(indexId);
+
+		reindex_event_trigger_collect(indexOid);
+	}
+
+	table_close(rel, ShareLock);
+}
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7..a269b5b6 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -961,7 +961,9 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_ReindexStmt:
-			ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+			ProcessUtilitySlow(pstate, pstmt, queryString,
+							   context, params, queryEnv,
+							   dest, qc);
 			break;
 
 			/*
@@ -1574,6 +1576,12 @@ ProcessUtilitySlow(ParseState *pstate,
 				}
 				break;
 
+			case T_ReindexStmt:
+				ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+				/* no commands stashed for REINDEX */
+				commandCollected = true;
+				break;
+
 			case T_CreateExtensionStmt:
 				address = CreateExtension(pstate, (CreateExtensionStmt *) parsetree);
 				break;
@@ -3620,7 +3628,7 @@ GetCommandLogLevel(Node *parsetree)
 			break;
 
 		case T_ReindexStmt:
-			lev = LOGSTMT_ALL;	/* should this be DDL? */
+			lev = LOGSTMT_DDL;
 			break;
 
 		case T_CreateConversionStmt:
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index e738ac1c..d0e0b186 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -193,7 +193,7 @@ PG_CMDTAG(CMDTAG_PREPARE, "PREPARE", false, false, false)
 PG_CMDTAG(CMDTAG_PREPARE_TRANSACTION, "PREPARE TRANSACTION", false, false, false)
 PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
 PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false)
-PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", false, false, false)
+PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false)
 PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
 PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false)
 PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false)
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 2c8a6b22..874fba2a 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -556,6 +556,132 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+-- test Reindex Event Trigger
+CREATE OR REPLACE FUNCTION reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+  RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_start_command();
+CREATE OR REPLACE FUNCTION reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+  obj record;
+BEGIN
+  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+      RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_command();
+-- test REINDEX event for a standard table
+DROP SCHEMA IF EXISTS reindex_test CASCADE;
+NOTICE:  schema "reindex_test" does not exist, skipping
+CREATE SCHEMA reindex_test;
+CREATE TABLE reindex_test.reindex_tester1 (a int);
+CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 (a);
+CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 (a);
+CREATE TABLE reindex_test.reindex_tester2 (a int);
+CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 (a);
+REINDEX INDEX reindex_test.reindex_test1_idx1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 USING btree (a)
+REINDEX TABLE reindex_test.reindex_tester1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 USING btree (a)
+REINDEX INDEX CONCURRENTLY reindex_test.reindex_test2_idx1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 USING btree (a)
+REINDEX TABLE CONCURRENTLY reindex_test.reindex_tester2;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 USING btree (a)
+-- test REINDEX event for a partitioned table
+CREATE TABLE reindex_test.ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE reindex_test.ptif_test0 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test01 PARTITION OF reindex_test.ptif_test0 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test1 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test11 PARTITION OF reindex_test.ptif_test1 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test2 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (100) TO (200);
+-- This partitioned table should remain with no partitions.
+CREATE TABLE reindex_test.ptif_test3 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+CREATE INDEX ptif_test_index ON ONLY reindex_test.ptif_test (a);
+CREATE INDEX ptif_test0_index ON ONLY reindex_test.ptif_test0 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test0_index;
+CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 (a);
+ALTER INDEX reindex_test.ptif_test0_index ATTACH PARTITION reindex_test.ptif_test01_index;
+CREATE INDEX ptif_test1_index ON ONLY reindex_test.ptif_test1 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test1_index;
+CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 (a);
+ALTER INDEX reindex_test.ptif_test1_index ATTACH PARTITION reindex_test.ptif_test11_index;
+CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test2_index;
+CREATE INDEX ptif_test3_index ON reindex_test.ptif_test3 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test3_index;
+-- reindex at the top level table to recursively reindex each partition
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- ptif_test0 is partitioned table so it will index partition: ptif_test01_index
+-- event trigger will log ptif_test01_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test0_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- ptif_test1_index is partitioned index so it will index partition: ptif_test11_index
+-- event trigger will effect on partion index:ptif_test11_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test1_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- ptif_test2 is a partition so event trigger will log ptif_test2_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test2_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+-- no partitions: event trigger won't do anything
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test3_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+-- reindex at the top level table to recursively reindex each partition
+REINDEX TABLE reindex_test.ptif_test;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- will direct to ptif_test01
+REINDEX TABLE reindex_test.ptif_test0;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test01;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test11;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test2;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+-- no partion, index won't do anything
+REINDEX TABLE reindex_test.ptif_test3;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+DROP EVENT TRIGGER start_reindex_command;
+DROP EVENT TRIGGER end_reindex_command;
+DROP SCHEMA reindex_test CASCADE;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table reindex_test.reindex_tester1
+drop cascades to table reindex_test.reindex_tester2
+drop cascades to table reindex_test.ptif_test
+DROP FUNCTION reindex_end_command;
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 1aeaddbe..d5d51bd9 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -418,6 +418,105 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+-- test Reindex Event Trigger
+CREATE OR REPLACE FUNCTION reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+  RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_start_command();
+
+CREATE OR REPLACE FUNCTION reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+  obj record;
+BEGIN
+  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+      RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_command();
+
+-- test REINDEX event for a standard table
+DROP SCHEMA IF EXISTS reindex_test CASCADE;
+CREATE SCHEMA reindex_test;
+CREATE TABLE reindex_test.reindex_tester1 (a int);
+CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 (a);
+CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 (a);
+CREATE TABLE reindex_test.reindex_tester2 (a int);
+CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 (a);
+
+REINDEX INDEX reindex_test.reindex_test1_idx1;
+REINDEX TABLE reindex_test.reindex_tester1;
+REINDEX INDEX CONCURRENTLY reindex_test.reindex_test2_idx1;
+REINDEX TABLE CONCURRENTLY reindex_test.reindex_tester2;
+
+-- test REINDEX event for a partitioned table
+CREATE TABLE reindex_test.ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE reindex_test.ptif_test0 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test01 PARTITION OF reindex_test.ptif_test0 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test1 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test11 PARTITION OF reindex_test.ptif_test1 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test2 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (100) TO (200);
+-- This partitioned table should remain with no partitions.
+CREATE TABLE reindex_test.ptif_test3 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+
+CREATE INDEX ptif_test_index ON ONLY reindex_test.ptif_test (a);
+CREATE INDEX ptif_test0_index ON ONLY reindex_test.ptif_test0 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test0_index;
+CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 (a);
+ALTER INDEX reindex_test.ptif_test0_index ATTACH PARTITION reindex_test.ptif_test01_index;
+CREATE INDEX ptif_test1_index ON ONLY reindex_test.ptif_test1 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test1_index;
+CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 (a);
+ALTER INDEX reindex_test.ptif_test1_index ATTACH PARTITION reindex_test.ptif_test11_index;
+CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test2_index;
+CREATE INDEX ptif_test3_index ON reindex_test.ptif_test3 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test3_index;
+
+-- reindex at the top level table to recursively reindex each partition
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test_index;
+-- ptif_test0 is partitioned table so it will index partition: ptif_test01_index
+-- event trigger will log ptif_test01_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test0_index;
+-- ptif_test1_index is partitioned index so it will index partition: ptif_test11_index
+-- event trigger will effect on partion index:ptif_test11_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test1_index;
+-- ptif_test2 is a partition so event trigger will log ptif_test2_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test2_index;
+-- no partitions: event trigger won't do anything
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test3_index;
+
+-- reindex at the top level table to recursively reindex each partition
+REINDEX TABLE reindex_test.ptif_test;
+-- will direct to ptif_test01
+REINDEX TABLE reindex_test.ptif_test0;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test01;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test11;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test2;
+-- no partion, index won't do anything
+REINDEX TABLE reindex_test.ptif_test3;
+
+DROP EVENT TRIGGER start_reindex_command;
+DROP EVENT TRIGGER end_reindex_command;
+DROP SCHEMA reindex_test CASCADE;
+DROP FUNCTION reindex_end_command;
+
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);

From a3b6775362e07d53b367bcf303a3535d24f42cce Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Fri, 1 Sep 2023 23:51:46 +0800
Subject: [PATCH v3] Add REINDEX tag to event triggers
This turns on the event tag REINDEX. This will now
 return a record for each index that was modified during as part of start/end
 ddl commands.

For concurrent reindex, this will return the OID for the new index. This
includes concurrently reindexing tables and databases. It will only
report the new index records and not the ones destroyed by the
concurrent reindex process.

Author: Garrett Thornburg
---
 doc/src/sgml/event-trigger.sgml             |   8 ++
 src/backend/commands/indexcmds.c            |  83 +++++++++++++
 src/backend/tcop/utility.c                  |  12 +-
 src/include/tcop/cmdtaglist.h               |   2 +-
 src/test/regress/expected/event_trigger.out | 126 ++++++++++++++++++++
 src/test/regress/sql/event_trigger.sql      |  99 +++++++++++++++
 6 files changed, 327 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 3b6a5361..aa1b359f 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1013,6 +1013,14 @@
         <entry align="center"><literal>-</literal></entry>
         <entry align="left"></entry>
        </row>
+       <row>
+        <entry align="left"><literal>REINDEX</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="left"></entry>
+       </row>
        <row>
         <entry align="left"><literal>REVOKE</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index ab8b81b3..483b0665 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -95,6 +95,8 @@ static char *ChooseIndexNameAddition(const List *colnames);
 static List *ChooseIndexColumnNames(const List *indexElems);
 static void ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params,
 						 bool isTopLevel);
+static void reindex_event_trigger_collect(Oid oid);
+static void reindex_event_trigger_collect_relation(Oid oid);
 static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
 											Oid relId, Oid oldRelId, void *arg);
 static Oid	ReindexTable(const RangeVar *relation, const ReindexParams *params,
@@ -111,6 +113,8 @@ static bool ReindexRelationConcurrently(Oid relationOid,
 static void update_relispartition(Oid relationId, bool newval);
 static inline void set_indexsafe_procflags(void);
 
+static ReindexStmt *currentReindexStatement = NULL;
+
 /*
  * callback argument type for RangeVarCallbackForReindexIndex()
  */
@@ -2696,6 +2700,12 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
 
+	/*
+	 * Make current stmt available for event triggers without directly passing
+	 * the context to every subsequent call.
+	 */
+	currentReindexStatement = unconstify(ReindexStmt*, stmt);
+
 	/* Parse option list */
 	foreach(lc, stmt->params)
 	{
@@ -2776,6 +2786,12 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 				 (int) stmt->kind);
 			break;
 	}
+
+	/*
+	 * Clear the reindex stmt global reference now that triggers should have
+	 * completed
+	 */
+	currentReindexStatement = NULL;
 }
 
 /*
@@ -2827,6 +2843,8 @@ ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool is
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
 		reindex_index(indOid, false, persistence, &newparams);
+		/* Add the index to event trigger */
+		reindex_event_trigger_collect(indOid);
 	}
 }
 
@@ -2950,6 +2968,9 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 			ereport(NOTICE,
 					(errmsg("table \"%s\" has no indexes to reindex",
 							relation->relname)));
+
+		/* Create even for the indexes being modified */
+		reindex_event_trigger_collect_relation(heapOid);
 	}
 
 	return heapOid;
@@ -3366,6 +3387,8 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
 			reindex_index(relid, false, relpersistence, &newparams);
+			/* Add the index to event trigger */
+			reindex_event_trigger_collect(relid);
 			PopActiveSnapshot();
 			/* reindex_index() does the verbose output */
 		}
@@ -3387,6 +3410,9 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 								get_namespace_name(get_rel_namespace(relid)),
 								get_rel_name(relid))));
 
+			/* Create even for the indexes being modified */
+			reindex_event_trigger_collect_relation(relid);
+
 			PopActiveSnapshot();
 		}
 
@@ -3833,6 +3859,9 @@ ReindexRelationConcurrently(Oid relationOid, const ReindexParams *params)
 
 		newIndexIds = lappend(newIndexIds, newidx);
 
+		/* Add the index to event trigger */
+		reindex_event_trigger_collect(newIndexId);
+
 		/*
 		 * Save lockrelid to protect each relation from drop then close
 		 * relations. The lockrelid on parent relation is not taken here to
@@ -4436,3 +4465,57 @@ set_indexsafe_procflags(void)
 	ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
 	LWLockRelease(ProcArrayLock);
 }
+
+static void
+reindex_event_trigger_collect(Oid oid)
+{
+	ObjectAddress address;
+
+	if (currentReindexStatement == NULL)
+		return;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) currentReindexStatement);
+}
+
+static void
+reindex_event_trigger_collect_relation(Oid relid)
+{
+	Relation	rel;
+	List	   *indexIds = NULL;
+	ListCell   *indexId = NULL;
+
+	/*
+	 * Open and lock the relation.  ShareLock is sufficient since we only need
+	 * to prevent schema and data changes in it.  The lock level used here
+	 * should match catalog's reindex_relation().
+	 */
+	rel = try_table_open(relid, ShareLock);
+
+	/* if relation is gone, leave */
+	if (!rel)
+		return;
+
+	/*
+	 * Get the list of index OIDs for this relation.  (We trust to the
+	 * relcache to get this with a sequential scan if ignoring system
+	 * indexes.)
+	 */
+	indexIds = RelationGetIndexList(rel);
+
+	/*
+	 * Get the list of index OIDs for this relation.
+	 */
+	foreach(indexId, indexIds)
+	{
+		Oid			indexOid = lfirst_oid(indexId);
+
+		reindex_event_trigger_collect(indexOid);
+	}
+
+	table_close(rel, ShareLock);
+}
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7..a269b5b6 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -961,7 +961,9 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_ReindexStmt:
-			ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+			ProcessUtilitySlow(pstate, pstmt, queryString,
+							   context, params, queryEnv,
+							   dest, qc);
 			break;
 
 			/*
@@ -1574,6 +1576,12 @@ ProcessUtilitySlow(ParseState *pstate,
 				}
 				break;
 
+			case T_ReindexStmt:
+				ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+				/* no commands stashed for REINDEX */
+				commandCollected = true;
+				break;
+
 			case T_CreateExtensionStmt:
 				address = CreateExtension(pstate, (CreateExtensionStmt *) parsetree);
 				break;
@@ -3620,7 +3628,7 @@ GetCommandLogLevel(Node *parsetree)
 			break;
 
 		case T_ReindexStmt:
-			lev = LOGSTMT_ALL;	/* should this be DDL? */
+			lev = LOGSTMT_DDL;
 			break;
 
 		case T_CreateConversionStmt:
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index e738ac1c..d0e0b186 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -193,7 +193,7 @@ PG_CMDTAG(CMDTAG_PREPARE, "PREPARE", false, false, false)
 PG_CMDTAG(CMDTAG_PREPARE_TRANSACTION, "PREPARE TRANSACTION", false, false, false)
 PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
 PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false)
-PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", false, false, false)
+PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false)
 PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
 PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false)
 PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false)
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 2c8a6b22..874fba2a 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -556,6 +556,132 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+-- test Reindex Event Trigger
+CREATE OR REPLACE FUNCTION reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+  RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_start_command();
+CREATE OR REPLACE FUNCTION reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+  obj record;
+BEGIN
+  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+      RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_command();
+-- test REINDEX event for a standard table
+DROP SCHEMA IF EXISTS reindex_test CASCADE;
+NOTICE:  schema "reindex_test" does not exist, skipping
+CREATE SCHEMA reindex_test;
+CREATE TABLE reindex_test.reindex_tester1 (a int);
+CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 (a);
+CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 (a);
+CREATE TABLE reindex_test.reindex_tester2 (a int);
+CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 (a);
+REINDEX INDEX reindex_test.reindex_test1_idx1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 USING btree (a)
+REINDEX TABLE reindex_test.reindex_tester1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 USING btree (a)
+REINDEX INDEX CONCURRENTLY reindex_test.reindex_test2_idx1;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 USING btree (a)
+REINDEX TABLE CONCURRENTLY reindex_test.reindex_tester2;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 USING btree (a)
+-- test REINDEX event for a partitioned table
+CREATE TABLE reindex_test.ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE reindex_test.ptif_test0 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test01 PARTITION OF reindex_test.ptif_test0 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test1 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test11 PARTITION OF reindex_test.ptif_test1 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test2 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (100) TO (200);
+-- This partitioned table should remain with no partitions.
+CREATE TABLE reindex_test.ptif_test3 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+CREATE INDEX ptif_test_index ON ONLY reindex_test.ptif_test (a);
+CREATE INDEX ptif_test0_index ON ONLY reindex_test.ptif_test0 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test0_index;
+CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 (a);
+ALTER INDEX reindex_test.ptif_test0_index ATTACH PARTITION reindex_test.ptif_test01_index;
+CREATE INDEX ptif_test1_index ON ONLY reindex_test.ptif_test1 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test1_index;
+CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 (a);
+ALTER INDEX reindex_test.ptif_test1_index ATTACH PARTITION reindex_test.ptif_test11_index;
+CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test2_index;
+CREATE INDEX ptif_test3_index ON reindex_test.ptif_test3 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test3_index;
+-- reindex at the top level table to recursively reindex each partition
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- ptif_test0 is partitioned table so it will index partition: ptif_test01_index
+-- event trigger will log ptif_test01_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test0_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- ptif_test1_index is partitioned index so it will index partition: ptif_test11_index
+-- event trigger will effect on partion index:ptif_test11_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test1_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- ptif_test2 is a partition so event trigger will log ptif_test2_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test2_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+-- no partitions: event trigger won't do anything
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test3_index;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+-- reindex at the top level table to recursively reindex each partition
+REINDEX TABLE reindex_test.ptif_test;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- will direct to ptif_test01
+REINDEX TABLE reindex_test.ptif_test0;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test01;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test11;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 USING btree (a)
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test2;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 USING btree (a)
+-- no partion, index won't do anything
+REINDEX TABLE reindex_test.ptif_test3;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+DROP EVENT TRIGGER start_reindex_command;
+DROP EVENT TRIGGER end_reindex_command;
+DROP SCHEMA reindex_test CASCADE;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table reindex_test.reindex_tester1
+drop cascades to table reindex_test.reindex_tester2
+drop cascades to table reindex_test.ptif_test
+DROP FUNCTION reindex_end_command;
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 1aeaddbe..d5d51bd9 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -418,6 +418,105 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+-- test Reindex Event Trigger
+CREATE OR REPLACE FUNCTION reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+  RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_start_command();
+
+CREATE OR REPLACE FUNCTION reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+  obj record;
+BEGIN
+  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+      RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_command();
+
+-- test REINDEX event for a standard table
+DROP SCHEMA IF EXISTS reindex_test CASCADE;
+CREATE SCHEMA reindex_test;
+CREATE TABLE reindex_test.reindex_tester1 (a int);
+CREATE INDEX reindex_test1_idx1 ON reindex_test.reindex_tester1 (a);
+CREATE INDEX reindex_test1_idx2 ON reindex_test.reindex_tester1 (a);
+CREATE TABLE reindex_test.reindex_tester2 (a int);
+CREATE INDEX reindex_test2_idx1 ON reindex_test.reindex_tester2 (a);
+
+REINDEX INDEX reindex_test.reindex_test1_idx1;
+REINDEX TABLE reindex_test.reindex_tester1;
+REINDEX INDEX CONCURRENTLY reindex_test.reindex_test2_idx1;
+REINDEX TABLE CONCURRENTLY reindex_test.reindex_tester2;
+
+-- test REINDEX event for a partitioned table
+CREATE TABLE reindex_test.ptif_test (a int, b int) PARTITION BY range (a);
+CREATE TABLE reindex_test.ptif_test0 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (minvalue) TO (0) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test01 PARTITION OF reindex_test.ptif_test0 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test1 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (0) TO (100) PARTITION BY list (b);
+CREATE TABLE reindex_test.ptif_test11 PARTITION OF reindex_test.ptif_test1 FOR VALUES IN (1);
+CREATE TABLE reindex_test.ptif_test2 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (100) TO (200);
+-- This partitioned table should remain with no partitions.
+CREATE TABLE reindex_test.ptif_test3 PARTITION OF reindex_test.ptif_test
+  FOR VALUES FROM (200) TO (maxvalue) PARTITION BY list (b);
+
+CREATE INDEX ptif_test_index ON ONLY reindex_test.ptif_test (a);
+CREATE INDEX ptif_test0_index ON ONLY reindex_test.ptif_test0 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test0_index;
+CREATE INDEX ptif_test01_index ON reindex_test.ptif_test01 (a);
+ALTER INDEX reindex_test.ptif_test0_index ATTACH PARTITION reindex_test.ptif_test01_index;
+CREATE INDEX ptif_test1_index ON ONLY reindex_test.ptif_test1 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test1_index;
+CREATE INDEX ptif_test11_index ON reindex_test.ptif_test11 (a);
+ALTER INDEX reindex_test.ptif_test1_index ATTACH PARTITION reindex_test.ptif_test11_index;
+CREATE INDEX ptif_test2_index ON reindex_test.ptif_test2 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test2_index;
+CREATE INDEX ptif_test3_index ON reindex_test.ptif_test3 (a);
+ALTER INDEX reindex_test.ptif_test_index ATTACH PARTITION reindex_test.ptif_test3_index;
+
+-- reindex at the top level table to recursively reindex each partition
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test_index;
+-- ptif_test0 is partitioned table so it will index partition: ptif_test01_index
+-- event trigger will log ptif_test01_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test0_index;
+-- ptif_test1_index is partitioned index so it will index partition: ptif_test11_index
+-- event trigger will effect on partion index:ptif_test11_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test1_index;
+-- ptif_test2 is a partition so event trigger will log ptif_test2_index
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test2_index;
+-- no partitions: event trigger won't do anything
+REINDEX INDEX CONCURRENTLY reindex_test.ptif_test3_index;
+
+-- reindex at the top level table to recursively reindex each partition
+REINDEX TABLE reindex_test.ptif_test;
+-- will direct to ptif_test01
+REINDEX TABLE reindex_test.ptif_test0;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test01;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test11;
+-- will index its associtaed index
+REINDEX TABLE reindex_test.ptif_test2;
+-- no partion, index won't do anything
+REINDEX TABLE reindex_test.ptif_test3;
+
+DROP EVENT TRIGGER start_reindex_command;
+DROP EVENT TRIGGER end_reindex_command;
+DROP SCHEMA reindex_test CASCADE;
+DROP FUNCTION reindex_end_command;
+
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
-- 
2.34.1

From 149b4e9860f92daa810dfed4704f4c796a585b1a Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Thu, 23 Nov 2023 21:56:09 +0800
Subject: [PATCH v4 1/1] Add REINDEX tag to event triggers

This turns on the event trigger on command tag REINDEX.
This will now return a record for each index that was modified as
part of start/end ddl commands.

For concurrent reindex, this will return the OID for the new index. This
includes concurrently reindexing tables and databases. It will only
report the new index records and not the ones destroyed by the
concurrent reindex process.

We put the new index OID collection deeper into the stack, where we hold the locks on the new index we are collecting.
So event trigger REINDEX command new index oid collection will be on the reindex_index and ReindexRelationConcurrently.
specifically It will be after `index_open(newIndexId, ShareUpdateExclusiveLock);`
Author: Garrett Thornburg
---
 doc/src/sgml/event-trigger.sgml             |  8 ++
 src/backend/catalog/index.c                 | 26 +++++--
 src/backend/commands/cluster.c              |  2 +-
 src/backend/commands/indexcmds.c            | 72 +++++++++++-------
 src/backend/commands/tablecmds.c            |  2 +-
 src/backend/tcop/utility.c                  | 12 ++-
 src/include/catalog/index.h                 |  4 +-
 src/include/tcop/cmdtaglist.h               |  2 +-
 src/test/regress/expected/event_trigger.out | 81 +++++++++++++++++++++
 src/test/regress/sql/event_trigger.sql      | 71 ++++++++++++++++++
 10 files changed, 242 insertions(+), 38 deletions(-)

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 10b20f03..234b4ffd 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1032,6 +1032,14 @@
         <entry align="center"><literal>-</literal></entry>
         <entry align="left"></entry>
        </row>
+       <row>
+        <entry align="left"><literal>REINDEX</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="left"></entry>
+       </row>
        <row>
         <entry align="left"><literal>REVOKE</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 143fae01..2afabdeb 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -136,7 +136,7 @@ static void SetReindexProcessing(Oid heapOid, Oid indexOid);
 static void ResetReindexProcessing(void);
 static void SetReindexPending(List *indexes);
 static void RemoveReindexPending(Oid indexOid);
-
+static void reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt);
 
 /*
  * relationHasPrimaryKey
@@ -3554,11 +3554,24 @@ IndexGetRelation(Oid indexId, bool missing_ok)
 	return result;
 }
 
+static void
+reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt)
+{
+	ObjectAddress address;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) stmt);
+}
+
 /*
  * reindex_index - This routine is used to recreate a single index
  */
 void
-reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
+reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks, char persistence,
 			  const ReindexParams *params)
 {
 	Relation	iRel,
@@ -3629,6 +3642,9 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 	if (progress)
 		pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
 									 iRel->rd_rel->relam);
+	/* event trigger tracking REINDEX primary pointer */
+	if (stmt)
+		reindex_event_trigger_collect(indexId, stmt);
 
 	/*
 	 * Partitioned indexes should never get processed here, as they have no
@@ -3865,7 +3881,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
  * index rebuild.
  */
 bool
-reindex_relation(Oid relid, int flags, const ReindexParams *params)
+reindex_relation(const ReindexStmt *stmt, Oid relid, int flags, const ReindexParams *params)
 {
 	Relation	rel;
 	Oid			toast_relid;
@@ -3953,7 +3969,7 @@ reindex_relation(Oid relid, int flags, const ReindexParams *params)
 			continue;
 		}
 
-		reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+		reindex_index(stmt, indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
 					  persistence, params);
 
 		CommandCounterIncrement();
@@ -3990,7 +4006,7 @@ reindex_relation(Oid relid, int flags, const ReindexParams *params)
 
 		newparams.options &= ~(REINDEXOPT_MISSING_OK);
 		newparams.tablespaceOid = InvalidOid;
-		result |= reindex_relation(toast_relid, flags, &newparams);
+		result |= reindex_relation(stmt, toast_relid, flags, &newparams);
 	}
 
 	return result;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index a3bef6ac..1f52d391 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1518,7 +1518,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
 								 PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
 
-	reindex_relation(OIDOldHeap, reindex_flags, &reindex_params);
+	reindex_relation(NULL, OIDOldHeap, reindex_flags, &reindex_params);
 
 	/* Report that we are now doing clean up */
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 0b3b8e98..a596c64b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -94,20 +94,21 @@ static char *ChooseIndexName(const char *tabname, Oid namespaceId,
 							 bool primary, bool isconstraint);
 static char *ChooseIndexNameAddition(const List *colnames);
 static List *ChooseIndexColumnNames(const List *indexElems);
-static void ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params,
+static void ReindexIndex(const ReindexStmt *stmt, const ReindexParams *params,
 						 bool isTopLevel);
+static void reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt);
 static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
 											Oid relId, Oid oldRelId, void *arg);
-static Oid	ReindexTable(const RangeVar *relation, const ReindexParams *params,
+static Oid	ReindexTable(const ReindexStmt *stmt, const RangeVar *relation, const ReindexParams *params,
 						 bool isTopLevel);
-static void ReindexMultipleTables(const char *objectName,
+static void ReindexMultipleTables(const ReindexStmt *stmt, const char *objectName,
 								  ReindexObjectType objectKind, const ReindexParams *params);
 static void reindex_error_callback(void *arg);
-static void ReindexPartitions(Oid relid, const ReindexParams *params,
+static void ReindexPartitions(const ReindexStmt *stmt, Oid relid, const ReindexParams *params,
 							  bool isTopLevel);
-static void ReindexMultipleInternal(const List *relids,
+static void ReindexMultipleInternal(const ReindexStmt *stmt, const List *relids,
 									const ReindexParams *params);
-static bool ReindexRelationConcurrently(Oid relationOid,
+static bool ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid,
 										const ReindexParams *params);
 static void update_relispartition(Oid relationId, bool newval);
 static inline void set_indexsafe_procflags(void);
@@ -2729,10 +2730,10 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 	switch (stmt->kind)
 	{
 		case REINDEX_OBJECT_INDEX:
-			ReindexIndex(stmt->relation, &params, isTopLevel);
+			ReindexIndex(stmt, &params, isTopLevel);
 			break;
 		case REINDEX_OBJECT_TABLE:
-			ReindexTable(stmt->relation, &params, isTopLevel);
+			ReindexTable(stmt, stmt->relation, &params, isTopLevel);
 			break;
 		case REINDEX_OBJECT_SCHEMA:
 		case REINDEX_OBJECT_SYSTEM:
@@ -2748,7 +2749,7 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 									  (stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" :
 									  (stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" :
 									  "REINDEX DATABASE");
-			ReindexMultipleTables(stmt->name, stmt->kind, &params);
+			ReindexMultipleTables(stmt, stmt->name, stmt->kind, &params);
 			break;
 		default:
 			elog(ERROR, "unrecognized object type: %d",
@@ -2762,13 +2763,15 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
  *		Recreate a specific index.
  */
 static void
-ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool isTopLevel)
+ReindexIndex(const ReindexStmt *stmt, const ReindexParams *params, bool isTopLevel)
 {
+	const RangeVar *indexRelation;
 	struct ReindexIndexCallbackState state;
 	Oid			indOid;
 	char		persistence;
 	char		relkind;
 
+	indexRelation = stmt->relation;
 	/*
 	 * Find and lock index, and check permissions on table; use callback to
 	 * obtain lock on table first, to avoid deadlock hazard.  The lock level
@@ -2796,16 +2799,16 @@ ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool is
 	relkind = get_rel_relkind(indOid);
 
 	if (relkind == RELKIND_PARTITIONED_INDEX)
-		ReindexPartitions(indOid, params, isTopLevel);
+		ReindexPartitions(stmt, indOid, params, isTopLevel);
 	else if ((params->options & REINDEXOPT_CONCURRENTLY) != 0 &&
 			 persistence != RELPERSISTENCE_TEMP)
-		ReindexRelationConcurrently(indOid, params);
+		ReindexRelationConcurrently(stmt, indOid, params);
 	else
 	{
 		ReindexParams newparams = *params;
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
-		reindex_index(indOid, false, persistence, &newparams);
+		reindex_index(stmt, indOid, false, persistence, &newparams);
 	}
 }
 
@@ -2885,7 +2888,7 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
  *		Recreate all indexes of a table (and of its toast table, if any)
  */
 static Oid
-ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLevel)
+ReindexTable(const ReindexStmt *stmt, const RangeVar *relation, const ReindexParams *params, bool isTopLevel)
 {
 	Oid			heapOid;
 	bool		result;
@@ -2905,11 +2908,11 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 									   RangeVarCallbackOwnsTable, NULL);
 
 	if (get_rel_relkind(heapOid) == RELKIND_PARTITIONED_TABLE)
-		ReindexPartitions(heapOid, params, isTopLevel);
+		ReindexPartitions(stmt, heapOid, params, isTopLevel);
 	else if ((params->options & REINDEXOPT_CONCURRENTLY) != 0 &&
 			 get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
 	{
-		result = ReindexRelationConcurrently(heapOid, params);
+		result = ReindexRelationConcurrently(stmt, heapOid, params);
 
 		if (!result)
 			ereport(NOTICE,
@@ -2921,7 +2924,7 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 		ReindexParams newparams = *params;
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
-		result = reindex_relation(heapOid,
+		result = reindex_relation(stmt, heapOid,
 								  REINDEX_REL_PROCESS_TOAST |
 								  REINDEX_REL_CHECK_CONSTRAINTS,
 								  &newparams);
@@ -2943,7 +2946,7 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
  * That means this must not be called within a user transaction block!
  */
 static void
-ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
+ReindexMultipleTables(const ReindexStmt *stmt, const char *objectName, ReindexObjectType objectKind,
 					  const ReindexParams *params)
 {
 	Oid			objectOid;
@@ -3152,7 +3155,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	 * Process each relation listed in a separate transaction.  Note that this
 	 * commits and then starts a new transaction immediately.
 	 */
-	ReindexMultipleInternal(relids, params);
+	ReindexMultipleInternal(stmt, relids, params);
 
 	MemoryContextDelete(private_context);
 }
@@ -3182,7 +3185,7 @@ reindex_error_callback(void *arg)
  * by the caller.
  */
 static void
-ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
+ReindexPartitions(const ReindexStmt *stmt, Oid relid, const ReindexParams *params, bool isTopLevel)
 {
 	List	   *partitions = NIL;
 	char		relkind = get_rel_relkind(relid);
@@ -3258,7 +3261,7 @@ ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
 	 * Process each partition listed in a separate transaction.  Note that
 	 * this commits and then starts a new transaction immediately.
 	 */
-	ReindexMultipleInternal(partitions, params);
+	ReindexMultipleInternal(stmt, partitions, params);
 
 	/*
 	 * Clean up working storage --- note we must do this after
@@ -3276,7 +3279,7 @@ ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
  * and starts a new transaction when finished.
  */
 static void
-ReindexMultipleInternal(const List *relids, const ReindexParams *params)
+ReindexMultipleInternal(const ReindexStmt *stmt, const List *relids, const ReindexParams *params)
 {
 	ListCell   *l;
 
@@ -3335,7 +3338,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 			ReindexParams newparams = *params;
 
 			newparams.options |= REINDEXOPT_MISSING_OK;
-			(void) ReindexRelationConcurrently(relid, &newparams);
+			(void) ReindexRelationConcurrently(stmt, relid, &newparams);
 			/* ReindexRelationConcurrently() does the verbose output */
 		}
 		else if (relkind == RELKIND_INDEX)
@@ -3344,7 +3347,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
-			reindex_index(relid, false, relpersistence, &newparams);
+			reindex_index(stmt, relid, false, relpersistence, &newparams);
 			PopActiveSnapshot();
 			/* reindex_index() does the verbose output */
 		}
@@ -3355,7 +3358,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
-			result = reindex_relation(relid,
+			result = reindex_relation(stmt, relid,
 									  REINDEX_REL_PROCESS_TOAST |
 									  REINDEX_REL_CHECK_CONSTRAINTS,
 									  &newparams);
@@ -3400,7 +3403,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
  * anyway, and a non-concurrent reindex is more efficient.
  */
 static bool
-ReindexRelationConcurrently(Oid relationOid, const ReindexParams *params)
+ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const ReindexParams *params)
 {
 	typedef struct ReindexIndexInfo
 	{
@@ -3812,6 +3815,10 @@ ReindexRelationConcurrently(Oid relationOid, const ReindexParams *params)
 
 		newIndexIds = lappend(newIndexIds, newidx);
 
+		/* Add the index to event trigger */
+		if (stmt)
+			reindex_event_trigger_collect(newIndexId, stmt);
+
 		/*
 		 * Save lockrelid to protect each relation from drop then close
 		 * relations. The lockrelid on parent relation is not taken here to
@@ -4415,3 +4422,16 @@ set_indexsafe_procflags(void)
 	ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
 	LWLockRelease(ProcArrayLock);
 }
+
+static void
+reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt)
+{
+	ObjectAddress address;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) stmt);
+}
\ No newline at end of file
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 323d9bf8..85ee7c63 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2172,7 +2172,7 @@ ExecuteTruncateGuts(List *explicit_rels,
 			/*
 			 * Reconstruct the indexes to match, and we're done.
 			 */
-			reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST,
+			reindex_relation(NULL, heap_relid, REINDEX_REL_PROCESS_TOAST,
 							 &reindex_params);
 		}
 
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7..a269b5b6 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -961,7 +961,9 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_ReindexStmt:
-			ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+			ProcessUtilitySlow(pstate, pstmt, queryString,
+							   context, params, queryEnv,
+							   dest, qc);
 			break;
 
 			/*
@@ -1574,6 +1576,12 @@ ProcessUtilitySlow(ParseState *pstate,
 				}
 				break;
 
+			case T_ReindexStmt:
+				ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+				/* no commands stashed for REINDEX */
+				commandCollected = true;
+				break;
+
 			case T_CreateExtensionStmt:
 				address = CreateExtension(pstate, (CreateExtensionStmt *) parsetree);
 				break;
@@ -3620,7 +3628,7 @@ GetCommandLogLevel(Node *parsetree)
 			break;
 
 		case T_ReindexStmt:
-			lev = LOGSTMT_ALL;	/* should this be DDL? */
+			lev = LOGSTMT_DDL;
 			break;
 
 		case T_CreateConversionStmt:
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index a4770eaf..e1272662 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -149,7 +149,7 @@ extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action);
 
 extern Oid	IndexGetRelation(Oid indexId, bool missing_ok);
 
-extern void reindex_index(Oid indexId, bool skip_constraint_checks,
+extern void reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks,
 						  char persistence, const ReindexParams *params);
 
 /* Flag bits for reindex_relation(): */
@@ -159,7 +159,7 @@ extern void reindex_index(Oid indexId, bool skip_constraint_checks,
 #define REINDEX_REL_FORCE_INDEXES_UNLOGGED	0x08
 #define REINDEX_REL_FORCE_INDEXES_PERMANENT 0x10
 
-extern bool reindex_relation(Oid relid, int flags, const ReindexParams *params);
+extern bool reindex_relation(const ReindexStmt *stmt, Oid relid, int flags, const ReindexParams *params);
 
 extern bool ReindexIsProcessingHeap(Oid heapOid);
 extern bool ReindexIsProcessingIndex(Oid indexOid);
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 553a3187..320ee915 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -194,7 +194,7 @@ PG_CMDTAG(CMDTAG_PREPARE, "PREPARE", false, false, false)
 PG_CMDTAG(CMDTAG_PREPARE_TRANSACTION, "PREPARE TRANSACTION", false, false, false)
 PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
 PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false)
-PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", false, false, false)
+PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false)
 PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
 PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false)
 PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false)
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 0b87a42d..1f0f4aad 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -556,6 +556,87 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+NOTICE:  schema "schema_to_reindex" does not exist, skipping
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+    obj record;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        if  obj.schema_name = 'pg_toast' then
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                        ,obj.object_type, obj.schema_name;
+        else
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        end if;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor; --reindex_start_command part will invoke, reindex_end_command won't.
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;  --reindex partitioned table
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+REINDEX (CONCURRENTLY) SCHEMA  schema_to_reindex;  --should cover reindex most cases.
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX concur_reindex_ind1 ON schema_to_reindex.concur_reindex_tab USING btree (c1)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind2 ON schema_to_reindex.concur_reindex_tab USING btree (c2)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind4 ON schema_to_reindex.concur_reindex_tab USING btree (c1, c1, c2)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table concur_reindex_tab
+drop cascades to table parted_irreg_ancestor
+RESET search_path;
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 6f0933b9..fbe1a858 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -418,6 +418,77 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+    obj record;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        if  obj.schema_name = 'pg_toast' then
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                        ,obj.object_type, obj.schema_name;
+        else
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        end if;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor; --reindex_start_command part will invoke, reindex_end_command won't.
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;  --reindex partitioned table
+REINDEX (CONCURRENTLY) SCHEMA  schema_to_reindex;  --should cover reindex most cases.
+
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+RESET search_path;
+
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
-- 
2.34.1

From a72aaf4dcc2ae70766f5380855e7013035c63d9c Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Fri, 24 Nov 2023 20:44:44 +0800
Subject: [PATCH v5 2/4] tag ReindexStmt as ddl in GetCommandLogLevel.

In GetCommandLogLevel, change T_ReindexStmt from lev = LOGSTMT_ALL to lev = LOGSTMT_DDL.
so log_statement (enum) >= 'ddl' will make the reindex statement be logged.
---
 src/backend/tcop/utility.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 5a7f63cb..a269b5b6 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -3628,7 +3628,7 @@ GetCommandLogLevel(Node *parsetree)
 			break;
 
 		case T_ReindexStmt:
-			lev = LOGSTMT_ALL;	/* should this be DDL? */
+			lev = LOGSTMT_DDL;
 			break;
 
 		case T_CreateConversionStmt:
-- 
2.34.1

From 864f53c932cd2b692b5042bbfddc021d5e49ef0c Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Sat, 25 Nov 2023 09:28:07 +0800
Subject: [PATCH v5 4/4] Event trigger support reindex command. this add
 documentation, regress test, static function pass reindex info to function
 EventTriggerCollectSimpleCommand.

---
 doc/src/sgml/event-trigger.sgml             |   8 ++
 src/backend/catalog/index.c                 |   5 +-
 src/backend/commands/indexcmds.c            |  17 ++++
 src/test/regress/expected/event_trigger.out | 103 ++++++++++++++++++++
 src/test/regress/sql/event_trigger.sql      |  93 ++++++++++++++++++
 5 files changed, 225 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 10b20f03..234b4ffd 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1032,6 +1032,14 @@
         <entry align="center"><literal>-</literal></entry>
         <entry align="left"></entry>
        </row>
+       <row>
+        <entry align="left"><literal>REINDEX</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="left"></entry>
+       </row>
        <row>
         <entry align="left"><literal>REVOKE</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index fc09191c..2afabdeb 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -136,7 +136,7 @@ static void SetReindexProcessing(Oid heapOid, Oid indexOid);
 static void ResetReindexProcessing(void);
 static void SetReindexPending(List *indexes);
 static void RemoveReindexPending(Oid indexOid);
-
+static void reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt);
 
 /*
  * relationHasPrimaryKey
@@ -3642,6 +3642,9 @@ reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks,
 	if (progress)
 		pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
 									 iRel->rd_rel->relam);
+	/* event trigger tracking REINDEX primary pointer */
+	if (stmt)
+		reindex_event_trigger_collect(indexId, stmt);
 
 	/*
 	 * Partitioned indexes should never get processed here, as they have no
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index f63647e3..cb31767d 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -3817,6 +3817,10 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
 
 		newIndexIds = lappend(newIndexIds, newidx);
 
+		/* Add the index to event trigger */
+		if (stmt)
+			reindex_event_trigger_collect(newIndexId, stmt);
+
 		/*
 		 * Save lockrelid to protect each relation from drop then close
 		 * relations. The lockrelid on parent relation is not taken here to
@@ -4420,3 +4424,16 @@ set_indexsafe_procflags(void)
 	ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
 	LWLockRelease(ProcArrayLock);
 }
+
+static void
+reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt)
+{
+	ObjectAddress address;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) stmt);
+}
\ No newline at end of file
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 0b87a42d..5dfb2ee1 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -556,6 +556,109 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+NOTICE:  schema "schema_to_reindex" does not exist, skipping
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+	obj record;
+	toast_main_table text;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        IF  obj.schema_name = 'pg_toast' THEN
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                          ,obj.object_type, obj.schema_name;
+            /* get the toast table will be reindexed main table.
+             * toast table name auto generated, cannot use to test.
+            */
+            SELECT t1.relname into toast_main_table
+            FROM (
+                SELECT t1.oid
+                FROM pg_class t1 INNER JOIN pg_index t2 ON t1.oid = t2.indrelid
+                WHERE t2.indexrelid = obj.objid AND relkind = 't') sub
+                INNER JOIN pg_class t1 ON t1.reltoastrelid = sub.oid;
+
+            RAISE NOTICE 'toast table related main relation: %', toast_main_table;
+        ELSE
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        END IF;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+-- event trigger disabled.
+--so reindex_start_command part will invoke, reindex_end_command won't.
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg1
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg2
+/* REINDEX SCHEMA (CONCURRENTLY) generated test result order unstable,
+ * So just do normal reindex schema. should cover reindex statement most cases.
+*/
+REINDEX SCHEMA  schema_to_reindex;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX concur_reindex_ind1 ON schema_to_reindex.concur_reindex_tab USING btree (c1)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind2 ON schema_to_reindex.concur_reindex_tab USING btree (c2)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind4 ON schema_to_reindex.concur_reindex_tab USING btree (c1, c1, c2)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: concur_reindex_tab
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg1
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg2
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table concur_reindex_tab
+drop cascades to table parted_irreg_ancestor
+RESET search_path;
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 6f0933b9..ace58308 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -418,6 +418,99 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+	obj record;
+	toast_main_table text;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        IF  obj.schema_name = 'pg_toast' THEN
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                          ,obj.object_type, obj.schema_name;
+            /* get the toast table will be reindexed main table.
+             * toast table name auto generated, cannot use to test.
+            */
+            SELECT t1.relname into toast_main_table
+            FROM (
+                SELECT t1.oid
+                FROM pg_class t1 INNER JOIN pg_index t2 ON t1.oid = t2.indrelid
+                WHERE t2.indexrelid = obj.objid AND relkind = 't') sub
+                INNER JOIN pg_class t1 ON t1.reltoastrelid = sub.oid;
+
+            RAISE NOTICE 'toast table related main relation: %', toast_main_table;
+        ELSE
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        END IF;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+
+-- event trigger disabled.
+--so reindex_start_command part will invoke, reindex_end_command won't.
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+
+/* REINDEX SCHEMA (CONCURRENTLY) generated test result order unstable,
+ * So just do normal reindex schema. should cover reindex statement most cases.
+*/
+REINDEX SCHEMA  schema_to_reindex;
+
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+RESET search_path;
+
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
-- 
2.34.1

From 463d0ce9e8c5d37f989aaf47cc28194b3375bea2 Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Fri, 24 Nov 2023 20:52:08 +0800
Subject: [PATCH v5 3/4] refactor multiple functions to support tracking
 reindex using event trigger.

Refactor the following functions:
ReindexIndex,
ReindexTable,
ReindexMultipleTables,
ReindexPartitions,ReindexMultipleInternal,
ReindexRelationConcurrently,
reindex_relation,
reindex_index
by adding `const ReindexStmt *stmt` as their first argument.
---
 src/backend/catalog/index.c      | 21 ++++++++---
 src/backend/commands/cluster.c   |  2 +-
 src/backend/commands/indexcmds.c | 61 +++++++++++++++++---------------
 src/backend/commands/tablecmds.c |  2 +-
 src/include/catalog/index.h      |  4 +--
 5 files changed, 54 insertions(+), 36 deletions(-)

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 143fae01..fc09191c 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -3554,11 +3554,24 @@ IndexGetRelation(Oid indexId, bool missing_ok)
 	return result;
 }
 
+static void
+reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt)
+{
+	ObjectAddress address;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) stmt);
+}
+
 /*
  * reindex_index - This routine is used to recreate a single index
  */
 void
-reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
+reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks, char persistence,
 			  const ReindexParams *params)
 {
 	Relation	iRel,
@@ -3865,7 +3878,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
  * index rebuild.
  */
 bool
-reindex_relation(Oid relid, int flags, const ReindexParams *params)
+reindex_relation(const ReindexStmt *stmt, Oid relid, int flags, const ReindexParams *params)
 {
 	Relation	rel;
 	Oid			toast_relid;
@@ -3953,7 +3966,7 @@ reindex_relation(Oid relid, int flags, const ReindexParams *params)
 			continue;
 		}
 
-		reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+		reindex_index(stmt, indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
 					  persistence, params);
 
 		CommandCounterIncrement();
@@ -3990,7 +4003,7 @@ reindex_relation(Oid relid, int flags, const ReindexParams *params)
 
 		newparams.options &= ~(REINDEXOPT_MISSING_OK);
 		newparams.tablespaceOid = InvalidOid;
-		result |= reindex_relation(toast_relid, flags, &newparams);
+		result |= reindex_relation(stmt, toast_relid, flags, &newparams);
 	}
 
 	return result;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index a3bef6ac..1f52d391 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1518,7 +1518,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
 								 PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
 
-	reindex_relation(OIDOldHeap, reindex_flags, &reindex_params);
+	reindex_relation(NULL, OIDOldHeap, reindex_flags, &reindex_params);
 
 	/* Report that we are now doing clean up */
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 0b3b8e98..f63647e3 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -94,20 +94,20 @@ static char *ChooseIndexName(const char *tabname, Oid namespaceId,
 							 bool primary, bool isconstraint);
 static char *ChooseIndexNameAddition(const List *colnames);
 static List *ChooseIndexColumnNames(const List *indexElems);
-static void ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params,
+static void ReindexIndex(const ReindexStmt *stmt, const ReindexParams *params,
 						 bool isTopLevel);
+static void reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt);
 static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
 											Oid relId, Oid oldRelId, void *arg);
-static Oid	ReindexTable(const RangeVar *relation, const ReindexParams *params,
+static Oid	ReindexTable(const ReindexStmt *stmt, const ReindexParams *params,
 						 bool isTopLevel);
-static void ReindexMultipleTables(const char *objectName,
-								  ReindexObjectType objectKind, const ReindexParams *params);
+static void ReindexMultipleTables(const ReindexStmt *stmt, const ReindexParams *params);
 static void reindex_error_callback(void *arg);
-static void ReindexPartitions(Oid relid, const ReindexParams *params,
+static void ReindexPartitions(const ReindexStmt *stmt, Oid relid, const ReindexParams *params,
 							  bool isTopLevel);
-static void ReindexMultipleInternal(const List *relids,
+static void ReindexMultipleInternal(const ReindexStmt *stmt, const List *relids,
 									const ReindexParams *params);
-static bool ReindexRelationConcurrently(Oid relationOid,
+static bool ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid,
 										const ReindexParams *params);
 static void update_relispartition(Oid relationId, bool newval);
 static inline void set_indexsafe_procflags(void);
@@ -2729,10 +2729,10 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 	switch (stmt->kind)
 	{
 		case REINDEX_OBJECT_INDEX:
-			ReindexIndex(stmt->relation, &params, isTopLevel);
+			ReindexIndex(stmt, &params, isTopLevel);
 			break;
 		case REINDEX_OBJECT_TABLE:
-			ReindexTable(stmt->relation, &params, isTopLevel);
+			ReindexTable(stmt, &params, isTopLevel);
 			break;
 		case REINDEX_OBJECT_SCHEMA:
 		case REINDEX_OBJECT_SYSTEM:
@@ -2748,7 +2748,7 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 									  (stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" :
 									  (stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" :
 									  "REINDEX DATABASE");
-			ReindexMultipleTables(stmt->name, stmt->kind, &params);
+			ReindexMultipleTables(stmt, &params);
 			break;
 		default:
 			elog(ERROR, "unrecognized object type: %d",
@@ -2762,13 +2762,15 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
  *		Recreate a specific index.
  */
 static void
-ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool isTopLevel)
+ReindexIndex(const ReindexStmt *stmt, const ReindexParams *params, bool isTopLevel)
 {
+	const RangeVar *indexRelation;
 	struct ReindexIndexCallbackState state;
 	Oid			indOid;
 	char		persistence;
 	char		relkind;
 
+	indexRelation = stmt->relation;
 	/*
 	 * Find and lock index, and check permissions on table; use callback to
 	 * obtain lock on table first, to avoid deadlock hazard.  The lock level
@@ -2796,16 +2798,16 @@ ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool is
 	relkind = get_rel_relkind(indOid);
 
 	if (relkind == RELKIND_PARTITIONED_INDEX)
-		ReindexPartitions(indOid, params, isTopLevel);
+		ReindexPartitions(stmt, indOid, params, isTopLevel);
 	else if ((params->options & REINDEXOPT_CONCURRENTLY) != 0 &&
 			 persistence != RELPERSISTENCE_TEMP)
-		ReindexRelationConcurrently(indOid, params);
+		ReindexRelationConcurrently(stmt, indOid, params);
 	else
 	{
 		ReindexParams newparams = *params;
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
-		reindex_index(indOid, false, persistence, &newparams);
+		reindex_index(stmt, indOid, false, persistence, &newparams);
 	}
 }
 
@@ -2885,11 +2887,12 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
  *		Recreate all indexes of a table (and of its toast table, if any)
  */
 static Oid
-ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLevel)
+ReindexTable(const ReindexStmt *stmt, const ReindexParams *params, bool isTopLevel)
 {
 	Oid			heapOid;
 	bool		result;
 
+	const RangeVar *relation = stmt->relation;
 	/*
 	 * The lock level used here should match reindex_relation().
 	 *
@@ -2905,11 +2908,11 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 									   RangeVarCallbackOwnsTable, NULL);
 
 	if (get_rel_relkind(heapOid) == RELKIND_PARTITIONED_TABLE)
-		ReindexPartitions(heapOid, params, isTopLevel);
+		ReindexPartitions(stmt, heapOid, params, isTopLevel);
 	else if ((params->options & REINDEXOPT_CONCURRENTLY) != 0 &&
 			 get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
 	{
-		result = ReindexRelationConcurrently(heapOid, params);
+		result = ReindexRelationConcurrently(stmt, heapOid, params);
 
 		if (!result)
 			ereport(NOTICE,
@@ -2921,7 +2924,7 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 		ReindexParams newparams = *params;
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
-		result = reindex_relation(heapOid,
+		result = reindex_relation(stmt, heapOid,
 								  REINDEX_REL_PROCESS_TOAST |
 								  REINDEX_REL_CHECK_CONSTRAINTS,
 								  &newparams);
@@ -2943,9 +2946,9 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
  * That means this must not be called within a user transaction block!
  */
 static void
-ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
-					  const ReindexParams *params)
+ReindexMultipleTables(const ReindexStmt *stmt, const ReindexParams *params)
 {
+
 	Oid			objectOid;
 	Relation	relationRelation;
 	TableScanDesc scan;
@@ -2957,6 +2960,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	int			num_keys;
 	bool		concurrent_warning = false;
 	bool		tablespace_warning = false;
+	const char *objectName = stmt->name;
+	const ReindexObjectType objectKind = stmt->kind;
 
 	Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
 		   objectKind == REINDEX_OBJECT_SYSTEM ||
@@ -3152,7 +3157,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	 * Process each relation listed in a separate transaction.  Note that this
 	 * commits and then starts a new transaction immediately.
 	 */
-	ReindexMultipleInternal(relids, params);
+	ReindexMultipleInternal(stmt, relids, params);
 
 	MemoryContextDelete(private_context);
 }
@@ -3182,7 +3187,7 @@ reindex_error_callback(void *arg)
  * by the caller.
  */
 static void
-ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
+ReindexPartitions(const ReindexStmt *stmt, Oid relid, const ReindexParams *params, bool isTopLevel)
 {
 	List	   *partitions = NIL;
 	char		relkind = get_rel_relkind(relid);
@@ -3258,7 +3263,7 @@ ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
 	 * Process each partition listed in a separate transaction.  Note that
 	 * this commits and then starts a new transaction immediately.
 	 */
-	ReindexMultipleInternal(partitions, params);
+	ReindexMultipleInternal(stmt, partitions, params);
 
 	/*
 	 * Clean up working storage --- note we must do this after
@@ -3276,7 +3281,7 @@ ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
  * and starts a new transaction when finished.
  */
 static void
-ReindexMultipleInternal(const List *relids, const ReindexParams *params)
+ReindexMultipleInternal(const ReindexStmt *stmt, const List *relids, const ReindexParams *params)
 {
 	ListCell   *l;
 
@@ -3335,7 +3340,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 			ReindexParams newparams = *params;
 
 			newparams.options |= REINDEXOPT_MISSING_OK;
-			(void) ReindexRelationConcurrently(relid, &newparams);
+			(void) ReindexRelationConcurrently(stmt, relid, &newparams);
 			/* ReindexRelationConcurrently() does the verbose output */
 		}
 		else if (relkind == RELKIND_INDEX)
@@ -3344,7 +3349,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
-			reindex_index(relid, false, relpersistence, &newparams);
+			reindex_index(stmt, relid, false, relpersistence, &newparams);
 			PopActiveSnapshot();
 			/* reindex_index() does the verbose output */
 		}
@@ -3355,7 +3360,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
-			result = reindex_relation(relid,
+			result = reindex_relation(stmt, relid,
 									  REINDEX_REL_PROCESS_TOAST |
 									  REINDEX_REL_CHECK_CONSTRAINTS,
 									  &newparams);
@@ -3400,7 +3405,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
  * anyway, and a non-concurrent reindex is more efficient.
  */
 static bool
-ReindexRelationConcurrently(Oid relationOid, const ReindexParams *params)
+ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const ReindexParams *params)
 {
 	typedef struct ReindexIndexInfo
 	{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 323d9bf8..85ee7c63 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2172,7 +2172,7 @@ ExecuteTruncateGuts(List *explicit_rels,
 			/*
 			 * Reconstruct the indexes to match, and we're done.
 			 */
-			reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST,
+			reindex_relation(NULL, heap_relid, REINDEX_REL_PROCESS_TOAST,
 							 &reindex_params);
 		}
 
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index a4770eaf..e1272662 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -149,7 +149,7 @@ extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action);
 
 extern Oid	IndexGetRelation(Oid indexId, bool missing_ok);
 
-extern void reindex_index(Oid indexId, bool skip_constraint_checks,
+extern void reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks,
 						  char persistence, const ReindexParams *params);
 
 /* Flag bits for reindex_relation(): */
@@ -159,7 +159,7 @@ extern void reindex_index(Oid indexId, bool skip_constraint_checks,
 #define REINDEX_REL_FORCE_INDEXES_UNLOGGED	0x08
 #define REINDEX_REL_FORCE_INDEXES_PERMANENT 0x10
 
-extern bool reindex_relation(Oid relid, int flags, const ReindexParams *params);
+extern bool reindex_relation(const ReindexStmt *stmt, Oid relid, int flags, const ReindexParams *params);
 
 extern bool ReindexIsProcessingHeap(Oid heapOid);
 extern bool ReindexIsProcessingIndex(Oid indexOid);
-- 
2.34.1

From c6d19985d559db51a9b588d2e5b5adeb202a2e5a Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Fri, 24 Nov 2023 21:22:12 +0800
Subject: [PATCH v5 1/4] make event triggers facility react to ReindexStmt

Move ReindexStmt moves from standard_ProcessUtility to ProcessUtilitySlow.
By default ProcessUtilitySlow will call trigger related functions.
So the event trigger facility can support reindex statements.
---
 src/backend/tcop/utility.c    | 10 +++++++++-
 src/include/tcop/cmdtaglist.h |  2 +-
 2 files changed, 10 insertions(+), 2 deletions(-)

diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7..5a7f63cb 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -961,7 +961,9 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 			break;
 
 		case T_ReindexStmt:
-			ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+			ProcessUtilitySlow(pstate, pstmt, queryString,
+							   context, params, queryEnv,
+							   dest, qc);
 			break;
 
 			/*
@@ -1574,6 +1576,12 @@ ProcessUtilitySlow(ParseState *pstate,
 				}
 				break;
 
+			case T_ReindexStmt:
+				ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+				/* no commands stashed for REINDEX */
+				commandCollected = true;
+				break;
+
 			case T_CreateExtensionStmt:
 				address = CreateExtension(pstate, (CreateExtensionStmt *) parsetree);
 				break;
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 553a3187..320ee915 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -194,7 +194,7 @@ PG_CMDTAG(CMDTAG_PREPARE, "PREPARE", false, false, false)
 PG_CMDTAG(CMDTAG_PREPARE_TRANSACTION, "PREPARE TRANSACTION", false, false, false)
 PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
 PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false)
-PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", false, false, false)
+PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false)
 PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
 PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false)
 PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false)
-- 
2.34.1

From 6a4f1bee35867565b5f65902db642a0d111be6e6 Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Mon, 27 Nov 2023 23:43:01 +0800
Subject: [PATCH v6 1/4] make event triggers facility react to ReindexStmt

Move ReindexStmt moves from standard_ProcessUtility to ProcessUtilitySlow.
By default ProcessUtilitySlow will call trigger related functions.
So the event trigger facility able to support reindex statements.
---
 src/backend/tcop/utility.c    | 10 ++++++----
 src/include/tcop/cmdtaglist.h |  2 +-
 2 files changed, 7 insertions(+), 5 deletions(-)

diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e3ccf6c7..37161eda 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -960,10 +960,6 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 							  (RecoveryInProgress() ? 0 : CHECKPOINT_FORCE));
 			break;
 
-		case T_ReindexStmt:
-			ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
-			break;
-
 			/*
 			 * The following statements are supported by Event Triggers only
 			 * in some cases, so we "fast path" them in the other cases.
@@ -1574,6 +1570,12 @@ ProcessUtilitySlow(ParseState *pstate,
 				}
 				break;
 
+			case T_ReindexStmt:
+				ExecReindex(pstate, (ReindexStmt *) parsetree, isTopLevel);
+				/* commandCollected done in the deep inside of ExecReindex */
+				commandCollected = true;
+				break;
+
 			case T_CreateExtensionStmt:
 				address = CreateExtension(pstate, (CreateExtensionStmt *) parsetree);
 				break;
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 553a3187..320ee915 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -194,7 +194,7 @@ PG_CMDTAG(CMDTAG_PREPARE, "PREPARE", false, false, false)
 PG_CMDTAG(CMDTAG_PREPARE_TRANSACTION, "PREPARE TRANSACTION", false, false, false)
 PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false)
 PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false)
-PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", false, false, false)
+PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false)
 PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false)
 PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false)
 PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false)
-- 
2.34.1

From 141b176ba6226e61698a08e0c429d7ba61939e7b Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Fri, 24 Nov 2023 20:44:44 +0800
Subject: [PATCH v6 2/4] tag ReindexStmt as ddl in GetCommandLogLevel.

In GetCommandLogLevel, change T_ReindexStmt from lev = LOGSTMT_ALL to lev = LOGSTMT_DDL.
so log_statement (enum) >= 'ddl' will make the reindex statement be logged.
---
 src/backend/tcop/utility.c | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 37161eda..e115b0ca 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -3622,7 +3622,7 @@ GetCommandLogLevel(Node *parsetree)
 			break;
 
 		case T_ReindexStmt:
-			lev = LOGSTMT_ALL;	/* should this be DDL? */
+			lev = LOGSTMT_DDL;
 			break;
 
 		case T_CreateConversionStmt:
-- 
2.34.1

From f6a660d14b3649f046c2d54c810ccf28f51f65db Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Tue, 28 Nov 2023 00:05:50 +0800
Subject: [PATCH v6 3/4] refactor multiple functions to support tracking
 reindex using event trigger.

---
 src/backend/catalog/index.c      |  8 ++---
 src/backend/commands/cluster.c   |  2 +-
 src/backend/commands/indexcmds.c | 60 +++++++++++++++++---------------
 src/backend/commands/tablecmds.c |  2 +-
 src/include/catalog/index.h      |  4 +--
 5 files changed, 40 insertions(+), 36 deletions(-)

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 143fae01..88ff8c5a 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -3558,7 +3558,7 @@ IndexGetRelation(Oid indexId, bool missing_ok)
  * reindex_index - This routine is used to recreate a single index
  */
 void
-reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
+reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks, char persistence,
 			  const ReindexParams *params)
 {
 	Relation	iRel,
@@ -3865,7 +3865,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
  * index rebuild.
  */
 bool
-reindex_relation(Oid relid, int flags, const ReindexParams *params)
+reindex_relation(const ReindexStmt *stmt, Oid relid, int flags, const ReindexParams *params)
 {
 	Relation	rel;
 	Oid			toast_relid;
@@ -3953,7 +3953,7 @@ reindex_relation(Oid relid, int flags, const ReindexParams *params)
 			continue;
 		}
 
-		reindex_index(indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
+		reindex_index(stmt, indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
 					  persistence, params);
 
 		CommandCounterIncrement();
@@ -3990,7 +3990,7 @@ reindex_relation(Oid relid, int flags, const ReindexParams *params)
 
 		newparams.options &= ~(REINDEXOPT_MISSING_OK);
 		newparams.tablespaceOid = InvalidOid;
-		result |= reindex_relation(toast_relid, flags, &newparams);
+		result |= reindex_relation(stmt, toast_relid, flags, &newparams);
 	}
 
 	return result;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index a3bef6ac..1f52d391 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1518,7 +1518,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
 								 PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
 
-	reindex_relation(OIDOldHeap, reindex_flags, &reindex_params);
+	reindex_relation(NULL, OIDOldHeap, reindex_flags, &reindex_params);
 
 	/* Report that we are now doing clean up */
 	pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 0b3b8e98..f47a4aa7 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -94,20 +94,19 @@ static char *ChooseIndexName(const char *tabname, Oid namespaceId,
 							 bool primary, bool isconstraint);
 static char *ChooseIndexNameAddition(const List *colnames);
 static List *ChooseIndexColumnNames(const List *indexElems);
-static void ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params,
+static void ReindexIndex(const ReindexStmt *stmt, const ReindexParams *params,
 						 bool isTopLevel);
 static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
 											Oid relId, Oid oldRelId, void *arg);
-static Oid	ReindexTable(const RangeVar *relation, const ReindexParams *params,
+static Oid	ReindexTable(const ReindexStmt *stmt, const ReindexParams *params,
 						 bool isTopLevel);
-static void ReindexMultipleTables(const char *objectName,
-								  ReindexObjectType objectKind, const ReindexParams *params);
+static void ReindexMultipleTables(const ReindexStmt *stmt, const ReindexParams *params);
 static void reindex_error_callback(void *arg);
-static void ReindexPartitions(Oid relid, const ReindexParams *params,
+static void ReindexPartitions(const ReindexStmt *stmt, Oid relid, const ReindexParams *params,
 							  bool isTopLevel);
-static void ReindexMultipleInternal(const List *relids,
+static void ReindexMultipleInternal(const ReindexStmt *stmt, const List *relids,
 									const ReindexParams *params);
-static bool ReindexRelationConcurrently(Oid relationOid,
+static bool ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid,
 										const ReindexParams *params);
 static void update_relispartition(Oid relationId, bool newval);
 static inline void set_indexsafe_procflags(void);
@@ -2729,10 +2728,10 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 	switch (stmt->kind)
 	{
 		case REINDEX_OBJECT_INDEX:
-			ReindexIndex(stmt->relation, &params, isTopLevel);
+			ReindexIndex(stmt, &params, isTopLevel);
 			break;
 		case REINDEX_OBJECT_TABLE:
-			ReindexTable(stmt->relation, &params, isTopLevel);
+			ReindexTable(stmt, &params, isTopLevel);
 			break;
 		case REINDEX_OBJECT_SCHEMA:
 		case REINDEX_OBJECT_SYSTEM:
@@ -2748,7 +2747,7 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
 									  (stmt->kind == REINDEX_OBJECT_SCHEMA) ? "REINDEX SCHEMA" :
 									  (stmt->kind == REINDEX_OBJECT_SYSTEM) ? "REINDEX SYSTEM" :
 									  "REINDEX DATABASE");
-			ReindexMultipleTables(stmt->name, stmt->kind, &params);
+			ReindexMultipleTables(stmt, &params);
 			break;
 		default:
 			elog(ERROR, "unrecognized object type: %d",
@@ -2762,13 +2761,15 @@ ExecReindex(ParseState *pstate, const ReindexStmt *stmt, bool isTopLevel)
  *		Recreate a specific index.
  */
 static void
-ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool isTopLevel)
+ReindexIndex(const ReindexStmt *stmt, const ReindexParams *params, bool isTopLevel)
 {
+	const RangeVar *indexRelation;
 	struct ReindexIndexCallbackState state;
 	Oid			indOid;
 	char		persistence;
 	char		relkind;
 
+	indexRelation = stmt->relation;
 	/*
 	 * Find and lock index, and check permissions on table; use callback to
 	 * obtain lock on table first, to avoid deadlock hazard.  The lock level
@@ -2796,16 +2797,16 @@ ReindexIndex(const RangeVar *indexRelation, const ReindexParams *params, bool is
 	relkind = get_rel_relkind(indOid);
 
 	if (relkind == RELKIND_PARTITIONED_INDEX)
-		ReindexPartitions(indOid, params, isTopLevel);
+		ReindexPartitions(stmt, indOid, params, isTopLevel);
 	else if ((params->options & REINDEXOPT_CONCURRENTLY) != 0 &&
 			 persistence != RELPERSISTENCE_TEMP)
-		ReindexRelationConcurrently(indOid, params);
+		ReindexRelationConcurrently(stmt, indOid, params);
 	else
 	{
 		ReindexParams newparams = *params;
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
-		reindex_index(indOid, false, persistence, &newparams);
+		reindex_index(stmt, indOid, false, persistence, &newparams);
 	}
 }
 
@@ -2885,11 +2886,12 @@ RangeVarCallbackForReindexIndex(const RangeVar *relation,
  *		Recreate all indexes of a table (and of its toast table, if any)
  */
 static Oid
-ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLevel)
+ReindexTable(const ReindexStmt *stmt, const ReindexParams *params, bool isTopLevel)
 {
 	Oid			heapOid;
 	bool		result;
 
+	const RangeVar *relation = stmt->relation;
 	/*
 	 * The lock level used here should match reindex_relation().
 	 *
@@ -2905,11 +2907,11 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 									   RangeVarCallbackOwnsTable, NULL);
 
 	if (get_rel_relkind(heapOid) == RELKIND_PARTITIONED_TABLE)
-		ReindexPartitions(heapOid, params, isTopLevel);
+		ReindexPartitions(stmt, heapOid, params, isTopLevel);
 	else if ((params->options & REINDEXOPT_CONCURRENTLY) != 0 &&
 			 get_rel_persistence(heapOid) != RELPERSISTENCE_TEMP)
 	{
-		result = ReindexRelationConcurrently(heapOid, params);
+		result = ReindexRelationConcurrently(stmt, heapOid, params);
 
 		if (!result)
 			ereport(NOTICE,
@@ -2921,7 +2923,7 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
 		ReindexParams newparams = *params;
 
 		newparams.options |= REINDEXOPT_REPORT_PROGRESS;
-		result = reindex_relation(heapOid,
+		result = reindex_relation(stmt, heapOid,
 								  REINDEX_REL_PROCESS_TOAST |
 								  REINDEX_REL_CHECK_CONSTRAINTS,
 								  &newparams);
@@ -2943,9 +2945,9 @@ ReindexTable(const RangeVar *relation, const ReindexParams *params, bool isTopLe
  * That means this must not be called within a user transaction block!
  */
 static void
-ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
-					  const ReindexParams *params)
+ReindexMultipleTables(const ReindexStmt *stmt, const ReindexParams *params)
 {
+
 	Oid			objectOid;
 	Relation	relationRelation;
 	TableScanDesc scan;
@@ -2957,6 +2959,8 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	int			num_keys;
 	bool		concurrent_warning = false;
 	bool		tablespace_warning = false;
+	const char *objectName = stmt->name;
+	const ReindexObjectType objectKind = stmt->kind;
 
 	Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
 		   objectKind == REINDEX_OBJECT_SYSTEM ||
@@ -3152,7 +3156,7 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	 * Process each relation listed in a separate transaction.  Note that this
 	 * commits and then starts a new transaction immediately.
 	 */
-	ReindexMultipleInternal(relids, params);
+	ReindexMultipleInternal(stmt, relids, params);
 
 	MemoryContextDelete(private_context);
 }
@@ -3182,7 +3186,7 @@ reindex_error_callback(void *arg)
  * by the caller.
  */
 static void
-ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
+ReindexPartitions(const ReindexStmt *stmt, Oid relid, const ReindexParams *params, bool isTopLevel)
 {
 	List	   *partitions = NIL;
 	char		relkind = get_rel_relkind(relid);
@@ -3258,7 +3262,7 @@ ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
 	 * Process each partition listed in a separate transaction.  Note that
 	 * this commits and then starts a new transaction immediately.
 	 */
-	ReindexMultipleInternal(partitions, params);
+	ReindexMultipleInternal(stmt, partitions, params);
 
 	/*
 	 * Clean up working storage --- note we must do this after
@@ -3276,7 +3280,7 @@ ReindexPartitions(Oid relid, const ReindexParams *params, bool isTopLevel)
  * and starts a new transaction when finished.
  */
 static void
-ReindexMultipleInternal(const List *relids, const ReindexParams *params)
+ReindexMultipleInternal(const ReindexStmt *stmt, const List *relids, const ReindexParams *params)
 {
 	ListCell   *l;
 
@@ -3335,7 +3339,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 			ReindexParams newparams = *params;
 
 			newparams.options |= REINDEXOPT_MISSING_OK;
-			(void) ReindexRelationConcurrently(relid, &newparams);
+			(void) ReindexRelationConcurrently(stmt, relid, &newparams);
 			/* ReindexRelationConcurrently() does the verbose output */
 		}
 		else if (relkind == RELKIND_INDEX)
@@ -3344,7 +3348,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
-			reindex_index(relid, false, relpersistence, &newparams);
+			reindex_index(stmt, relid, false, relpersistence, &newparams);
 			PopActiveSnapshot();
 			/* reindex_index() does the verbose output */
 		}
@@ -3355,7 +3359,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
 
 			newparams.options |=
 				REINDEXOPT_REPORT_PROGRESS | REINDEXOPT_MISSING_OK;
-			result = reindex_relation(relid,
+			result = reindex_relation(stmt, relid,
 									  REINDEX_REL_PROCESS_TOAST |
 									  REINDEX_REL_CHECK_CONSTRAINTS,
 									  &newparams);
@@ -3400,7 +3404,7 @@ ReindexMultipleInternal(const List *relids, const ReindexParams *params)
  * anyway, and a non-concurrent reindex is more efficient.
  */
 static bool
-ReindexRelationConcurrently(Oid relationOid, const ReindexParams *params)
+ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const ReindexParams *params)
 {
 	typedef struct ReindexIndexInfo
 	{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 323d9bf8..85ee7c63 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2172,7 +2172,7 @@ ExecuteTruncateGuts(List *explicit_rels,
 			/*
 			 * Reconstruct the indexes to match, and we're done.
 			 */
-			reindex_relation(heap_relid, REINDEX_REL_PROCESS_TOAST,
+			reindex_relation(NULL, heap_relid, REINDEX_REL_PROCESS_TOAST,
 							 &reindex_params);
 		}
 
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index a4770eaf..e1272662 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -149,7 +149,7 @@ extern void index_set_state_flags(Oid indexId, IndexStateFlagsAction action);
 
 extern Oid	IndexGetRelation(Oid indexId, bool missing_ok);
 
-extern void reindex_index(Oid indexId, bool skip_constraint_checks,
+extern void reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks,
 						  char persistence, const ReindexParams *params);
 
 /* Flag bits for reindex_relation(): */
@@ -159,7 +159,7 @@ extern void reindex_index(Oid indexId, bool skip_constraint_checks,
 #define REINDEX_REL_FORCE_INDEXES_UNLOGGED	0x08
 #define REINDEX_REL_FORCE_INDEXES_PERMANENT 0x10
 
-extern bool reindex_relation(Oid relid, int flags, const ReindexParams *params);
+extern bool reindex_relation(const ReindexStmt *stmt, Oid relid, int flags, const ReindexParams *params);
 
 extern bool ReindexIsProcessingHeap(Oid heapOid);
 extern bool ReindexIsProcessingIndex(Oid indexOid);
-- 
2.34.1

From cdf69292a19c76a1e15d331a64e732eabf384dfd Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universality@gmail.com>
Date: Tue, 28 Nov 2023 00:12:29 +0800
Subject: [PATCH v6 4/4] make Event trigger support reindex command.

documentation event trigger suppport index, regress test.
also mark reindex_event_trigger_collect as extern,
since we use reindex_event_trigger_collect both in indexcmds.c and index.c.
---
 doc/src/sgml/event-trigger.sgml             |   8 ++
 src/backend/catalog/index.c                 |  15 +++
 src/backend/commands/indexcmds.c            |   4 +
 src/include/catalog/index.h                 |   2 +-
 src/test/regress/expected/event_trigger.out | 103 ++++++++++++++++++++
 src/test/regress/sql/event_trigger.sql      |  93 ++++++++++++++++++
 6 files changed, 224 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 10b20f03..234b4ffd 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -1032,6 +1032,14 @@
         <entry align="center"><literal>-</literal></entry>
         <entry align="left"></entry>
        </row>
+       <row>
+        <entry align="left"><literal>REINDEX</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>X</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="center"><literal>-</literal></entry>
+        <entry align="left"></entry>
+       </row>
        <row>
         <entry align="left"><literal>REVOKE</literal></entry>
         <entry align="center"><literal>X</literal></entry>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 88ff8c5a..2af9efb8 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -3554,6 +3554,18 @@ IndexGetRelation(Oid indexId, bool missing_ok)
 	return result;
 }
 
+void
+reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt)
+{
+	ObjectAddress address;
+
+	address.classId = RelationRelationId;
+	address.objectId = oid;
+	address.objectSubId = 0;
+
+	EventTriggerCollectSimpleCommand(address,
+									 InvalidObjectAddress, (Node *) stmt);
+}
 /*
  * reindex_index - This routine is used to recreate a single index
  */
@@ -3629,6 +3641,9 @@ reindex_index(const ReindexStmt *stmt, Oid indexId, bool skip_constraint_checks,
 	if (progress)
 		pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
 									 iRel->rd_rel->relam);
+	/* event trigger tracking REINDEX primary pointer */
+	if (stmt)
+		reindex_event_trigger_collect(indexId, stmt);
 
 	/*
 	 * Partitioned indexes should never get processed here, as they have no
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index f47a4aa7..fa874307 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -3816,6 +3816,10 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
 
 		newIndexIds = lappend(newIndexIds, newidx);
 
+		/* Add the index to event trigger */
+		if (stmt)
+			reindex_event_trigger_collect(newIndexId, stmt);
+
 		/*
 		 * Save lockrelid to protect each relation from drop then close
 		 * relations. The lockrelid on parent relation is not taken here to
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index e1272662..6f757f77 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -170,7 +170,7 @@ extern void SerializeReindexState(Size maxsize, char *start_address);
 extern void RestoreReindexState(const void *reindexstate);
 
 extern void IndexSetParentIndex(Relation partitionIdx, Oid parentOid);
-
+extern void reindex_event_trigger_collect(Oid oid, const ReindexStmt *stmt);
 
 /*
  * itemptr_encode - Encode ItemPointer as int64/int8
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 0b87a42d..266cccec 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -556,6 +556,109 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+NOTICE:  schema "schema_to_reindex" does not exist, skipping
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+	obj record;
+	toast_main_table text;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        IF  obj.schema_name = 'pg_toast' THEN
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                          ,obj.object_type, obj.schema_name;
+            /* get the toast table will be reindexed main table.
+             * toast table name auto generated, cannot use to test.
+            */
+            SELECT t1.relname into toast_main_table
+            FROM (
+                SELECT t1.oid
+                FROM pg_class t1 INNER JOIN pg_index t2 ON t1.oid = t2.indrelid
+                WHERE t2.indexrelid = obj.objid AND relkind = 't') sub
+                INNER JOIN pg_class t1 ON t1.reltoastrelid = sub.oid;
+
+            RAISE NOTICE 'toast table related main relation: %', toast_main_table;
+        ELSE
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        END IF;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+-- event trigger disabled.
+--so reindex_start_command part will invoke, reindex_end_command won't.
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg1
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg2
+/* REINDEX SCHEMA (CONCURRENTLY) generated test result order unstable,
+ * So just do normal reindex schema. should cover reindex statement most cases.
+*/
+REINDEX SCHEMA  schema_to_reindex;
+NOTICE:  ddl_start_command -- REINDEX: ddl_command_start REINDEX
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX concur_reindex_ind1 ON schema_to_reindex.concur_reindex_tab USING btree (c1)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind2 ON schema_to_reindex.concur_reindex_tab USING btree (c2)
+NOTICE:  ddl_end_command -- REINDEX: CREATE INDEX concur_reindex_ind4 ON schema_to_reindex.concur_reindex_tab USING btree (c1, c1, c2)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: concur_reindex_tab
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg1_pkey ON schema_to_reindex.parted1_irreg1 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg1
+NOTICE:  ddl_end_command -- REINDEX: CREATE UNIQUE INDEX parted1_irreg2_pkey ON schema_to_reindex.parted1_irreg2 USING btree (b)
+NOTICE:  reindexing toast related index! object_type: index, schema_name: pg_toast
+NOTICE:  toast table related main relation: parted1_irreg2
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table concur_reindex_tab
+drop cascades to table parted_irreg_ancestor
+RESET search_path;
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 6f0933b9..ace58308 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -418,6 +418,99 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+--reindex command, event trigger test setup.
+DROP SCHEMA IF EXISTS schema_to_reindex CASCADE;
+CREATE SCHEMA schema_to_reindex;
+SET search_path TO schema_to_reindex;
+
+CREATE TABLE concur_reindex_tab (c1 int,c2 text);
+CREATE UNIQUE INDEX concur_reindex_ind1 ON concur_reindex_tab (c1);
+CREATE INDEX concur_reindex_ind2 ON concur_reindex_tab (c2);
+CREATE INDEX concur_reindex_ind4 ON concur_reindex_tab (c1, c1, c2);
+ALTER TABLE concur_reindex_tab ADD PRIMARY KEY USING INDEX concur_reindex_ind1;
+INSERT INTO concur_reindex_tab VALUES (1, 'a'),(2, 'a');
+
+CREATE TABLE parted_irreg_ancestor (b text,a int) PARTITION BY RANGE (b);
+CREATE TABLE parted_irreg (a int,b text) PARTITION BY RANGE (b);
+
+ALTER TABLE parted_irreg_ancestor ATTACH PARTITION parted_irreg
+FOR VALUES FROM ('aaaa') TO ('zzzz');
+
+CREATE TABLE parted1_irreg1 (b text NOT NULL,a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg1
+FOR VALUES FROM ('aaaa') TO ('jjjj');
+
+CREATE TABLE parted1_irreg2 (b text NOT NULL, a int);
+ALTER TABLE parted_irreg ATTACH PARTITION parted1_irreg2
+FOR VALUES FROM ('jjjj') TO ('zzzz');
+
+INSERT INTO parted_irreg_ancestor (b)
+VALUES ('daasvog'),('asdhjksd'),('sssdjk'),('jssdjk');
+
+ALTER TABLE parted_irreg_ancestor ADD PRIMARY KEY (b);
+
+CREATE OR REPLACE FUNCTION public.reindex_start_command()
+RETURNS event_trigger AS $$
+BEGIN
+    RAISE NOTICE 'ddl_start_command -- REINDEX: % %', tg_event, tg_tag;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER start_reindex_command ON ddl_command_start
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_start_command();
+
+CREATE OR REPLACE FUNCTION public.reindex_end_command()
+RETURNS event_trigger AS $$
+DECLARE
+	obj record;
+	toast_main_table text;
+BEGIN
+FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
+    LOOP
+        IF  obj.schema_name = 'pg_toast' THEN
+            RAISE NOTICE 'reindexing toast related index! object_type: %, schema_name: %'
+                          ,obj.object_type, obj.schema_name;
+            /* get the toast table will be reindexed main table.
+             * toast table name auto generated, cannot use to test.
+            */
+            SELECT t1.relname into toast_main_table
+            FROM (
+                SELECT t1.oid
+                FROM pg_class t1 INNER JOIN pg_index t2 ON t1.oid = t2.indrelid
+                WHERE t2.indexrelid = obj.objid AND relkind = 't') sub
+                INNER JOIN pg_class t1 ON t1.reltoastrelid = sub.oid;
+
+            RAISE NOTICE 'toast table related main relation: %', toast_main_table;
+        ELSE
+            RAISE NOTICE 'ddl_end_command -- REINDEX: %', pg_get_indexdef(obj.objid);
+        END IF;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE EVENT TRIGGER end_reindex_command ON ddl_command_end
+    WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE public.reindex_end_command();
+
+REINDEX (CONCURRENTLY) INDEX  parted_irreg_ancestor_pkey;
+ALTER EVENT TRIGGER end_reindex_command DISABLE;
+
+-- event trigger disabled.
+--so reindex_start_command part will invoke, reindex_end_command won't.
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+
+ALTER EVENT TRIGGER end_reindex_command ENABLE;
+REINDEX (CONCURRENTLY) TABLE  parted_irreg_ancestor;
+
+/* REINDEX SCHEMA (CONCURRENTLY) generated test result order unstable,
+ * So just do normal reindex schema. should cover reindex statement most cases.
+*/
+REINDEX SCHEMA  schema_to_reindex;
+
+drop event trigger if exists  start_reindex_command,end_reindex_command;
+drop function if exists  public.reindex_end_command, public.reindex_start_command cascade;
+drop schema if exists schema_to_reindex cascade;
+RESET search_path;
+
 -- test Row Security Event Trigger
 RESET SESSION AUTHORIZATION;
 CREATE TABLE event_trigger_test (a integer, b text);
-- 
2.34.1

diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 412e1ba84f..4ee498d985 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -3347,6 +3347,8 @@ ReindexMultipleInternal(const ReindexStmt *stmt, const List *relids, const Reind
 
 			newparams.options |= REINDEXOPT_MISSING_OK;
 			(void) ReindexRelationConcurrently(stmt, relid, &newparams);
+			if (ActiveSnapshotSet())
+				PopActiveSnapshot();
 			/* ReindexRelationConcurrently() does the verbose output */
 		}
 		else if (relkind == RELKIND_INDEX)
@@ -3698,10 +3700,7 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
 	 * session until this operation completes.
 	 */
 	if (indexIds == NIL)
-	{
-		PopActiveSnapshot();
 		return false;
-	}
 
 	/* It's not a shared catalog, so refuse to move it to shared tablespace */
 	if (params->tablespaceOid == GLOBALTABLESPACE_OID)
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index fdcd127945..a6ce337be5 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -581,6 +581,36 @@ $$ LANGUAGE plpgsql;
 CREATE EVENT TRIGGER regress_reindex_end ON ddl_command_end
     WHEN TAG IN ('REINDEX')
     EXECUTE PROCEDURE reindex_end_command();
+-- Extra event to force the use of a snapshot.
+CREATE FUNCTION reindex_end_command_snap() RETURNS EVENT_TRIGGER
+  AS $$ BEGIN PERFORM 1; END $$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER regress_reindex_end_snap ON ddl_command_end
+  EXECUTE FUNCTION reindex_end_command_snap();
+-- With a Schema
+CREATE SCHEMA concur_reindex_schema;
+-- No indexes
+REINDEX SCHEMA concur_reindex_schema;
+NOTICE:  REINDEX START: ddl_command_start REINDEX
+REINDEX SCHEMA CONCURRENTLY concur_reindex_schema;
+NOTICE:  REINDEX START: ddl_command_start REINDEX
+CREATE TABLE concur_reindex_schema.tab (a int);
+CREATE INDEX ind ON concur_reindex_schema.tab (a);
+-- One index reported
+REINDEX SCHEMA concur_reindex_schema;
+NOTICE:  REINDEX START: ddl_command_start REINDEX
+NOTICE:  REINDEX END: command_tag=REINDEX type=index identity=concur_reindex_schema.ind
+REINDEX SCHEMA CONCURRENTLY concur_reindex_schema;
+NOTICE:  REINDEX START: ddl_command_start REINDEX
+NOTICE:  REINDEX END: command_tag=REINDEX type=index identity=concur_reindex_schema.ind
+-- One table on schema but no indexes
+DROP INDEX concur_reindex_schema.ind;
+REINDEX SCHEMA concur_reindex_schema;
+NOTICE:  REINDEX START: ddl_command_start REINDEX
+REINDEX SCHEMA CONCURRENTLY concur_reindex_schema;
+NOTICE:  REINDEX START: ddl_command_start REINDEX
+DROP SCHEMA concur_reindex_schema CASCADE;
+NOTICE:  drop cascades to table concur_reindex_schema.tab
+-- With relation
 CREATE TABLE concur_reindex_tab (c1 int);
 CREATE INDEX concur_reindex_ind ON concur_reindex_tab (c1);
 -- Both start and end triggers enabled.
@@ -602,10 +632,18 @@ REINDEX INDEX concur_reindex_ind;
 NOTICE:  REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_ind
 REINDEX INDEX CONCURRENTLY concur_reindex_ind;
 NOTICE:  REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_ind
+-- without an index
+DROP INDEX concur_reindex_ind;
+REINDEX TABLE concur_reindex_tab;
+NOTICE:  table "concur_reindex_tab" has no indexes to reindex
+REINDEX TABLE CONCURRENTLY concur_reindex_tab;
+NOTICE:  table "concur_reindex_tab" has no indexes that can be reindexed concurrently
 -- Clean up
 DROP EVENT TRIGGER regress_reindex_start;
 DROP EVENT TRIGGER regress_reindex_end;
+DROP EVENT TRIGGER regress_reindex_end_snap;
 DROP FUNCTION reindex_end_command();
+DROP FUNCTION reindex_end_command_snap();
 DROP FUNCTION reindex_start_command();
 DROP TABLE concur_reindex_tab;
 -- test Row Security Event Trigger
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index e5c8bf8412..18bac8820d 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -443,7 +443,29 @@ $$ LANGUAGE plpgsql;
 CREATE EVENT TRIGGER regress_reindex_end ON ddl_command_end
     WHEN TAG IN ('REINDEX')
     EXECUTE PROCEDURE reindex_end_command();
+-- Extra event to force the use of a snapshot.
+CREATE FUNCTION reindex_end_command_snap() RETURNS EVENT_TRIGGER
+  AS $$ BEGIN PERFORM 1; END $$ LANGUAGE plpgsql;
+CREATE EVENT TRIGGER regress_reindex_end_snap ON ddl_command_end
+  EXECUTE FUNCTION reindex_end_command_snap();
 
+-- With a Schema
+CREATE SCHEMA concur_reindex_schema;
+-- No indexes
+REINDEX SCHEMA concur_reindex_schema;
+REINDEX SCHEMA CONCURRENTLY concur_reindex_schema;
+CREATE TABLE concur_reindex_schema.tab (a int);
+CREATE INDEX ind ON concur_reindex_schema.tab (a);
+-- One index reported
+REINDEX SCHEMA concur_reindex_schema;
+REINDEX SCHEMA CONCURRENTLY concur_reindex_schema;
+-- One table on schema but no indexes
+DROP INDEX concur_reindex_schema.ind;
+REINDEX SCHEMA concur_reindex_schema;
+REINDEX SCHEMA CONCURRENTLY concur_reindex_schema;
+DROP SCHEMA concur_reindex_schema CASCADE;
+
+-- With relation
 CREATE TABLE concur_reindex_tab (c1 int);
 CREATE INDEX concur_reindex_ind ON concur_reindex_tab (c1);
 -- Both start and end triggers enabled.
@@ -455,11 +477,17 @@ REINDEX TABLE CONCURRENTLY concur_reindex_tab;
 ALTER EVENT TRIGGER regress_reindex_start DISABLE;
 REINDEX INDEX concur_reindex_ind;
 REINDEX INDEX CONCURRENTLY concur_reindex_ind;
+-- without an index
+DROP INDEX concur_reindex_ind;
+REINDEX TABLE concur_reindex_tab;
+REINDEX TABLE CONCURRENTLY concur_reindex_tab;
 
 -- Clean up
 DROP EVENT TRIGGER regress_reindex_start;
 DROP EVENT TRIGGER regress_reindex_end;
+DROP EVENT TRIGGER regress_reindex_end_snap;
 DROP FUNCTION reindex_end_command();
+DROP FUNCTION reindex_end_command_snap();
 DROP FUNCTION reindex_start_command();
 DROP TABLE concur_reindex_tab;