Hi, finding active progress reports currently requires querying each pg_stat_progress_* view separately.
This patch adds pg_stat_progress with three columns: pid, command, and progress_view, pointing to the detailed view for each operation. It reuses the existing infrastructure without backend changes. IMHO, these are the minimal common fields. I also considered datid and datname, but some tracked operations, such as base backups, cover the whole cluster. Documentation and tests are included. Build, regression, isolation, and new TAP tests passed locally. I'd appreciate feedback on the usefulness and proposed columns. My longer-term aim is to make cluster operations easier to discover and monitor, and to extend progress reporting to more commands. Regards, Josef
From eab4fea13096fbca5c0edc5a1de89d843eabc95c Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Josef=20=C5=A0im=C3=A1nek?= <[email protected]> Date: Sat, 7 Nov 2026 22:08:43 +0100 Subject: [PATCH v1] Add a `pg_stat_progress` overview view List backends reporting command progress with their PID, command family, and detailed progress view. Reuse `pg_stat_get_progress_info()` and the existing progress reporting permissions. --- doc/src/sgml/monitoring.sgml | 78 ++++++++ src/backend/catalog/system_views.sql | 13 ++ src/include/catalog/catversion.h | 2 +- src/test/modules/test_misc/meson.build | 1 + .../modules/test_misc/t/016_stat_progress.pl | 168 ++++++++++++++++++ src/test/regress/expected/rules.out | 5 + 6 files changed, 266 insertions(+), 1 deletion(-) create mode 100644 src/test/modules/test_misc/t/016_stat_progress.pl diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 445fbc9232c..9de608fb37e 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -381,6 +381,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser </entry> </row> + <row> + <entry><structname>pg_stat_progress</structname><indexterm><primary>pg_stat_progress</primary></indexterm></entry> + <entry>One row for each backend reporting command progress, identifying + the command family and its detailed progress view. + See <xref linkend="pg-stat-progress-view"/>. + </entry> + </row> + <row> <entry><structname>pg_stat_progress_analyze</structname><indexterm><primary>pg_stat_progress_analyze</primary></indexterm></entry> <entry>One row for each backend (including autovacuum worker processes) running @@ -6493,6 +6501,76 @@ FROM pg_stat_get_backend_idset() AS backendid; This may be expanded in the future. </para> + <sect2 id="command-progress-reporting"> + <title>Active Command Progress</title> + + <para> + The <structname>pg_stat_progress</structname> view provides an overview of + all backends currently reporting command progress, across all databases. + It contains one row per reporting backend. Use the view named in + <structfield>progress_view</structfield>, filtering by + <structfield>pid</structfield>, to examine the command-specific counters. + For example: + </para> + + <programlisting> +SELECT * FROM pg_stat_progress; +SELECT * FROM pg_stat_progress_copy WHERE pid = 12345; + </programlisting> + + <table id="pg-stat-progress-view" xreflabel="pg_stat_progress"> + <title><structname>pg_stat_progress</structname> View</title> + <tgroup cols="1"> + <thead> + <row><entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para><para>Description</para></entry></row> + </thead> + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>pid</structfield> <type>integer</type> + </para> + <para>Process ID of the backend reporting progress.</para></entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>command</structfield> <type>text</type> + </para> + <para> + Progress reporting command family: <literal>ANALYZE</literal>, + <literal>VACUUM</literal>, <literal>CREATE INDEX</literal>, + <literal>BASEBACKUP</literal>, <literal>COPY</literal>, + <literal>REPACK</literal>, or <literal>DATACHECKSUMS</literal>. + The <literal>CREATE INDEX</literal> family includes + <command>REINDEX</command>. The <literal>REPACK</literal> family + includes <command>CLUSTER</command> and <command>VACUUM FULL</command>. + </para></entry> + </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>progress_view</structfield> <type>regclass</type> + </para> + <para> + The detailed progress view for this command family. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + These columns are visible to all users. Access to command-specific + details follows the permission rules of the detailed progress views. + A row disappears when the backend stops reporting progress. Commands + that do not support progress reporting are not included. The + <structname>pg_stat_progress_cluster</structname> compatibility view does + not produce an additional overview row for <command>REPACK</command>, + <command>CLUSTER</command>, or <command>VACUUM FULL</command>. + An operation can finish before its detailed view is queried. + </para> + </sect2> + <sect2 id="analyze-progress-reporting"> <title>ANALYZE Progress Reporting</title> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 6dce9192ba0..9a71a072604 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1509,6 +1509,19 @@ CREATE VIEW pg_stat_progress_data_checksums AS LEFT JOIN pg_database D ON S.datid = D.oid ORDER BY S.datid; -- return the launcher process first +CREATE VIEW pg_stat_progress AS + SELECT S.pid, C.command, C.progress_view + FROM (VALUES + ('ANALYZE', 'pg_catalog.pg_stat_progress_analyze'::regclass), + ('VACUUM', 'pg_catalog.pg_stat_progress_vacuum'::regclass), + ('CREATE INDEX', 'pg_catalog.pg_stat_progress_create_index'::regclass), + ('BASEBACKUP', 'pg_catalog.pg_stat_progress_basebackup'::regclass), + ('COPY', 'pg_catalog.pg_stat_progress_copy'::regclass), + ('REPACK', 'pg_catalog.pg_stat_progress_repack'::regclass), + ('DATACHECKSUMS', 'pg_catalog.pg_stat_progress_data_checksums'::regclass) + ) AS C(command, progress_view) + CROSS JOIN LATERAL pg_stat_get_progress_info(C.command) AS S; + CREATE VIEW pg_user_mappings AS SELECT U.oid AS umid, diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 1051467800c..adef5e810a1 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202611021 +#define CATALOG_VERSION_NO 202611071 #endif diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build index 5d81f5b13be..2bb890b3112 100644 --- a/src/test/modules/test_misc/meson.build +++ b/src/test/modules/test_misc/meson.build @@ -24,6 +24,7 @@ tests += { 't/013_temp_obj_multisession.pl', 't/014_log_statement_max_length.pl', 't/015_temp_schema_exit_deferrable.pl', + 't/016_stat_progress.pl', ], # The injection points are cluster-wide, so disable installcheck 'runningcheck': false, diff --git a/src/test/modules/test_misc/t/016_stat_progress.pl b/src/test/modules/test_misc/t/016_stat_progress.pl new file mode 100644 index 00000000000..26c3725e3b6 --- /dev/null +++ b/src/test/modules/test_misc/t/016_stat_progress.pl @@ -0,0 +1,168 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node = PostgreSQL::Test::Cluster->new('primary'); +$node->init; +$node->start; +$node->safe_psql( + 'postgres', q[ +CREATE ROLE progress_viewer; +CREATE TABLE progress_data AS SELECT generate_series(1, 64) AS i; +CREATE FUNCTION progress_gate(i integer, k integer) RETURNS integer +LANGUAGE plpgsql IMMUTABLE AS $$ +BEGIN + PERFORM pg_advisory_xact_lock(7002, k); + RETURN i; +END +$$; +]); + +my $blocker = $node->background_psql('postgres'); +my $copy = $node->background_psql('postgres'); +my $index = $node->background_psql('postgres', on_error_stop => 0); +my $query = $node->background_psql('postgres'); +my $copy_pid = $copy->query_safe('SELECT pg_backend_pid()'); +my $index_pid = $index->query_safe('SELECT pg_backend_pid()'); +my $query_pid = $query->query_safe('SELECT pg_backend_pid()'); + +sub wait_for_gate +{ + my ($pid, $key) = @_; + $node->poll_query_until( + 'postgres', qq[ +SELECT count(*) = 1 FROM pg_locks +WHERE pid = $pid AND locktype = 'advisory' AND NOT granted + AND classid = 7002 AND objid = $key; +]) or die "backend $pid did not reach gate $key"; +} + +sub no_progress +{ + my ($pid, $description) = @_; + is( $node->safe_psql( + 'postgres', qq[ +SELECT count(*) FROM pg_stat_progress WHERE pid = $pid; +]), + '0', + $description); +} + +$blocker->query_safe( + q[ +SELECT pg_advisory_lock(7002, 1), pg_advisory_lock(7002, 2), + pg_advisory_lock(7002, 3); +]); +$copy->query_until( + qr/copy_started/, q[ +\echo copy_started +COPY (SELECT progress_gate(i, 1) FROM progress_data LIMIT 1) TO STDOUT; +]); +$index->query_until( + qr/index_started/, q[ +\echo index_started +CREATE INDEX progress_idx ON progress_data (progress_gate(i, 2)); +]); +$query->query_until( + qr/query_started/, q[ +\echo query_started +SELECT progress_gate(i, 3) FROM progress_data LIMIT 1; +]); +wait_for_gate($copy_pid, 1); +wait_for_gate($index_pid, 2); +wait_for_gate($query_pid, 3); + +is( $node->safe_psql( + 'postgres', qq[ +SELECT command, progress_view FROM pg_stat_progress +WHERE pid IN ($copy_pid, $index_pid, $query_pid) ORDER BY command; +]), + "COPY|pg_stat_progress_copy\nCREATE INDEX|pg_stat_progress_create_index", + 'overview lists simultaneous progress reporters and their detailed views' +); +is( $node->safe_psql( + 'postgres', qq[ +SELECT (SELECT count(*) FROM pg_stat_progress_copy WHERE pid = $copy_pid), + (SELECT count(*) FROM pg_stat_progress_create_index WHERE pid = $index_pid); +]), + '1|1', + 'overview rows have corresponding detailed progress rows'); +no_progress($query_pid, + 'an ordinary active query is not a progress reporter'); + +is( $node->safe_psql( + 'postgres', qq[ +SET ROLE progress_viewer; +SELECT command, progress_view FROM pg_stat_progress +WHERE pid IN ($copy_pid, $index_pid) ORDER BY command; +]), + "COPY|pg_stat_progress_copy\nCREATE INDEX|pg_stat_progress_create_index", + 'all users can discover progress reporters'); +is( $node->safe_psql( + 'postgres', qq[ +SET ROLE progress_viewer; +SELECT relid IS NULL FROM pg_stat_progress_copy WHERE pid = $copy_pid; +]), + 't', + 'discovering a reporter does not expose restricted details'); + +$blocker->query_safe('SELECT pg_advisory_unlock(7002, 1)'); +$copy->query_safe('SELECT 1'); +no_progress($copy_pid, 'completed COPY disappears from the overview'); +is( $node->safe_psql( + 'postgres', qq[ +SELECT command FROM pg_stat_progress WHERE pid = $index_pid; +]), + 'CREATE INDEX', + 'another active reporter remains visible'); +is($node->safe_psql('postgres', "SELECT pg_cancel_backend($index_pid)"), + 't', 'cancel the index build'); +$index->query('SELECT 1'); +like( + $index->{stderr}, + qr/canceling statement due to user request/, + 'the index build was canceled'); +no_progress($index_pid, 'canceled index build disappears from the overview'); +$blocker->query_safe( + 'SELECT pg_advisory_unlock(7002, 2), pg_advisory_unlock(7002, 3)'); +$query->query_safe('SELECT 1'); +$index->quit; +$query->quit; + +$index = $node->background_psql('postgres'); +$index_pid = $index->query_safe('SELECT pg_backend_pid()'); +$index->query_safe( + 'CREATE INDEX progress_idx ON progress_data (progress_gate(i, 2))'); +$blocker->query_safe('SELECT pg_advisory_lock(7002, 2)'); +$index->query_until( + qr/cluster_started/, q[ +\echo cluster_started +CLUSTER progress_data USING progress_idx; +]); +wait_for_gate($index_pid, 2); +is( $node->safe_psql( + 'postgres', qq[ +SELECT command, progress_view FROM pg_stat_progress WHERE pid = $index_pid; +]), + 'REPACK|pg_stat_progress_repack', + 'CLUSTER has one canonical overview row'); +is( $node->safe_psql( + 'postgres', qq[ +SELECT count(*) FROM pg_stat_progress_cluster WHERE pid = $index_pid; +]), + '1', + 'the compatibility view remains available for the same backend'); +$blocker->query_safe('SELECT pg_advisory_unlock(7002, 2)'); +$index->query_safe('SELECT 1'); +no_progress($index_pid, 'completed CLUSTER disappears from the overview'); + +$copy->quit; +$index->quit; +$blocker->quit; +$node->stop; +done_testing(); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index acbe4667d89..d580e266684 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1982,6 +1982,11 @@ pg_stat_lock| SELECT locktype, fastpath_exceeded, stats_reset FROM pg_stat_get_lock() l(locktype, waits, wait_time, fastpath_exceeded, stats_reset); +pg_stat_progress| SELECT s.pid, + c.command, + c.progress_view + FROM (( VALUES ('ANALYZE'::text,'pg_stat_progress_analyze'::regclass), ('VACUUM'::text,'pg_stat_progress_vacuum'::regclass), ('CREATE INDEX'::text,'pg_stat_progress_create_index'::regclass), ('BASEBACKUP'::text,'pg_stat_progress_basebackup'::regclass), ('COPY'::text,'pg_stat_progress_copy'::regclass), ('REPACK'::text,'pg_stat_progress_repack'::regclass), ('DATACHECKSUMS'::text,'pg_stat_progress_data_checksums'::regclass)) c(command, progress_view) + CROSS JOIN LATERAL pg_stat_get_progress_info(c.command) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)); pg_stat_progress_analyze| SELECT s.pid, s.datid, d.datname,
