From: Bertrand Drouvot Date: Sat, 9 Aug 2025 14:22:36 +0000 Subject: [PATCH v1 02/10] Adding the pg_stat_backend view This view displays one row per server process, showing statistics related to the current activity of that process. It currently displays the pid, the number of sequential scans initiated on tables and the time at which these statistics were last reset. It's built on top of a new function (pg_stat_get_backend_statistics()). The idea is the same as pg_stat_activity and pg_stat_get_activity(). Adding documentation and tests. XXX: Bump catversion --- doc/src/sgml/monitoring.sgml | 95 ++++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 7 ++ src/backend/utils/adt/pgstatfuncs.c | 55 ++++++++++++++++ src/include/catalog/pg_proc.dat | 9 +++ src/test/regress/expected/rules.out | 4 ++ src/test/regress/expected/stats.out | 10 +++ src/test/regress/sql/stats.sql | 8 +++ 7 files changed, 188 insertions(+) 50.8% doc/src/sgml/ 26.9% src/backend/utils/adt/ 8.0% src/include/catalog/ 7.1% src/test/regress/expected/ 4.0% src/test/regress/sql/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 3f4a27a736e..6868af55db4 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -320,6 +320,19 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser + + + pg_stat_backend + pg_stat_backend + + + One row per server process, showing statistics related to + the current activity of that process, such as number of sequential scans. + See + pg_stat_backend for details. + + + pg_stat_replicationpg_stat_replication One row per WAL sender process, showing statistics about @@ -1172,6 +1185,72 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + <structname>pg_stat_backend</structname> + + + pg_stat_backend + + + + The pg_stat_backend view will have one row + per server process, showing statistics related to + the current activity of that process. + + + + <structname>pg_stat_backend</structname> View + + + + + Column Type + + + Description + + + + + + + + pid integer + + + Process ID of this backend + + + + + + seq_scan bigint + + + The number of sequential scans initiated on tables. + + + + + + stats_reset timestamp with time zone + + + Time at which these statistics were last reset + + + + +
+ + + + The view does not return statistics for the checkpointer, + the background writer, the startup process and the autovacuum launcher. + + + + <structname>pg_stat_replication</structname> @@ -4921,6 +5000,22 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + + pg_stat_get_backend_statistics + + pg_stat_get_backend_statistics ( integer ) + setof record + + + Returns a record of statistics bout the backend with the specified + process ID, or one record for each active backend in the system + if NULL is specified. The fields returned are a + subset of those in the pg_stat_backend view. + + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 1b3c5a55882..3ab7802cc5c 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -911,6 +911,13 @@ CREATE VIEW pg_stat_activity AS LEFT JOIN pg_database AS D ON (S.datid = D.oid) LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); +CREATE VIEW pg_stat_backend AS + SELECT + S.pid, + S.seq_scan, + S.stats_reset + FROM pg_stat_get_backend_statistics(NULL) AS S; + CREATE VIEW pg_stat_replication AS SELECT S.pid, diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index c756c2bebaa..2adfbdcb65c 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -685,6 +685,61 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) return (Datum) 0; } +/* + * Returns statistics of PG backends. + */ +Datum +pg_stat_get_backend_statistics(PG_FUNCTION_ARGS) +{ +#define PG_STAT_GET_BACKEND_STATS_COLS 3 + int num_backends = pgstat_fetch_stat_numbackends(); + int curr_backend; + int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0); + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + InitMaterializedSRF(fcinfo, 0); + + /* 1-based index */ + for (curr_backend = 1; curr_backend <= num_backends; curr_backend++) + { + /* for each row */ + Datum values[PG_STAT_GET_BACKEND_STATS_COLS] = {0}; + bool nulls[PG_STAT_GET_BACKEND_STATS_COLS] = {0}; + LocalPgBackendStatus *local_beentry; + PgBackendStatus *beentry; + PgStat_Backend *backend_stats; + + /* Get the next one in the list */ + local_beentry = pgstat_get_local_beentry_by_index(curr_backend); + beentry = &local_beentry->backendStatus; + + /* If looking for specific PID, ignore all the others */ + if (pid != -1 && beentry->st_procpid != pid) + continue; + + backend_stats = pgstat_fetch_stat_backend_by_pid(beentry->st_procpid, NULL); + + values[0] = Int32GetDatum(beentry->st_procpid); + + if (!backend_stats) + continue; + + values[1] = Int64GetDatum(backend_stats->heap_scan); + + if (backend_stats->stat_reset_timestamp != 0) + values[2] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp); + else + nulls[2] = true; + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); + + /* If only a single backend was requested, and we found it, break. */ + if (pid != -1) + break; + } + + return (Datum) 0; +} Datum pg_backend_pid(PG_FUNCTION_ARGS) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 118d6da1ace..e87200374d8 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5641,6 +5641,15 @@ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}', proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}', prosrc => 'pg_stat_get_activity' }, +{ oid => '9555', + descr => 'statistics: statistics about currently active backends', + proname => 'pg_stat_get_backend_statistics', prorows => '100', proisstrict => 'f', + proretset => 't', provolatile => 's', proparallel => 'r', + prorettype => 'record', proargtypes => 'int4', + proallargtypes => '{int4,int4,int8,timestamptz}', + proargmodes => '{i,o,o,o}', + proargnames => '{pid,pid,seq_scan,stats_reset}', + prosrc => 'pg_stat_get_backend_statistics' }, { oid => '6318', descr => 'describe wait events', proname => 'pg_get_wait_events', procost => '10', prorows => '250', proretset => 't', provolatile => 'v', prorettype => 'record', diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 35e8aad7701..63f8f12b8a5 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1847,6 +1847,10 @@ pg_stat_archiver| SELECT archived_count, last_failed_time, stats_reset FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset); +pg_stat_backend| SELECT pid, + seq_scan, + stats_reset + FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, seq_scan, stats_reset); pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_alloc() AS buffers_alloc, diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 605f5070376..937bf4bfc5b 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -118,6 +118,8 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch, pg_catalog.pg_statio_user_tables AS b WHERE t.relname='tenk2' AND b.relname='tenk2'; COMMIT; +SELECT seq_scan AS seq_scan_before + FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset -- test effects of TRUNCATE on n_live_tup/n_dead_tup counters CREATE TABLE trunc_stats_test(id serial); CREATE TABLE trunc_stats_test1(id serial, stuff text); @@ -219,6 +221,14 @@ SELECT st.seq_scan >= pr.seq_scan + 1, t | t | t | t (1 row) +SELECT seq_scan AS seq_scan_after + FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset +SELECT :seq_scan_after > :seq_scan_before; + ?column? +---------- + t +(1 row) + SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages, st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1 FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 54e72866344..b743352eadb 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -38,6 +38,9 @@ SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch, WHERE t.relname='tenk2' AND b.relname='tenk2'; COMMIT; +SELECT seq_scan AS seq_scan_before + FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset + -- test effects of TRUNCATE on n_live_tup/n_dead_tup counters CREATE TABLE trunc_stats_test(id serial); CREATE TABLE trunc_stats_test1(id serial, stuff text); @@ -122,6 +125,11 @@ SELECT st.seq_scan >= pr.seq_scan + 1, FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr WHERE st.relname='tenk2' AND cl.relname='tenk2'; +SELECT seq_scan AS seq_scan_after + FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset + +SELECT :seq_scan_after > :seq_scan_before; + SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages, st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1 FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr -- 2.34.1 --j6dXQ8GCuNzKPBpX Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v1-0003-Adding-seq_tup_read-to-pg_stat_backend.patch"