v3-0001-Add-mode-and-triggered_by-columns-to-pg_stat_prog.patch
application/octet-stream
Filename: v3-0001-Add-mode-and-triggered_by-columns-to-pg_stat_prog.patch
Type: application/octet-stream
Part: 0
From 42de34d76b222645cde46d5da3c45e924ccdc22f Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Thu, 30 Oct 2025 16:16:27 +0900
Subject: [PATCH v3] Add mode and triggered_by columns to
pg_stat_progress_vacuum
This commit introduces the new mode and triggered_by columns, which
specify the vacuum's behavior mode and how the current vacuum was
launched, respectively.
The mode can be one of the following:
- normal
- aggressive
- failsafe
The triggered_by can be one of the following:
- manual
- autovacuum
- autovacuum_wraparound
Bump catalog version.
Author: Shinya Kato <shinya11.kato@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Nathan Bossart <nathandbossart@gmail.com>
Reviewed-by: Robert Treat <rob@xzilla.net>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-by: Sami Imseih <samimseih@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discusssion: https://postgr.es/m/CAOzEurQcOY-OBL_ouEVfEaFqe_md3vB5pXjR_m6L71Dcp1JKCQ@mail.gmail.com
---
doc/src/sgml/maintenance.sgml | 7 ++-
doc/src/sgml/monitoring.sgml | 67 ++++++++++++++++++++++++++++
src/backend/access/heap/vacuumlazy.c | 24 ++++++++--
src/backend/catalog/system_views.sql | 10 ++++-
src/include/commands/progress.h | 12 +++++
src/test/regress/expected/rules.out | 14 +++++-
6 files changed, 126 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index dc59c88319e..f003841e6be 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -1014,8 +1014,11 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
see <xref linkend="table-lock-compatibility"/>. However, if the autovacuum
is running to prevent transaction ID wraparound (i.e., the autovacuum query
name in the <structname>pg_stat_activity</structname> view ends with
- <literal>(to prevent wraparound)</literal>), the autovacuum is not
- automatically interrupted.
+ <literal>(to prevent wraparound)</literal> or the
+ <literal>autovacuum_wraparound</literal> value in the
+ <structfield>triggered_by</structfield> column in the
+ <structname>pg_stat_progress_vacuum</structname> view), the autovacuum is
+ not automatically interrupted.
</para>
<warning>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index f3bf527d5b4..3f9dafc6cd5 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6676,6 +6676,73 @@ FROM pg_stat_get_backend_idset() AS backendid;
stale.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>mode</structfield> <type>text</type>
+ </para>
+ <para>
+ The mode of the current vacuum operation. Possible values are:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>normal</literal>: A standard vacuum operation that is not
+ required to be aggressive and has not entered failsafe mode.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>aggressive</literal>: A vacuum that must scan the entire
+ table because <xref linkend="guc-vacuum-freeze-table-age"/> or
+ <xref linkend="guc-vacuum-freeze-min-age"/> (or the corresponding
+ per-table storage parameters) required it, or because page skipping
+ was disabled via <command>VACUUM (DISABLE_PAGE_SKIPPING)</command>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>failsafe</literal>: A vacuum operation that entered failsafe
+ mode when the system was at risk of transaction ID or multixact ID
+ wraparound (see <xref linkend="guc-vacuum-failsafe-age"/> and
+ <xref linkend="guc-vacuum-multixact-failsafe-age"/>).
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>triggered_by</structfield> <type>text</type>
+ </para>
+ <para>
+ The trigger of the current vacuum operation. Possible values are:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>manual</literal>: Initiated by an explicit
+ <command>VACUUM</command> command.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>autovacuum</literal>: Launched by autovacuum based on
+ <xref linkend="guc-autovacuum-vacuum-threshold"/> or
+ <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>autovacuum_wraparound</literal>: Launched by autovacuum to
+ avoid transaction ID or multixact ID wraparound (see
+ <xref linkend="vacuum-for-wraparound"/> as well as
+ <xref linkend="guc-autovacuum-freeze-max-age"/> and
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>).
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index d2b031fdd06..88229bf3375 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -664,6 +664,16 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
RelationGetRelid(rel));
+ pgstat_progress_update_param(PROGRESS_VACUUM_MODE,
+ PROGRESS_VACUUM_MODE_NORMAL);
+ if (AmAutoVacuumWorkerProcess())
+ pgstat_progress_update_param(PROGRESS_VACUUM_TRIGGERED_BY,
+ params.is_wraparound
+ ? PROGRESS_VACUUM_TRIGGERED_BY_AUTOVACUUM_WRAPAROUND
+ : PROGRESS_VACUUM_TRIGGERED_BY_AUTOVACUUM);
+ else
+ pgstat_progress_update_param(PROGRESS_VACUUM_TRIGGERED_BY,
+ PROGRESS_VACUUM_TRIGGERED_BY_MANUAL);
/*
* Setup error traceback support for ereport() first. The idea is to set
@@ -787,6 +797,9 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
* to increase the number of dead tuples it can prune away.)
*/
vacrel->aggressive = vacuum_get_cutoffs(rel, params, &vacrel->cutoffs);
+ if (vacrel->aggressive)
+ pgstat_progress_update_param(PROGRESS_VACUUM_MODE,
+ PROGRESS_VACUUM_MODE_AGGRESSIVE);
vacrel->rel_pages = orig_rel_pages = RelationGetNumberOfBlocks(rel);
vacrel->vistest = GlobalVisTestFor(rel);
@@ -808,6 +821,8 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
* visibility map (even those set all-frozen)
*/
vacrel->aggressive = true;
+ pgstat_progress_update_param(PROGRESS_VACUUM_MODE,
+ PROGRESS_VACUUM_MODE_AGGRESSIVE);
skipwithvm = false;
}
@@ -2995,9 +3010,10 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
{
const int progress_index[] = {
PROGRESS_VACUUM_INDEXES_TOTAL,
- PROGRESS_VACUUM_INDEXES_PROCESSED
+ PROGRESS_VACUUM_INDEXES_PROCESSED,
+ PROGRESS_VACUUM_MODE
};
- int64 progress_val[2] = {0, 0};
+ int64 progress_val[3] = {0, 0, PROGRESS_VACUUM_MODE_FAILSAFE};
VacuumFailsafeActive = true;
@@ -3013,8 +3029,8 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
vacrel->do_index_cleanup = false;
vacrel->do_rel_truncate = false;
- /* Reset the progress counters */
- pgstat_progress_update_multi_param(2, progress_index, progress_val);
+ /* Reset the progress counters and the mode */
+ pgstat_progress_update_multi_param(3, progress_index, progress_val);
ereport(WARNING,
(errmsg("bypassing nonessential maintenance of table \"%s.%s.%s\" as a failsafe after %d index scans",
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index dec8df4f8ee..816ec88942f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1265,7 +1265,15 @@ CREATE VIEW pg_stat_progress_vacuum AS
S.param6 AS max_dead_tuple_bytes, S.param7 AS dead_tuple_bytes,
S.param8 AS num_dead_item_ids, S.param9 AS indexes_total,
S.param10 AS indexes_processed,
- S.param11 / 1000000::double precision AS delay_time
+ S.param11 / 1000000::double precision AS delay_time,
+ CASE S.param12 WHEN 1 THEN 'normal'
+ WHEN 2 THEN 'aggressive'
+ WHEN 3 THEN 'failsafe'
+ ELSE NULL END AS mode,
+ CASE S.param13 WHEN 1 THEN 'manual'
+ WHEN 2 THEN 'autovacuum'
+ WHEN 3 THEN 'autovacuum_wraparound'
+ ELSE NULL END AS triggered_by
FROM pg_stat_get_progress_info('VACUUM') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index 1cde4bd9bcf..5fc5d3fcbee 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -29,6 +29,8 @@
#define PROGRESS_VACUUM_INDEXES_TOTAL 8
#define PROGRESS_VACUUM_INDEXES_PROCESSED 9
#define PROGRESS_VACUUM_DELAY_TIME 10
+#define PROGRESS_VACUUM_MODE 11
+#define PROGRESS_VACUUM_TRIGGERED_BY 12
/* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */
#define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1
@@ -38,6 +40,16 @@
#define PROGRESS_VACUUM_PHASE_TRUNCATE 5
#define PROGRESS_VACUUM_PHASE_FINAL_CLEANUP 6
+/* Modes of vacuum (as advertised via PROGRESS_VACUUM_MODE) */
+#define PROGRESS_VACUUM_MODE_NORMAL 1
+#define PROGRESS_VACUUM_MODE_AGGRESSIVE 2
+#define PROGRESS_VACUUM_MODE_FAILSAFE 3
+
+/* Reasons for vacuum (as advertised via PROGRESS_VACUUM_TRIGGERED_BY) */
+#define PROGRESS_VACUUM_TRIGGERED_BY_MANUAL 1
+#define PROGRESS_VACUUM_TRIGGERED_BY_AUTOVACUUM 2
+#define PROGRESS_VACUUM_TRIGGERED_BY_AUTOVACUUM_WRAPAROUND 3
+
/* Progress parameters for analyze */
#define PROGRESS_ANALYZE_PHASE 0
#define PROGRESS_ANALYZE_BLOCKS_TOTAL 1
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 77e25ca029e..830444a05bf 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2104,7 +2104,19 @@ pg_stat_progress_vacuum| SELECT s.pid,
s.param8 AS num_dead_item_ids,
s.param9 AS indexes_total,
s.param10 AS indexes_processed,
- ((s.param11)::double precision / (1000000)::double precision) AS delay_time
+ ((s.param11)::double precision / (1000000)::double precision) AS delay_time,
+ CASE s.param12
+ WHEN 1 THEN 'normal'::text
+ WHEN 2 THEN 'aggressive'::text
+ WHEN 3 THEN 'failsafe'::text
+ ELSE NULL::text
+ END AS mode,
+ CASE s.param13
+ WHEN 1 THEN 'manual'::text
+ WHEN 2 THEN 'autovacuum'::text
+ WHEN 3 THEN 'autovacuum_wraparound'::text
+ ELSE NULL::text
+ END AS triggered_by
FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
pg_stat_recovery_prefetch| SELECT stats_reset,
--
2.47.3