Report progress of REINDEX operations

This uses the same infrastructure that the CREATE INDEX progress
reporting uses.  Add a column to pg_stat_progress_create_index to
report the OID of the index being worked on.  This was not necessary
for CREATE INDEX, but it's useful for REINDEX.

Also edit the phase descriptions a bit to be more consistent with the
source code comments.

Discussion: https://www.postgresql.org/message-id/ef6a6757-c36a-9e81-123f-13b19e36b7d7%402ndquadrant.com

6 files changed, 62 insertions, 12 deletions

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 2ed7fdb021f..9b1d5467917 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -3286,12 +3286,20 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 	heapId = IndexGetRelation(indexId, false);
 	heapRelation = table_open(heapId, ShareLock);
 
+	pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
+								  heapId);
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
+								 indexId);
+
 	/*
 	 * Open the target index relation and get an exclusive lock on it, to
 	 * ensure that no one else is touching this particular index.
 	 */
 	iRel = index_open(indexId, AccessExclusiveLock);
 
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
+								 iRel->rd_rel->relam);
+
 	/*
 	 * The case of reindexing partitioned tables and indexes is handled
 	 * differently by upper layers, so this case shouldn't arise.
@@ -3442,6 +3450,8 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
 				 errdetail_internal("%s",
 									pg_rusage_show(&ru0))));
 
+	pgstat_progress_end_command();
+
 	/* Close rels, but keep locks */
 	index_close(iRel, NoLock);
 	table_close(heapRelation, NoLock);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 17e7aef2204..16e456a7d94 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -946,16 +946,19 @@ CREATE VIEW pg_stat_progress_create_index AS
 	SELECT
 		S.pid AS pid, S.datid AS datid, D.datname AS datname,
 		S.relid AS relid,
+		CAST(S.param7 AS oid) AS index_relid,
 		CASE S.param10 WHEN 0 THEN 'initializing'
-					  WHEN 1 THEN 'waiting for old snapshots'
+					  WHEN 1 THEN 'waiting for writers before build'
 					  WHEN 2 THEN 'building index' ||
 						COALESCE((': ' || pg_indexam_progress_phasename(S.param9::oid, S.param11)),
 							'')
-					  WHEN 3 THEN 'waiting for writer snapshots'
+					  WHEN 3 THEN 'waiting for writers before validation'
 					  WHEN 4 THEN 'index validation: scanning index'
 					  WHEN 5 THEN 'index validation: sorting tuples'
 					  WHEN 6 THEN 'index validation: scanning table'
-					  WHEN 7 THEN 'waiting for reader snapshots'
+					  WHEN 7 THEN 'waiting for old snapshots'
+					  WHEN 8 THEN 'waiting for readers before marking dead'
+					  WHEN 9 THEN 'waiting for readers before dropping'
 					  END as phase,
 		S.param4 AS lockers_total,
 		S.param5 AS lockers_done,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 348d5432977..46f32c21f97 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -479,6 +479,12 @@ DefineIndex(Oid relationId,
 									  relationId);
 
 	/*
+	 * No index OID to report yet
+	 */
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
+								 InvalidOid);
+
+	/*
 	 * count key attributes in index
 	 */
 	numberOfKeyAttributes = list_length(stmt->indexParams);
@@ -1245,6 +1251,12 @@ DefineIndex(Oid relationId,
 	StartTransactionCommand();
 
 	/*
+	 * The index is now visible, so we can report the OID.
+	 */
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
+								 indexRelationId);
+
+	/*
 	 * Phase 2 of concurrent index build (see comments for validate_index()
 	 * for an overview of how this works)
 	 *
@@ -2873,6 +2885,13 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 		heapRel = table_open(indexRel->rd_index->indrelid,
 							 ShareUpdateExclusiveLock);
 
+		pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
+									  RelationGetRelid(heapRel));
+		pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
+									 indexId);
+		pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
+									 indexRel->rd_rel->relam);
+
 		/* Choose a temporary relation name for the new index */
 		concurrentName = ChooseRelationName(get_rel_name(indexId),
 											NULL,
@@ -2967,7 +2986,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 	 * DefineIndex() for more details.
 	 */
 
-	WaitForLockersMultiple(lockTags, ShareLock, false);
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
+								 PROGRESS_CREATEIDX_PHASE_WAIT_1);
+	WaitForLockersMultiple(lockTags, ShareLock, true);
 	CommitTransactionCommand();
 
 	forboth(lc, indexIds, lc2, newIndexIds)
@@ -3009,7 +3030,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 	 * for more details.
 	 */
 
-	WaitForLockersMultiple(lockTags, ShareLock, false);
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
+								 PROGRESS_CREATEIDX_PHASE_WAIT_2);
+	WaitForLockersMultiple(lockTags, ShareLock, true);
 	CommitTransactionCommand();
 
 	foreach(lc, newIndexIds)
@@ -3057,7 +3080,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 		 * before the reference snap was taken, we have to wait out any
 		 * transactions that might have older snapshots.
 		 */
-		WaitForOlderSnapshots(limitXmin, false);
+		pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
+									 PROGRESS_CREATEIDX_PHASE_WAIT_3);
+		WaitForOlderSnapshots(limitXmin, true);
 
 		CommitTransactionCommand();
 	}
@@ -3128,7 +3153,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 	 * index_drop() for more details.
 	 */
 
-	WaitForLockersMultiple(lockTags, AccessExclusiveLock, false);
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
+								 PROGRESS_CREATEIDX_PHASE_WAIT_4);
+	WaitForLockersMultiple(lockTags, AccessExclusiveLock, true);
 
 	foreach(lc, indexIds)
 	{
@@ -3150,7 +3177,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 	 * Drop the old indexes.
 	 */
 
-	WaitForLockersMultiple(lockTags, AccessExclusiveLock, false);
+	pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
+								 PROGRESS_CREATEIDX_PHASE_WAIT_4);
+	WaitForLockersMultiple(lockTags, AccessExclusiveLock, true);
 
 	PushActiveSnapshot(GetTransactionSnapshot());
 
@@ -3225,6 +3254,8 @@ ReindexRelationConcurrently(Oid relationOid, int options)
 
 	MemoryContextDelete(private_context);
 
+	pgstat_progress_end_command();
+
 	return true;
 }
 
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index dcb7cce1e53..116cb5b1269 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201904071
+#define CATALOG_VERSION_NO	201904072
 
 #endif
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index f046fa13b18..37043e926d1 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -59,6 +59,7 @@
 
 /* Progress parameters for CREATE INDEX */
 /* 3, 4 and 5 reserved for "waitfor" metrics */
+#define PROGRESS_CREATEIDX_INDEX_OID			6
 #define PROGRESS_CREATEIDX_ACCESS_METHOD_OID	8
 #define PROGRESS_CREATEIDX_PHASE				9	/* AM-agnostic phase # */
 #define PROGRESS_CREATEIDX_SUBPHASE				10	/* phase # filled by AM */
@@ -76,6 +77,8 @@
 #define PROGRESS_CREATEIDX_PHASE_VALIDATE_SORT		5
 #define PROGRESS_CREATEIDX_PHASE_VALIDATE_TABLESCAN	6
 #define PROGRESS_CREATEIDX_PHASE_WAIT_3			7
+#define PROGRESS_CREATEIDX_PHASE_WAIT_4			8
+#define PROGRESS_CREATEIDX_PHASE_WAIT_5			9
 
 /*
  * Subphases of CREATE INDEX, for index_build.
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 22d462f1e5f..4638374a76e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1867,15 +1867,18 @@ pg_stat_progress_create_index| SELECT s.pid,
     s.datid,
     d.datname,
     s.relid,
+    (s.param7)::oid AS index_relid,
         CASE s.param10
             WHEN 0 THEN 'initializing'::text
-            WHEN 1 THEN 'waiting for old snapshots'::text
+            WHEN 1 THEN 'waiting for writers before build'::text
             WHEN 2 THEN ('building index'::text || COALESCE((': '::text || pg_indexam_progress_phasename((s.param9)::oid, s.param11)), ''::text))
-            WHEN 3 THEN 'waiting for writer snapshots'::text
+            WHEN 3 THEN 'waiting for writers before validation'::text
             WHEN 4 THEN 'index validation: scanning index'::text
             WHEN 5 THEN 'index validation: sorting tuples'::text
             WHEN 6 THEN 'index validation: scanning table'::text
-            WHEN 7 THEN 'waiting for reader snapshots'::text
+            WHEN 7 THEN 'waiting for old snapshots'::text
+            WHEN 8 THEN 'waiting for readers before marking dead'::text
+            WHEN 9 THEN 'waiting for readers before dropping'::text
             ELSE NULL::text
         END AS phase,
     s.param4 AS lockers_total,