Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit03f9e5c

Browse files
committed
Report progress of REINDEX operations
This uses the same infrastructure that the CREATE INDEX progressreporting uses. Add a column to pg_stat_progress_create_index toreport the OID of the index being worked on. This was not necessaryfor CREATE INDEX, but it's useful for REINDEX.Also edit the phase descriptions a bit to be more consistent with thesource code comments.Discussion:https://www.postgresql.org/message-id/ef6a6757-c36a-9e81-123f-13b19e36b7d7%402ndquadrant.com
1 parent106f2eb commit03f9e5c

File tree

7 files changed

+101
-23
lines changed

7 files changed

+101
-23
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 39 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -346,7 +346,7 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
346346

347347
<row>
348348
<entry><structname>pg_stat_progress_create_index</structname><indexterm><primary>pg_stat_progress_create_index</primary></indexterm></entry>
349-
<entry>One row for each backend running <command>CREATE INDEX</command>, showing
349+
<entry>One row for each backend running <command>CREATE INDEX</command> or <command>REINDEX</command>, showing
350350
current progress.
351351
See <xref linkend='create-index-progress-reporting'/>.
352352
</entry>
@@ -3477,7 +3477,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
34773477
<title>CREATE INDEX Progress Reporting</title>
34783478

34793479
<para>
3480-
Whenever <command>CREATE INDEX</command> is running, the
3480+
Whenever <command>CREATE INDEX</command>or <command>REINDEX</command>is running, the
34813481
<structname>pg_stat_progress_create_index</structname> view will contain
34823482
one row for each backend that is currently creating indexes. The tables
34833483
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,
35163516
<entry><type>oid</type></entry>
35173517
<entry>OID of the table on which the index is being created.</entry>
35183518
</row>
3519+
<row>
3520+
<entry><structfield>index_relid</structfield></entry>
3521+
<entry><type>oid</type></entry>
3522+
<entry>OID of the index being created or reindexed. During a
3523+
non-concurrent <command>CREATE INDEX</command>, this is 0.</entry>
3524+
</row>
35193525
<row>
35203526
<entry><structfield>phase</structfield></entry>
35213527
<entry><type>text</type></entry>
@@ -3605,15 +3611,15 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
36053611
<row>
36063612
<entry><literal>initializing</literal></entry>
36073613
<entry>
3608-
<command>CREATE INDEX</command> is preparing to create the index. This
3614+
<command>CREATE INDEX</command>or <command>REINDEX</command>is preparing to create the index. This
36093615
phase is expected to be very brief.
36103616
</entry>
36113617
</row>
36123618
<row>
3613-
<entry><literal>waiting forold snapshots</literal></entry>
3619+
<entry><literal>waiting forwriters before build</literal></entry>
36143620
<entry>
3615-
<command>CREATE INDEX CONCURRENTLY</command> is waiting for transactions
3616-
that can potentially see the table torelease their snapshots.
3621+
<command>CREATE INDEX CONCURRENTLY</command>or <command>REINDEX CONCURRENTLY</command>is waiting for transactions
3622+
with write locksthat can potentially see the table tofinish.
36173623
This phase is skipped when not in concurrent mode.
36183624
Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
36193625
and <structname>current_locker_pid</structname> contain the progress
@@ -3632,10 +3638,10 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
36323638
</entry>
36333639
</row>
36343640
<row>
3635-
<entry><literal>waiting forwriter snapshots</literal></entry>
3641+
<entry><literal>waiting forwriters before validation</literal></entry>
36363642
<entry>
3637-
<command>CREATE INDEX CONCURRENTLY</command> is waiting for transactions
3638-
that can potentially write into the table torelease their snapshots.
3643+
<command>CREATE INDEX CONCURRENTLY</command>or <command>REINDEX CONCURRENTLY</command>is waiting for transactions
3644+
with write locksthat can potentially write into the table tofinish.
36393645
This phase is skipped when not in concurrent mode.
36403646
Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
36413647
and <structname>current_locker_pid</structname> contain the progress
@@ -3670,16 +3676,38 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
36703676
</entry>
36713677
</row>
36723678
<row>
3673-
<entry><literal>waiting forreader snapshots</literal></entry>
3679+
<entry><literal>waiting forold snapshots</literal></entry>
36743680
<entry>
3675-
<command>CREATE INDEX CONCURRENTLY</command> is waiting for transactions
3681+
<command>CREATE INDEX CONCURRENTLY</command>or <command>REINDEX CONCURRENTLY</command>is waiting for transactions
36763682
that can potentially see the table to release their snapshots. This
36773683
phase is skipped when not in concurrent mode.
36783684
Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
36793685
and <structname>current_locker_pid</structname> contain the progress
36803686
information for this phase.
36813687
</entry>
36823688
</row>
3689+
<row>
3690+
<entry><literal>waiting for readers before marking dead</literal></entry>
3691+
<entry>
3692+
<command>REINDEX CONCURRENTLY</command> is waiting for transactions
3693+
with read locks on the table to finish, before marking the old index dead.
3694+
This phase is skipped when not in concurrent mode.
3695+
Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
3696+
and <structname>current_locker_pid</structname> contain the progress
3697+
information for this phase.
3698+
</entry>
3699+
</row>
3700+
<row>
3701+
<entry><literal>waiting for readers before dropping</literal></entry>
3702+
<entry>
3703+
<command>REINDEX CONCURRENTLY</command> is waiting for transactions
3704+
with read locks on the table to finish, before dropping the old index.
3705+
This phase is skipped when not in concurrent mode.
3706+
Columns <structname>lockers_total</structname>, <structname>lockers_done</structname>
3707+
and <structname>current_locker_pid</structname> contain the progress
3708+
information for this phase.
3709+
</entry>
3710+
</row>
36833711
</tbody>
36843712
</tgroup>
36853713
</table>

‎src/backend/catalog/index.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3286,12 +3286,20 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
32863286
heapId=IndexGetRelation(indexId, false);
32873287
heapRelation=table_open(heapId,ShareLock);
32883288

3289+
pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
3290+
heapId);
3291+
pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
3292+
indexId);
3293+
32893294
/*
32903295
* Open the target index relation and get an exclusive lock on it, to
32913296
* ensure that no one else is touching this particular index.
32923297
*/
32933298
iRel=index_open(indexId,AccessExclusiveLock);
32943299

3300+
pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
3301+
iRel->rd_rel->relam);
3302+
32953303
/*
32963304
* The case of reindexing partitioned tables and indexes is handled
32973305
* differently by upper layers, so this case shouldn't arise.
@@ -3442,6 +3450,8 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
34423450
errdetail_internal("%s",
34433451
pg_rusage_show(&ru0))));
34443452

3453+
pgstat_progress_end_command();
3454+
34453455
/* Close rels, but keep locks */
34463456
index_close(iRel,NoLock);
34473457
table_close(heapRelation,NoLock);

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -946,16 +946,19 @@ CREATE VIEW pg_stat_progress_create_index AS
946946
SELECT
947947
S.pidAS pid,S.datidAS datid,D.datnameAS datname,
948948
S.relidAS relid,
949+
CAST(S.param7ASoid)AS index_relid,
949950
CASES.param10 WHEN0 THEN'initializing'
950-
WHEN1 THEN'waiting forold snapshots'
951+
WHEN1 THEN'waiting forwriters before build'
951952
WHEN2 THEN'building index'||
952953
COALESCE((':'|| pg_indexam_progress_phasename(S.param9::oid,S.param11)),
953954
'')
954-
WHEN3 THEN'waiting forwriter snapshots'
955+
WHEN3 THEN'waiting forwriters before validation'
955956
WHEN4 THEN'index validation: scanning index'
956957
WHEN5 THEN'index validation: sorting tuples'
957958
WHEN6 THEN'index validation: scanning table'
958-
WHEN7 THEN'waiting for reader snapshots'
959+
WHEN7 THEN'waiting for old snapshots'
960+
WHEN8 THEN'waiting for readers before marking dead'
961+
WHEN9 THEN'waiting for readers before dropping'
959962
ENDas phase,
960963
S.param4AS lockers_total,
961964
S.param5AS lockers_done,

‎src/backend/commands/indexcmds.c

Lines changed: 36 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -478,6 +478,12 @@ DefineIndex(Oid relationId,
478478
pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
479479
relationId);
480480

481+
/*
482+
* No index OID to report yet
483+
*/
484+
pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
485+
InvalidOid);
486+
481487
/*
482488
* count key attributes in index
483489
*/
@@ -1244,6 +1250,12 @@ DefineIndex(Oid relationId,
12441250
CommitTransactionCommand();
12451251
StartTransactionCommand();
12461252

1253+
/*
1254+
* The index is now visible, so we can report the OID.
1255+
*/
1256+
pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
1257+
indexRelationId);
1258+
12471259
/*
12481260
* Phase 2 of concurrent index build (see comments for validate_index()
12491261
* for an overview of how this works)
@@ -2873,6 +2885,13 @@ ReindexRelationConcurrently(Oid relationOid, int options)
28732885
heapRel=table_open(indexRel->rd_index->indrelid,
28742886
ShareUpdateExclusiveLock);
28752887

2888+
pgstat_progress_start_command(PROGRESS_COMMAND_CREATE_INDEX,
2889+
RelationGetRelid(heapRel));
2890+
pgstat_progress_update_param(PROGRESS_CREATEIDX_INDEX_OID,
2891+
indexId);
2892+
pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID,
2893+
indexRel->rd_rel->relam);
2894+
28762895
/* Choose a temporary relation name for the new index */
28772896
concurrentName=ChooseRelationName(get_rel_name(indexId),
28782897
NULL,
@@ -2967,7 +2986,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
29672986
* DefineIndex() for more details.
29682987
*/
29692988

2970-
WaitForLockersMultiple(lockTags,ShareLock, false);
2989+
pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
2990+
PROGRESS_CREATEIDX_PHASE_WAIT_1);
2991+
WaitForLockersMultiple(lockTags,ShareLock, true);
29712992
CommitTransactionCommand();
29722993

29732994
forboth(lc,indexIds,lc2,newIndexIds)
@@ -3009,7 +3030,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
30093030
* for more details.
30103031
*/
30113032

3012-
WaitForLockersMultiple(lockTags,ShareLock, false);
3033+
pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
3034+
PROGRESS_CREATEIDX_PHASE_WAIT_2);
3035+
WaitForLockersMultiple(lockTags,ShareLock, true);
30133036
CommitTransactionCommand();
30143037

30153038
foreach(lc,newIndexIds)
@@ -3057,7 +3080,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
30573080
* before the reference snap was taken, we have to wait out any
30583081
* transactions that might have older snapshots.
30593082
*/
3060-
WaitForOlderSnapshots(limitXmin, false);
3083+
pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
3084+
PROGRESS_CREATEIDX_PHASE_WAIT_3);
3085+
WaitForOlderSnapshots(limitXmin, true);
30613086

30623087
CommitTransactionCommand();
30633088
}
@@ -3128,7 +3153,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
31283153
* index_drop() for more details.
31293154
*/
31303155

3131-
WaitForLockersMultiple(lockTags,AccessExclusiveLock, false);
3156+
pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
3157+
PROGRESS_CREATEIDX_PHASE_WAIT_4);
3158+
WaitForLockersMultiple(lockTags,AccessExclusiveLock, true);
31323159

31333160
foreach(lc,indexIds)
31343161
{
@@ -3150,7 +3177,9 @@ ReindexRelationConcurrently(Oid relationOid, int options)
31503177
* Drop the old indexes.
31513178
*/
31523179

3153-
WaitForLockersMultiple(lockTags,AccessExclusiveLock, false);
3180+
pgstat_progress_update_param(PROGRESS_CREATEIDX_PHASE,
3181+
PROGRESS_CREATEIDX_PHASE_WAIT_4);
3182+
WaitForLockersMultiple(lockTags,AccessExclusiveLock, true);
31543183

31553184
PushActiveSnapshot(GetTransactionSnapshot());
31563185

@@ -3225,6 +3254,8 @@ ReindexRelationConcurrently(Oid relationOid, int options)
32253254

32263255
MemoryContextDelete(private_context);
32273256

3257+
pgstat_progress_end_command();
3258+
32283259
return true;
32293260
}
32303261

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201904071
56+
#defineCATALOG_VERSION_NO201904072
5757

5858
#endif

‎src/include/commands/progress.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,7 @@
5959

6060
/* Progress parameters for CREATE INDEX */
6161
/* 3, 4 and 5 reserved for "waitfor" metrics */
62+
#definePROGRESS_CREATEIDX_INDEX_OID6
6263
#definePROGRESS_CREATEIDX_ACCESS_METHOD_OID8
6364
#definePROGRESS_CREATEIDX_PHASE9/* AM-agnostic phase # */
6465
#definePROGRESS_CREATEIDX_SUBPHASE10/* phase # filled by AM */
@@ -76,6 +77,8 @@
7677
#definePROGRESS_CREATEIDX_PHASE_VALIDATE_SORT5
7778
#definePROGRESS_CREATEIDX_PHASE_VALIDATE_TABLESCAN6
7879
#definePROGRESS_CREATEIDX_PHASE_WAIT_37
80+
#definePROGRESS_CREATEIDX_PHASE_WAIT_48
81+
#definePROGRESS_CREATEIDX_PHASE_WAIT_59
7982

8083
/*
8184
* Subphases of CREATE INDEX, for index_build.

‎src/test/regress/expected/rules.out

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1867,15 +1867,18 @@ pg_stat_progress_create_index| SELECT s.pid,
18671867
s.datid,
18681868
d.datname,
18691869
s.relid,
1870+
(s.param7)::oid AS index_relid,
18701871
CASE s.param10
18711872
WHEN 0 THEN 'initializing'::text
1872-
WHEN 1 THEN 'waiting forold snapshots'::text
1873+
WHEN 1 THEN 'waiting forwriters before build'::text
18731874
WHEN 2 THEN ('building index'::text || COALESCE((': '::text || pg_indexam_progress_phasename((s.param9)::oid, s.param11)), ''::text))
1874-
WHEN 3 THEN 'waiting forwriter snapshots'::text
1875+
WHEN 3 THEN 'waiting forwriters before validation'::text
18751876
WHEN 4 THEN 'index validation: scanning index'::text
18761877
WHEN 5 THEN 'index validation: sorting tuples'::text
18771878
WHEN 6 THEN 'index validation: scanning table'::text
1878-
WHEN 7 THEN 'waiting for reader snapshots'::text
1879+
WHEN 7 THEN 'waiting for old snapshots'::text
1880+
WHEN 8 THEN 'waiting for readers before marking dead'::text
1881+
WHEN 9 THEN 'waiting for readers before dropping'::text
18791882
ELSE NULL::text
18801883
END AS phase,
18811884
s.param4 AS lockers_total,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp