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

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index c5853da7af07bb0c40d0b26e8f715645ba909c04..4eb43f2de95047287e49f3974ca11502bfb9de7a 100644 (file)
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -346,7 +346,7 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
 
      <row>
       <entry><structname>pg_stat_progress_create_index</structname><indexterm><primary>pg_stat_progress_create_index</primary></indexterm></entry>
-      <entry>One row for each backend running <command>CREATE INDEX</command>, showing
+      <entry>One row for each backend running <command>CREATE INDEX</command> or <command>REINDEX</command>, showing
       current progress.
       See <xref linkend='create-index-progress-reporting'/>.
      </entry>
@@ -3477,7 +3477,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
   <title>CREATE INDEX Progress Reporting</title>
 
   <para>
-   Whenever <command>CREATE INDEX</command> is running, the
+   Whenever <command>CREATE INDEX</command> or <command>REINDEX</command> is running, the
    <structname>pg_stat_progress_create_index</structname> view will contain
    one row for each backend that is currently creating indexes.  The tables
    below describe the information that will be reported and provide information
@@ -3516,6 +3516,12 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       <entry><type>oid</type></entry>
       <entry>OID of the table on which the index is being created.</entry>
      </row>
+     <row>
+      <entry><structfield>index_relid</structfield></entry>
+      <entry><type>oid</type></entry>
+      <entry>OID of the index being created or reindexed.  During a
+      non-concurrent <command>CREATE INDEX</command>, this is 0.</entry>
+     </row>
      <row>
       <entry><structfield>phase</structfield></entry>
       <entry><type>text</type></entry>
@@ -3605,15 +3611,15 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
      <row>
       <entry><literal>initializing</literal></entry>
       <entry>
-       <command>CREATE INDEX</command> is preparing to create the index.  This
+       <command>CREATE INDEX</command> or <command>REINDEX</command> is preparing to create the index.  This
        phase is expected to be very brief.
       </entry>
      </row>
      <row>
-      <entry><literal>waiting for old snapshots</literal></entry>
+      <entry><literal>waiting for writers before build</literal></entry>
       <entry>
-       <command>CREATE INDEX CONCURRENTLY</command> is waiting for transactions
-       that can potentially see the table to release their snapshots.
+       <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX CONCURRENTLY</command> is waiting for transactions
+       with write locks that can potentially see the table to finish.
        This phase is skipped when not in concurrent mode.
        Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
        and <structname>current_locker_pid</structname> contain the progress 
@@ -3632,10 +3638,10 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       </entry>
      </row>
      <row>
-      <entry><literal>waiting for writer snapshots</literal></entry>
+      <entry><literal>waiting for writers before validation</literal></entry>
       <entry>
-       <command>CREATE INDEX CONCURRENTLY</command> is waiting for transactions
-       that can potentially write into the table to release their snapshots.
+       <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX CONCURRENTLY</command> is waiting for transactions
+       with write locks that can potentially write into the table to finish.
        This phase is skipped when not in concurrent mode.
        Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
        and <structname>current_locker_pid</structname> contain the progress 
@@ -3670,9 +3676,9 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       </entry>
      </row>
      <row>
-      <entry><literal>waiting for reader snapshots</literal></entry>
+      <entry><literal>waiting for old snapshots</literal></entry>
       <entry>
-       <command>CREATE INDEX CONCURRENTLY</command> is waiting for transactions
+       <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX CONCURRENTLY</command> is waiting for transactions
        that can potentially see the table to release their snapshots.  This
        phase is skipped when not in concurrent mode.
        Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
@@ -3680,6 +3686,28 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
        information for this phase.
       </entry>
      </row>
+     <row>
+      <entry><literal>waiting for readers before marking dead</literal></entry>
+      <entry>
+       <command>REINDEX CONCURRENTLY</command> is waiting for transactions
+       with read locks on the table to finish, before marking the old index dead.
+       This phase is skipped when not in concurrent mode.
+       Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
+       and <structname>current_locker_pid</structname> contain the progress 
+       information for this phase.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>waiting for readers before dropping</literal></entry>
+      <entry>
+       <command>REINDEX CONCURRENTLY</command> is waiting for transactions
+       with read locks on the table to finish, before dropping the old index.
+       This phase is skipped when not in concurrent mode.
+       Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
+       and <structname>current_locker_pid</structname> contain the progress 
+       information for this phase.
+      </entry>
+     </row>
     </tbody>
    </tgroup>
   </table>

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 2ed7fdb021fe50f1d974be61999aa39af44c9026..9b1d54679179cbfcd8daf21fccb93765a910939e 100644 (file)
--- 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 17e7aef2204d8ad08773d5b2209586b705dea114..16e456a7d9484e1bf5577ae3f4b03661ce3393eb 100644 (file)
--- 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 348d54329779924a4422b441144f8c0e3b331598..46f32c21f97146ed3a0f20f3a3b237c4edf9faeb 100644 (file)
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -478,6 +478,12 @@ DefineIndex(Oid relationId,
        pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
                                      relationId);
 
+   /*
+    * No index OID to report yet
+    */
+   pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
+                                InvalidOid);
+
    /*
     * count key attributes in index
     */
@@ -1244,6 +1250,12 @@ DefineIndex(Oid relationId,
    CommitTransactionCommand();
    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 dcb7cce1e53174aa9ccf6d609d4ff4c251d9770c..116cb5b126969769f7a1c51f8de2fb81564db693 100644 (file)
--- 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 f046fa13b18fb31868a09ae09fe030d402514e98..37043e926d14c76250d1a931dbf9ed9c1d4dd696 100644 (file)
--- 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 22d462f1e5f516a873cdef6d73753dca912517eb..4638374a76ed5b1a1818db973fa36f27f179294b 100644 (file)
--- 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,