From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Rahila Syed <rahila.syed@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Cc: David Fetter <david@fetter.org>
Cc: Pavan Deolasee <pavan.deolasee@gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Subject: Re: monitoring CREATE INDEX [CONCURRENTLY]
Date: Fri, 5 Apr 2019 22:46:28 +0200
Message-ID: <41c78a9f-da1d-7f3a-48a6-3fa21d75cae4@2ndquadrant.com> (raw)
In-Reply-To: <20190405150102.GA2616@alvherre.pgsql>
References: <20190405150102.GA2616@alvherre.pgsql>
On 2019-04-05 17:01, Alvaro Herrera wrote:
> Users are going to wonder why the other phases don't appear to complete
> for a long time :-) Keep in mind that the "waiting" phases are very
> confusing to users. I suggest we just define additional phase numbers
> for those phases, then switch the "false" argument to
> WaitForLockersMultiple to "true", and it should work :-) Doc-wise, list
> all the phases in the same docbook table, indicate that REINDEX is also
> covered, and document in an easier-to-follow fashion which phases each
> command goes through.
Done in the attached patch.
I've reworded the phases a bit. There was a bit of a mixup of waiting
for snapshots and waiting for lockers. Perhaps not so important from a
user's perspective, but at least now it's more consistent with the
source code comments.
> Yeah, I think that's simple enough -- the CLUSTER one already does that,
> I think.
Added that.
> Another thing for REINDEX TABLE is that we should add a count
> of indexes to process, and how many are done.
Reasonable, but maybe a bit too much for the last moment.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 22c0254b76be05a2235ed84268d189fa6785e844 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Fri, 5 Apr 2019 22:27:14 +0200
Subject: [PATCH v2] Report progress of REINDEX operations
This uses the same infrastructure that the CREATE INDEX progress
reporting uses.
---
doc/src/sgml/monitoring.sgml | 52 +++++++++++++++++++++-------
src/backend/catalog/index.c | 10 ++++++
src/backend/catalog/system_views.sql | 11 +++---
src/backend/commands/indexcmds.c | 39 ++++++++++++++++++---
src/include/commands/progress.h | 3 ++
src/test/regress/expected/rules.out | 11 +++---
6 files changed, 102 insertions(+), 24 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgmlindex b946e13fdc..4eb43f2de9 100644--- a/doc/src/sgml/monitoring.sgml+++ b/doc/src/sgml/monitoring.sgml@@ -346,7 +346,7 @@ <title>Dynamic Statistics Views</title>
<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 @@ <title>Progress Reporting</title>
<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 @@ <title><structname>pg_stat_progress_create_index</structname> View</title>
<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 @@ <title>CREATE INDEX phases</title>
<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 @@ <title>CREATE INDEX phases</title>
</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 @@ <title>CREATE INDEX phases</title>
</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 @@ <title>CREATE INDEX phases</title>
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>
@@ -3937,7 +3965,7 @@ <title><structname>pg_stat_progress_cluster</structname> View</title>
</row>
<row>
<entry><structfield>cluster_index_relid</structfield></entry>
- <entry><type>bigint</type></entry>+ <entry><type>oid</type></entry>
<entry>
If the table is being scanned using an index, this is the OID of the
index being used; otherwise, it is zero.
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 2ed7fdb021..9b1d546791 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.sqlindex 72f786d6f8..16e456a7d9 100644--- a/src/backend/catalog/system_views.sql+++ b/src/backend/catalog/system_views.sql@@ -933,7 +933,7 @@ CREATE VIEW pg_stat_progress_cluster AS
WHEN 6 THEN 'rebuilding index'
WHEN 7 THEN 'performing final cleanup'
END AS phase,
- S.param3 AS cluster_index_relid,+ CAST(S.param3 AS oid) AS cluster_index_relid,
S.param4 AS heap_tuples_scanned,
S.param5 AS heap_tuples_written,
S.param6 AS heap_blks_total,
@@ -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.cindex 348d543297..4a9b030c2c 100644--- 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,6 +3080,8 @@ ReindexRelationConcurrently(Oid relationOid, int options)
* before the reference snap was taken, we have to wait out any
* transactions that might have older snapshots.
*/
+ pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,+ PROGRESS_CREATEIDX_PHASE_WAIT_3);
WaitForOlderSnapshots(limitXmin, false);
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/commands/progress.h b/src/include/commands/progress.hindex f046fa13b1..37043e926d 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.outindex bf7fca54ee..4638374a76 100644--- a/src/test/regress/expected/rules.out+++ b/src/test/regress/expected/rules.out@@ -1855,7 +1855,7 @@ pg_stat_progress_cluster| SELECT s.pid,
WHEN 7 THEN 'performing final cleanup'::text
ELSE NULL::text
END AS phase,
- s.param3 AS cluster_index_relid,+ (s.param3)::oid AS cluster_index_relid,
s.param4 AS heap_tuples_scanned,
s.param5 AS heap_tuples_written,
s.param6 AS heap_blks_total,
@@ -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,
--
2.21.0
Attachments:
[text/plain] v2-0001-Report-progress-of-REINDEX-operations.patch (15.7K, ../41c78a9f-da1d-7f3a-48a6-3fa21d75cae4@2ndquadrant.com/2-v2-0001-Report-progress-of-REINDEX-operations.patch)
download | inline diff:
From 22c0254b76be05a2235ed84268d189fa6785e844 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Fri, 5 Apr 2019 22:27:14 +0200
Subject: [PATCH v2] Report progress of REINDEX operations
This uses the same infrastructure that the CREATE INDEX progress
reporting uses.
---
doc/src/sgml/monitoring.sgml | 52 +++++++++++++++++++++-------
src/backend/catalog/index.c | 10 ++++++
src/backend/catalog/system_views.sql | 11 +++---
src/backend/commands/indexcmds.c | 39 ++++++++++++++++++---
src/include/commands/progress.h | 3 ++
src/test/regress/expected/rules.out | 11 +++---
6 files changed, 102 insertions(+), 24 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgmlindex b946e13fdc..4eb43f2de9 100644--- a/doc/src/sgml/monitoring.sgml+++ b/doc/src/sgml/monitoring.sgml@@ -346,7 +346,7 @@ <title>Dynamic Statistics Views</title>
<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 @@ <title>Progress Reporting</title>
<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 @@ <title><structname>pg_stat_progress_create_index</structname> View</title>
<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 @@ <title>CREATE INDEX phases</title>
<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 @@ <title>CREATE INDEX phases</title>
</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 @@ <title>CREATE INDEX phases</title>
</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 @@ <title>CREATE INDEX phases</title>
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>
@@ -3937,7 +3965,7 @@ <title><structname>pg_stat_progress_cluster</structname> View</title>
</row>
<row>
<entry><structfield>cluster_index_relid</structfield></entry>
- <entry><type>bigint</type></entry>+ <entry><type>oid</type></entry>
<entry>
If the table is being scanned using an index, this is the OID of the
index being used; otherwise, it is zero.
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 2ed7fdb021..9b1d546791 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.sqlindex 72f786d6f8..16e456a7d9 100644--- a/src/backend/catalog/system_views.sql+++ b/src/backend/catalog/system_views.sql@@ -933,7 +933,7 @@ CREATE VIEW pg_stat_progress_cluster AS
WHEN 6 THEN 'rebuilding index'
WHEN 7 THEN 'performing final cleanup'
END AS phase,
- S.param3 AS cluster_index_relid,+ CAST(S.param3 AS oid) AS cluster_index_relid,
S.param4 AS heap_tuples_scanned,
S.param5 AS heap_tuples_written,
S.param6 AS heap_blks_total,
@@ -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.cindex 348d543297..4a9b030c2c 100644--- 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,6 +3080,8 @@ ReindexRelationConcurrently(Oid relationOid, int options)
* before the reference snap was taken, we have to wait out any
* transactions that might have older snapshots.
*/
+ pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,+ PROGRESS_CREATEIDX_PHASE_WAIT_3);
WaitForOlderSnapshots(limitXmin, false);
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/commands/progress.h b/src/include/commands/progress.hindex f046fa13b1..37043e926d 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.outindex bf7fca54ee..4638374a76 100644--- a/src/test/regress/expected/rules.out+++ b/src/test/regress/expected/rules.out@@ -1855,7 +1855,7 @@ pg_stat_progress_cluster| SELECT s.pid,
WHEN 7 THEN 'performing final cleanup'::text
ELSE NULL::text
END AS phase,
- s.param3 AS cluster_index_relid,+ (s.param3)::oid AS cluster_index_relid,
s.param4 AS heap_tuples_scanned,
s.param5 AS heap_tuples_written,
s.param6 AS heap_blks_total,
@@ -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,
--
2.21.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, alvherre@2ndquadrant.com, rahila.syed@2ndquadrant.com, andres@anarazel.de, robertmhaas@gmail.com, simon@2ndquadrant.com, pgsql-hackers@lists.postgresql.org, david@fetter.org, pavan.deolasee@gmail.com, Langote_Amit_f8@lab.ntt.co.jp
Subject: Re: monitoring CREATE INDEX [CONCURRENTLY]
In-Reply-To: <41c78a9f-da1d-7f3a-48a6-3fa21d75cae4@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox