Hi Rafia!
On 2019/03/18 20:42, Rafia Sabih wrote:
On Fri, 8 Mar 2019 at 09:14, Tatsuro Yamada
<yamada.tats...@lab.ntt.co.jp> wrote:
Attached file is rebased patch on current HEAD.
I changed a status. :)
Looks like the patch needs a rebase.
I was on the commit fb5806533f9fe0433290d84c9b019399cd69e9c2
PFA reject file in case you want to have a look.
Thanks for testing it. :)
I rebased the patch on the current head:
f2004f19ed9c9228d3ea2b12379ccb4b9212641f.
Please find attached file.
Also, I share my test case of progress monitor below.
=== My test case ===
[Terminal1]
Run this query on psql:
\a \t
select * from pg_stat_progress_cluster; \watch 0.05
[Terminal2]
Run these queries on psql:
drop table t1;
create table t1 as select a, random() * 1000 as b from generate_series(0,
999999) a;
create index idx_t1 on t1(a);
create index idx_t1_b on t1(b);
analyze t1;
-- index scan
set enable_seqscan to off;
cluster verbose t1 using idx_t1;
-- seq scan
set enable_seqscan to on;
set enable_indexscan to off;
cluster verbose t1 using idx_t1;
-- only given table name to cluster command
cluster verbose t1;
-- only cluster command
cluster verbose;
-- vacuum full
vacuum full t1;
-- vacuum full
vacuum full;
====================
Regards,
Tatsuro Yamada
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index c339a2bb77..8a634dd57e 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -52,6 +52,7 @@
#include "catalog/storage.h"
#include "commands/tablecmds.h"
#include "commands/event_trigger.h"
+#include "commands/progress.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "miscadmin.h"
@@ -59,6 +60,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
#include "parser/parser.h"
+#include "pgstat.h"
#include "rewrite/rewriteManip.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
@@ -3851,6 +3853,7 @@ reindex_relation(Oid relid, int flags, int options)
List *indexIds;
bool is_pg_class;
bool result;
+ int i;
/*
* Open and lock the relation. ShareLock is sufficient since we only need
@@ -3938,6 +3941,7 @@ reindex_relation(Oid relid, int flags, int options)
/* Reindex all the indexes. */
doneIndexes = NIL;
+ i = 1;
foreach(indexId, indexIds)
{
Oid indexOid = lfirst_oid(indexId);
@@ -3955,6 +3959,11 @@ reindex_relation(Oid relid, int flags, int options)
if (is_pg_class)
doneIndexes = lappend_oid(doneIndexes, indexOid);
+
+ /* Set index rebuild count */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_INDEX_REBUILD_COUNT,
+ i);
+ i++;
}
}
PG_CATCH();
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index d962648bc5..87c0092787 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -907,6 +907,32 @@ CREATE VIEW pg_stat_progress_vacuum AS
FROM pg_stat_get_progress_info('VACUUM') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
+CREATE VIEW pg_stat_progress_cluster AS
+ SELECT
+ S.pid AS pid,
+ S.datid AS datid,
+ D.datname AS datname,
+ S.relid AS relid,
+ CASE S.param1 WHEN 1 THEN 'CLUSTER'
+ WHEN 2 THEN 'VACUUM FULL'
+ END AS command,
+ CASE S.param2 WHEN 0 THEN 'initializing'
+ WHEN 1 THEN 'seq scanning heap'
+ WHEN 2 THEN 'index scanning heap'
+ WHEN 3 THEN 'sorting tuples'
+ WHEN 4 THEN 'writing new heap'
+ WHEN 5 THEN 'swapping relation files'
+ WHEN 6 THEN 'rebuilding index'
+ WHEN 7 THEN 'performing final cleanup'
+ END AS phase,
+ S.param3 AS cluster_index_relid,
+ S.param4 AS heap_tuples_scanned,
+ S.param5 AS heap_blks_total,
+ S.param6 AS heap_blks_scanned,
+ S.param7 AS index_rebuild_count
+ FROM pg_stat_get_progress_info('CLUSTER') AS S
+ LEFT JOIN pg_database D ON S.datid = D.oid;
+
CREATE VIEW pg_user_mappings AS
SELECT
U.oid AS umid,
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 3e2a807640..478894c869 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -36,10 +36,12 @@
#include "catalog/objectaccess.h"
#include "catalog/toasting.h"
#include "commands/cluster.h"
+#include "commands/progress.h"
#include "commands/tablecmds.h"
#include "commands/vacuum.h"
#include "miscadmin.h"
#include "optimizer/optimizer.h"
+#include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
@@ -276,6 +278,8 @@ cluster_rel(Oid tableOid, Oid indexOid, int options)
/* Check for user-requested abort. */
CHECK_FOR_INTERRUPTS();
+ pgstat_progress_start_command(PROGRESS_COMMAND_CLUSTER, tableOid);
+
/*
* We grab exclusive access to the target rel and index for the duration
* of the transaction. (This is redundant for the single-transaction
@@ -386,6 +390,18 @@ cluster_rel(Oid tableOid, Oid indexOid, int options)
*/
CheckTableNotInUse(OldHeap, OidIsValid(indexOid) ? "CLUSTER" : "VACUUM");
+ /* Set command to column */
+ if (OidIsValid(indexOid))
+ {
+ pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND,
+ PROGRESS_CLUSTER_COMMAND_CLUSTER);
+ }
+ else
+ {
+ pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND,
+ PROGRESS_CLUSTER_COMMAND_VACUUM_FULL);
+ }
+
/* Check heap and index are valid to cluster on */
if (OidIsValid(indexOid))
check_index_is_clusterable(OldHeap, indexOid, recheck, AccessExclusiveLock);
@@ -416,6 +432,8 @@ cluster_rel(Oid tableOid, Oid indexOid, int options)
rebuild_relation(OldHeap, indexOid, verbose);
/* NB: rebuild_relation does table_close() on OldHeap */
+
+ pgstat_progress_end_command();
}
/*
@@ -928,6 +946,17 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
*/
if (OldIndex != NULL && !use_sort)
{
+ const int ci_index[] = {
+ PROGRESS_CLUSTER_PHASE,
+ PROGRESS_CLUSTER_INDEX_RELID
+ };
+ int64 ci_val[2];
+
+ /* Set phase and OIDOldIndex to columns */
+ ci_val[0] = PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP;
+ ci_val[1] = OIDOldIndex;
+ pgstat_progress_update_multi_param(2, ci_index, ci_val);
+
tableScan = NULL;
heapScan = NULL;
indexScan = index_beginscan(OldHeap, OldIndex, SnapshotAny, 0, 0);
@@ -935,9 +964,17 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
}
else
{
+ /* Set phase */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
+ PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP);
+
tableScan = table_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL);
heapScan = (HeapScanDesc) tableScan;
indexScan = NULL;
+
+ /* Set total heap blocks */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_TOTAL_HEAP_BLKS,
+ heapScan->rs_nblocks);
}
slot = table_slot_create(OldHeap, NULL);
@@ -994,6 +1031,10 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
break;
buf = heapScan->rs_cbuf;
+
+ /* Set heap blocks scanned */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_BLKS_SCANNED,
+ heapScan->rs_cblock);
}
LockBuffer(buf, BUFFER_LOCK_SHARE);
@@ -1070,6 +1111,10 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
oldTupDesc, newTupDesc,
values, isnull,
rwstate);
+
+ /* Regardless of index scan or seq scan, update tuples_scanned column */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED,
+ num_tuples);
}
if (indexScan != NULL)
@@ -1085,8 +1130,29 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
*/
if (tuplesort != NULL)
{
+ double num_tuples = 0;
+ const int cp_index[] = {
+ PROGRESS_CLUSTER_PHASE,
+ PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED,
+ PROGRESS_CLUSTER_TOTAL_HEAP_BLKS,
+ PROGRESS_CLUSTER_HEAP_BLKS_SCANNED
+ };
+ int64 cp_val[4];
+
+ /* Report that we are now sorting tuples */
+ cp_val[0] = PROGRESS_CLUSTER_PHASE_SORT_TUPLES;
+ cp_val[1] = num_tuples;
+ cp_val[2] = 0;
+ cp_val[3] = 0;
+ pgstat_progress_update_multi_param(4, cp_index, cp_val);
+
tuplesort_performsort(tuplesort);
+ /* Report that we are now writing new heap */
+ cp_val[0] = PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP;
+ cp_val[1] = num_tuples;
+ pgstat_progress_update_multi_param(2, cp_index, cp_val);
+
for (;;)
{
HeapTuple tuple;
@@ -1097,10 +1163,14 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
if (tuple == NULL)
break;
+ num_tuples += 1;
reform_and_rewrite_tuple(tuple,
oldTupDesc, newTupDesc,
values, isnull,
rwstate);
+ /* Report num_tuples */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED,
+ num_tuples);
}
tuplesort_end(tuplesort);
@@ -1538,6 +1608,20 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
Oid mapped_tables[4];
int reindex_flags;
int i;
+ const int cp_index[] = {
+ PROGRESS_CLUSTER_PHASE,
+ PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED,
+ PROGRESS_CLUSTER_TOTAL_HEAP_BLKS,
+ PROGRESS_CLUSTER_HEAP_BLKS_SCANNED
+ };
+ int64 cp_val[4];
+
+ /* Report that we are now swapping relation files */
+ cp_val[0] = PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES;
+ cp_val[1] = 0;
+ cp_val[2] = 0;
+ cp_val[3] = 0;
+ pgstat_progress_update_multi_param(4, cp_index, cp_val);
/* Zero out possible results from swapped_relation_files */
memset(mapped_tables, 0, sizeof(mapped_tables));
@@ -1573,6 +1657,11 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
* because the new heap won't contain any HOT chains at all, let alone
* broken ones, so it can't be necessary to set indcheckxmin.
*/
+
+ /* Report that we are now reindexing relations */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
+ PROGRESS_CLUSTER_PHASE_REBUILD_INDEX);
+
reindex_flags = REINDEX_REL_SUPPRESS_INDEX_USE;
if (check_constraints)
reindex_flags |= REINDEX_REL_CHECK_CONSTRAINTS;
@@ -1588,6 +1677,10 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
reindex_relation(OIDOldHeap, reindex_flags, 0);
+ /* Report that we are now doing clean up */
+ pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE,
+ PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP);
+
/*
* If the relation being rebuild is pg_class, swap_relation_files()
* couldn't update pg_class's own pg_class entry (check comments in
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index da1d685c08..a7256dfefa 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -468,6 +468,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
/* Translate command name into command type code. */
if (pg_strcasecmp(cmd, "VACUUM") == 0)
cmdtype = PROGRESS_COMMAND_VACUUM;
+ else if(pg_strcasecmp(cmd, "CLUSTER") == 0)
+ cmdtype = PROGRESS_COMMAND_CLUSTER;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index 9858b36a38..0f637fe4e7 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -34,4 +34,26 @@
#define PROGRESS_VACUUM_PHASE_TRUNCATE 5
#define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP 6
+/* Progress parameters for cluster */
+#define PROGRESS_CLUSTER_COMMAND 0
+#define PROGRESS_CLUSTER_PHASE 1
+#define PROGRESS_CLUSTER_INDEX_RELID 2
+#define PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED 3
+#define PROGRESS_CLUSTER_TOTAL_HEAP_BLKS 4
+#define PROGRESS_CLUSTER_HEAP_BLKS_SCANNED 5
+#define PROGRESS_CLUSTER_INDEX_REBUILD_COUNT 6
+
+/* Phases of cluster (as dvertised via PROGRESS_CLUSTER_PHASE) */
+#define PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP 1
+#define PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP 2
+#define PROGRESS_CLUSTER_PHASE_SORT_TUPLES 3
+#define PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP 4
+#define PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES 5
+#define PROGRESS_CLUSTER_PHASE_REBUILD_INDEX 6
+#define PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP 7
+
+/* Commands of PROGRESS_CLUSTER */
+#define PROGRESS_CLUSTER_COMMAND_CLUSTER 1
+#define PROGRESS_CLUSTER_COMMAND_VACUUM_FULL 2
+
#endif
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ea6cc8b560..c080fa6388 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -950,7 +950,8 @@ typedef enum
typedef enum ProgressCommandType
{
PROGRESS_COMMAND_INVALID,
- PROGRESS_COMMAND_VACUUM
+ PROGRESS_COMMAND_VACUUM,
+ PROGRESS_COMMAND_CLUSTER
} ProgressCommandType;
#define PGSTAT_NUM_PROGRESS_PARAM 10
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f104dc4a62..45ac8085ea 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1830,6 +1830,33 @@ pg_stat_database_conflicts| SELECT d.oid AS datid,
pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin,
pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock
FROM pg_database d;
+pg_stat_progress_cluster| SELECT s.pid,
+ s.datid,
+ d.datname,
+ s.relid,
+ CASE s.param1
+ WHEN 1 THEN 'CLUSTER'::text
+ WHEN 2 THEN 'VACUUM FULL'::text
+ ELSE NULL::text
+ END AS command,
+ CASE s.param2
+ WHEN 0 THEN 'initializing'::text
+ WHEN 1 THEN 'seq scanning heap'::text
+ WHEN 2 THEN 'index scanning heap'::text
+ WHEN 3 THEN 'sorting tuples'::text
+ WHEN 4 THEN 'writing new heap'::text
+ WHEN 5 THEN 'swapping relation files'::text
+ WHEN 6 THEN 'rebuilding index'::text
+ WHEN 7 THEN 'performing final cleanup'::text
+ ELSE NULL::text
+ END AS phase,
+ s.param4 AS cluster_index_relid,
+ s.param5 AS heap_tuples_scanned,
+ s.param6 AS heap_blks_total,
+ s.param7 AS heap_blks_scanned,
+ s.param8 AS index_rebuild_count
+ FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10)
+ LEFT JOIN pg_database d ON ((s.datid = d.oid)));
pg_stat_progress_vacuum| SELECT s.pid,
s.datid,
d.datname,