Hi,
On Tue, Feb 25, 2025 at 03:50:38PM +0900, Michael Paquier wrote:
> On Mon, Feb 24, 2025 at 09:07:39AM +0000, Bertrand Drouvot wrote:
> > Now that 2421e9a51d2 is in, let's resume working in this thread. PFA a
> > rebase to
> > make the CF bot happy. Nothing has changed since V7, V8 only removes
> > "v7-0001" (
> > as part of 2421e9a51d2), so that v8-000N is nothing but v7-000(N+1).
>
> v7-0001 looks sensible, so does v7-0002 with the introduction of
> PgStat_WalCounters to tackle the fact that backend statistics need
> only one reset_timestamp shared across IO and WAL stats.
Thanks for looking at it! (I guess you meant to say v8-0001 and v8-0002).
> --- a/src/backend/utils/activity/pgstat_wal.c
> +++ b/src/backend/utils/activity/pgstat_wal.c
> @@ -53,6 +53,8 @@ pgstat_report_wal(bool force)
> /* flush wal stats */
> pgstat_flush_wal(nowait);
>
> + pgstat_flush_backend(nowait, PGSTAT_BACKEND_FLUSH_WAL);
> +
> /* flush IO stats */
> pgstat_flush_io(nowait);
>
> Fine to stick that into pgstat_report_wal(), which is used anywhere
> else. pgstat_flush_wal() could be static in pgstat_wal.c?
hmm right. Not linked to this patch though, so done in a dedicated patch
in passing (v9-0001).
> +Datum
> +pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
> +{
> [...]
> + pid = PG_GETARG_INT32(0);
> + proc = BackendPidGetProc(pid);
> +
> + /*
> + * This could be an auxiliary process but these do not report backend
> + * statistics due to pgstat_tracks_backend_bktype(), so there is no need
> + * for an extra call to AuxiliaryPidGetProc().
> + */
> + if (!proc)
> + PG_RETURN_NULL();
> +
> + procNumber = GetNumberFromPGProc(proc);
> +
> + beentry = pgstat_get_beentry_by_proc_number(procNumber);
> + if (!beentry)
> + PG_RETURN_NULL();
> +
> + backend_stats = pgstat_fetch_stat_backend(procNumber);
> + if (!backend_stats)
> + PG_RETURN_NULL();
> +
> + /* if PID does not match, leave */
> + if (beentry->st_procpid != pid)
> + PG_RETURN_NULL();
> +
> + /* backend may be gone, so recheck in case */
> + if (beentry->st_backendType == B_INVALID)
> + PG_RETURN_NULL();
>
> This is a block of code copy-pasted from pg_stat_get_backend_io().
> This is complex, so perhaps it would be better to refactor that in a
> single routine that returns PgStat_Backend? Then reuse the refactored
> code in both pg_stat_get_backend_io() and the new
> pg_stat_get_backend_wal().
That makes fully sense. Done in 0004 attached. Somehow related to that, I've
a patch in progress to address some of Rahila's comments ([1]) (the one related
to the AuxiliaryPidGetProc() call is relevant specially since a051e71e28a where
pgstat_tracks_backend_bktype() has been modified for B_WAL_RECEIVER,
B_WAL_SUMMARIZER
and B_WAL_WRITER). I'll wait for 0004 to go in before sharing the patch.
> +-- Test pg_stat_get_backend_wal (and make a temp table so our temp schema
> exists)
> +SELECT wal_bytes AS backend_wal_bytes_before from
> pg_stat_get_backend_wal(pg_backend_pid()) \gset
> +
> CREATE TEMP TABLE test_stats_temp AS SELECT 17;
> DROP TABLE test_stats_temp;
> [...]
> +SELECT pg_stat_force_next_flush();
> +SELECT wal_bytes > :backend_wal_bytes_before FROM
> pg_stat_get_backend_wal(pg_backend_pid());
>
> That should be stable, as we're guaranteed to have records here.
Yup.
[1]:
https://www.postgresql.org/message-id/CAH2L28v9BwN8_y0k6FQ591%3D0g2Hj_esHLGj3bP38c9nmVykoiA%40mail.gmail.com
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From ee2fb0f389161c8f706923aa58e227ab6a2c623f Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Tue, 25 Feb 2025 08:22:50 +0000
Subject: [PATCH v9 1/5] make pgstat_flush_wal() static
pgstat_flush_wal() is used only in pgstat_wal.c so make it static.
---
src/backend/utils/activity/pgstat_wal.c | 3 ++-
src/include/utils/pgstat_internal.h | 2 --
2 files changed, 2 insertions(+), 3 deletions(-)
57.6% src/backend/utils/activity/
42.3% src/include/utils/
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index 4dc41a4a590..b702891ed46 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -29,6 +29,7 @@
*/
static WalUsage prevWalUsage;
+static void pgstat_flush_wal(bool nowait);
/*
* Calculate how much WAL usage counters have increased and update
@@ -72,7 +73,7 @@ pgstat_fetch_stat_wal(void)
/*
* Simple wrapper of pgstat_wal_flush_cb()
*/
-void
+static void
pgstat_flush_wal(bool nowait)
{
(void) pgstat_wal_flush_cb(nowait);
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 06dcea3f0dc..36d228e3558 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -748,8 +748,6 @@ extern void pgstat_slru_snapshot_cb(void);
* Functions in pgstat_wal.c
*/
-extern void pgstat_flush_wal(bool nowait);
-
extern void pgstat_wal_init_backend_cb(void);
extern bool pgstat_wal_have_pending_cb(void);
extern bool pgstat_wal_flush_cb(bool nowait);
--
2.34.1
>From f2c3f83228e0cb1909cdd43f732ecaa377e0ebeb Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 6 Jan 2025 07:51:27 +0000
Subject: [PATCH v9 2/5] Extract logic filling pg_stat_get_wal()'s tuple into
its own routine
This commit adds pg_stat_wal_build_tuple(), a helper routine for
pg_stat_get_wal(), that fills its tuple based on the contents
of PgStat_WalStats. This will be used in a follow-up commit that uses
the same structures as pg_stat_wal for reporting, but for the PGSTAT_KIND_BACKEND
statistics kind.
---
src/backend/utils/adt/pgstatfuncs.c | 48 +++++++++++++++++++----------
1 file changed, 32 insertions(+), 16 deletions(-)
100.0% src/backend/utils/adt/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 68e16e52ab6..620d60a0938 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1632,20 +1632,22 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
}
/*
- * Returns statistics of WAL activity
+ * pg_stat_wal_build_tuple
+ *
+ * Helper routine for pg_stat_get_wal() returning one tuple based on the contents
+ * of wal_stats.
*/
-Datum
-pg_stat_get_wal(PG_FUNCTION_ARGS)
+static Datum
+pg_stat_wal_build_tuple(PgStat_WalStats wal_stats)
{
-#define PG_STAT_GET_WAL_COLS 5
+#define PG_STAT_WAL_COLS 5
TupleDesc tupdesc;
- Datum values[PG_STAT_GET_WAL_COLS] = {0};
- bool nulls[PG_STAT_GET_WAL_COLS] = {0};
+ Datum values[PG_STAT_WAL_COLS] = {0};
+ bool nulls[PG_STAT_WAL_COLS] = {0};
char buf[256];
- PgStat_WalStats *wal_stats;
/* Initialise attributes information in the tuple descriptor */
- tupdesc = CreateTemplateTupleDesc(PG_STAT_GET_WAL_COLS);
+ tupdesc = CreateTemplateTupleDesc(PG_STAT_WAL_COLS);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "wal_records",
INT8OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "wal_fpi",
@@ -1659,28 +1661,42 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
BlessTupleDesc(tupdesc);
- /* Get statistics about WAL activity */
- wal_stats = pgstat_fetch_stat_wal();
-
/* Fill values and NULLs */
- values[0] = Int64GetDatum(wal_stats->wal_records);
- values[1] = Int64GetDatum(wal_stats->wal_fpi);
+ values[0] = Int64GetDatum(wal_stats.wal_records);
+ values[1] = Int64GetDatum(wal_stats.wal_fpi);
/* Convert to numeric. */
- snprintf(buf, sizeof buf, UINT64_FORMAT, wal_stats->wal_bytes);
+ snprintf(buf, sizeof buf, UINT64_FORMAT, wal_stats.wal_bytes);
values[2] = DirectFunctionCall3(numeric_in,
CStringGetDatum(buf),
ObjectIdGetDatum(0),
Int32GetDatum(-1));
- values[3] = Int64GetDatum(wal_stats->wal_buffers_full);
+ values[3] = Int64GetDatum(wal_stats.wal_buffers_full);
- values[4] = TimestampTzGetDatum(wal_stats->stat_reset_timestamp);
+ if (wal_stats.stat_reset_timestamp != 0)
+ values[4] = TimestampTzGetDatum(wal_stats.stat_reset_timestamp);
+ else
+ nulls[4] = true;
/* Returns the record as Datum */
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
}
+/*
+ * Returns statistics of WAL activity
+ */
+Datum
+pg_stat_get_wal(PG_FUNCTION_ARGS)
+{
+ PgStat_WalStats *wal_stats;
+
+ /* Get statistics about WAL activity */
+ wal_stats = pgstat_fetch_stat_wal();
+
+ return (pg_stat_wal_build_tuple(*wal_stats));
+}
+
/*
* Returns statistics of SLRU caches.
*/
--
2.34.1
>From 5a9a7f138128b8b36c566ab5c44eb3a37c357f88 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Thu, 16 Jan 2025 15:06:01 +0000
Subject: [PATCH v9 3/5] Adding a new PgStat_WalCounters struct
This new struct contains only the counters related to the WAL statistics.
This will be used in a follow-up commit that uses the same structures but
for the PGSTAT_KIND_BACKEND statistics kind.
---
src/backend/utils/activity/pgstat_wal.c | 2 +-
src/backend/utils/adt/pgstatfuncs.c | 20 +++++++++++---------
src/include/pgstat.h | 7 ++++++-
src/tools/pgindent/typedefs.list | 1 +
4 files changed, 19 insertions(+), 11 deletions(-)
8.5% src/backend/utils/activity/
82.0% src/backend/utils/adt/
7.8% src/include/
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index b702891ed46..830b51a7b93 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -116,7 +116,7 @@ pgstat_wal_flush_cb(bool nowait)
return true;
#define WALSTAT_ACC(fld, var_to_add) \
- (stats_shmem->stats.fld += var_to_add.fld)
+ (stats_shmem->stats.wal_counters.fld += var_to_add.fld)
WALSTAT_ACC(wal_records, wal_usage_diff);
WALSTAT_ACC(wal_fpi, wal_usage_diff);
WALSTAT_ACC(wal_bytes, wal_usage_diff);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 620d60a0938..9de14ffd449 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1635,10 +1635,11 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
* pg_stat_wal_build_tuple
*
* Helper routine for pg_stat_get_wal() returning one tuple based on the contents
- * of wal_stats.
+ * of wal_counters.
*/
static Datum
-pg_stat_wal_build_tuple(PgStat_WalStats wal_stats)
+pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters,
+ TimestampTz stat_reset_timestamp)
{
#define PG_STAT_WAL_COLS 5
TupleDesc tupdesc;
@@ -1662,20 +1663,20 @@ pg_stat_wal_build_tuple(PgStat_WalStats wal_stats)
BlessTupleDesc(tupdesc);
/* Fill values and NULLs */
- values[0] = Int64GetDatum(wal_stats.wal_records);
- values[1] = Int64GetDatum(wal_stats.wal_fpi);
+ values[0] = Int64GetDatum(wal_counters.wal_records);
+ values[1] = Int64GetDatum(wal_counters.wal_fpi);
/* Convert to numeric. */
- snprintf(buf, sizeof buf, UINT64_FORMAT, wal_stats.wal_bytes);
+ snprintf(buf, sizeof buf, UINT64_FORMAT, wal_counters.wal_bytes);
values[2] = DirectFunctionCall3(numeric_in,
CStringGetDatum(buf),
ObjectIdGetDatum(0),
Int32GetDatum(-1));
- values[3] = Int64GetDatum(wal_stats.wal_buffers_full);
+ values[3] = Int64GetDatum(wal_counters.wal_buffers_full);
- if (wal_stats.stat_reset_timestamp != 0)
- values[4] = TimestampTzGetDatum(wal_stats.stat_reset_timestamp);
+ if (stat_reset_timestamp != 0)
+ values[4] = TimestampTzGetDatum(stat_reset_timestamp);
else
nulls[4] = true;
@@ -1694,7 +1695,8 @@ pg_stat_get_wal(PG_FUNCTION_ARGS)
/* Get statistics about WAL activity */
wal_stats = pgstat_fetch_stat_wal();
- return (pg_stat_wal_build_tuple(*wal_stats));
+ return (pg_stat_wal_build_tuple(wal_stats->wal_counters,
+ wal_stats->stat_reset_timestamp));
}
/*
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index fc651d03cf9..438736870b3 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -474,12 +474,17 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter total_autoanalyze_time;
} PgStat_StatTabEntry;
-typedef struct PgStat_WalStats
+typedef struct PgStat_WalCounters
{
PgStat_Counter wal_records;
PgStat_Counter wal_fpi;
uint64 wal_bytes;
PgStat_Counter wal_buffers_full;
+} PgStat_WalCounters;
+
+typedef struct PgStat_WalStats
+{
+ PgStat_WalCounters wal_counters;
TimestampTz stat_reset_timestamp;
} PgStat_WalStats;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e3e09a2207e..19d510c9ec3 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2188,6 +2188,7 @@ PgStat_SubXactStatus
PgStat_TableCounts
PgStat_TableStatus
PgStat_TableXactStatus
+PgStat_WalCounters
PgStat_WalStats
PgXmlErrorContext
PgXmlStrictness
--
2.34.1
>From ff26611c1f74cfc23b13e4607dcf7494cc807ae5 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Tue, 25 Feb 2025 09:03:55 +0000
Subject: [PATCH v9 4/5] Add the pg_stat_get_backend_stats() helper for
pg_stat_get_backend_io()
This commit adds pg_stat_get_backend_stats(), a helper routine for
pg_stat_get_backend_io(), that returns the backend stats based on a pid passed
as an argument.
This will be used in a follow-up commit that uses the same logic to return the
per backend WAL stats.
---
src/backend/utils/activity/pgstat_backend.c | 52 +++++++++++++++++++++
src/backend/utils/adt/pgstatfuncs.c | 30 +-----------
src/include/pgstat.h | 1 +
3 files changed, 54 insertions(+), 29 deletions(-)
58.1% src/backend/utils/activity/
38.0% src/backend/utils/adt/
3.7% src/include/
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 4a667e7019c..3284d97fa95 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -25,6 +25,8 @@
#include "postgres.h"
#include "storage/bufmgr.h"
+#include "storage/proc.h"
+#include "storage/procarray.h"
#include "utils/memutils.h"
#include "utils/pgstat_internal.h"
@@ -81,6 +83,56 @@ pgstat_fetch_stat_backend(ProcNumber procNumber)
return backend_entry;
}
+/*
+ * Returns statistics of a backend by pid.
+ *
+ * It adds extra checks as compared to pgstat_fetch_stat_backend() to ensure
+ * that the backend is not gone. Also, if not NULL, bktype is populated as
+ * pg_stat_get_backend_io() needs it.
+ */
+PgStat_Backend *
+pg_stat_get_backend_stats(int pid, BackendType *bktype)
+{
+
+ PGPROC *proc;
+ PgBackendStatus *beentry;
+ ProcNumber procNumber;
+ PgStat_Backend *backend_stats;
+
+ proc = BackendPidGetProc(pid);
+
+ /*
+ * This could be an auxiliary process but these do not report backend
+ * statistics due to pgstat_tracks_backend_bktype(), so there is no need
+ * for an extra call to AuxiliaryPidGetProc().
+ */
+ if (!proc)
+ return NULL;
+
+ procNumber = GetNumberFromPGProc(proc);
+
+ beentry = pgstat_get_beentry_by_proc_number(procNumber);
+ if (!beentry)
+ return NULL;
+
+ backend_stats = pgstat_fetch_stat_backend(procNumber);
+ if (!backend_stats)
+ return NULL;
+
+ /* if PID does not match, leave */
+ if (beentry->st_procpid != pid)
+ return NULL;
+
+ /* backend may be gone, so recheck in case */
+ if (beentry->st_backendType == B_INVALID)
+ return NULL;
+
+ if (bktype)
+ *bktype = beentry->st_backendType;
+
+ return backend_stats;
+}
+
/*
* Flush out locally pending backend IO statistics. Locking is managed
* by the caller.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 9de14ffd449..13c91515480 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1576,46 +1576,18 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo;
BackendType bktype;
int pid;
- PGPROC *proc;
- ProcNumber procNumber;
PgStat_Backend *backend_stats;
PgStat_BktypeIO *bktype_stats;
- PgBackendStatus *beentry;
InitMaterializedSRF(fcinfo, 0);
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
pid = PG_GETARG_INT32(0);
- proc = BackendPidGetProc(pid);
-
- /*
- * This could be an auxiliary process but these do not report backend
- * statistics due to pgstat_tracks_backend_bktype(), so there is no need
- * for an extra call to AuxiliaryPidGetProc().
- */
- if (!proc)
- return (Datum) 0;
-
- procNumber = GetNumberFromPGProc(proc);
+ backend_stats = pg_stat_get_backend_stats(pid, &bktype);
- beentry = pgstat_get_beentry_by_proc_number(procNumber);
- if (!beentry)
- return (Datum) 0;
-
- backend_stats = pgstat_fetch_stat_backend(procNumber);
if (!backend_stats)
return (Datum) 0;
- bktype = beentry->st_backendType;
-
- /* if PID does not match, leave */
- if (beentry->st_procpid != pid)
- return (Datum) 0;
-
- /* backend may be gone, so recheck in case */
- if (bktype == B_INVALID)
- return (Datum) 0;
-
bktype_stats = &backend_stats->io_stats;
/*
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 438736870b3..3b71c3d4ed6 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -542,6 +542,7 @@ extern void pgstat_count_backend_io_op(IOObject io_object,
IOOp io_op, uint32 cnt,
uint64 bytes);
extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
+extern PgStat_Backend *pg_stat_get_backend_stats(int pid, BackendType *bktype);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
--
2.34.1
>From 84b2c89c0f1a719ddfe4cf87fc0f994e21667335 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 6 Jan 2025 10:00:00 +0000
Subject: [PATCH v9 5/5] per backend WAL statistics
Now that commit 9aea73fc61 added backend-level statistics to pgstats (and
per backend IO statistics) we can more easily add per backend statistics.
This commit adds per backend WAL statistics using the same layer as pg_stat_wal,
except that it is now possible to know how much WAL activity is happening in each
backend rather than an overall aggregate of all the activity. A function called
pg_stat_get_backend_wal() is added to access this data depending on the
PID of a backend.
The same limitation as in 9aea73fc61 persists, meaning that Auxiliary processes
are not included in this set of statistics.
XXX: bump catalog version
---
doc/src/sgml/monitoring.sgml | 19 ++++++
src/backend/utils/activity/pgstat_backend.c | 64 +++++++++++++++++++++
src/backend/utils/activity/pgstat_wal.c | 2 +
src/backend/utils/adt/pgstatfuncs.c | 26 ++++++++-
src/include/catalog/pg_proc.dat | 7 +++
src/include/pgstat.h | 13 +++--
src/include/utils/pgstat_internal.h | 3 +-
src/test/regress/expected/stats.out | 14 +++++
src/test/regress/sql/stats.sql | 6 ++
9 files changed, 145 insertions(+), 9 deletions(-)
15.9% doc/src/sgml/
39.4% src/backend/utils/activity/
15.5% src/backend/utils/adt/
8.8% src/include/catalog/
4.5% src/include/utils/
8.4% src/test/regress/expected/
6.4% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3dfd059b7ee..6b5c9b23c3a 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4856,6 +4856,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry id="pg-stat-get-backend-wal" role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_stat_get_backend_wal</primary>
+ </indexterm>
+ <function>pg_stat_get_backend_wal</function> ( <type>integer</type> )
+ <returnvalue>record</returnvalue>
+ </para>
+ <para>
+ Returns WAL statistics about the backend with the specified
+ process ID. The output fields are exactly the same as the ones in the
+ <structname>pg_stat_wal</structname> view.
+ </para>
+ <para>
+ The function does not return WAL statistics for the checkpointer,
+ the background writer, the startup process and the autovacuum launcher.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 3284d97fa95..b49e8539324 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -37,6 +37,14 @@
*/
static PgStat_BackendPending PendingBackendStats;
+/*
+ * WAL usage counters saved from pgWalUsage at the previous call to
+ * pgstat_report_wal(). This is used to calculate how much WAL usage
+ * happens between pgstat_report_wal() calls, by subtracting
+ * the previous counters from the current ones.
+ */
+static WalUsage prevBackendWalUsage;
+
/*
* Utility routines to report I/O stats for backends, kept here to avoid
* exposing PendingBackendStats to the outside world.
@@ -183,6 +191,57 @@ pgstat_flush_backend_entry_io(PgStat_EntryRef *entry_ref)
MemSet(&PendingBackendStats.pending_io, 0, sizeof(PgStat_PendingIO));
}
+/*
+ * To determine whether WAL usage happened.
+ */
+static bool
+pgstat_backend_wal_have_pending(void)
+{
+ return pgWalUsage.wal_records != prevBackendWalUsage.wal_records;
+}
+
+/*
+ * Flush out locally pending backend WAL statistics. Locking is managed
+ * by the caller.
+ */
+static void
+pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref)
+{
+ PgStatShared_Backend *shbackendent;
+ PgStat_WalCounters *bktype_shstats;
+ WalUsage wal_usage_diff = {0};
+
+ /*
+ * This function can be called even if nothing at all has happened. Avoid
+ * taking lock for nothing in that case.
+ */
+ if (!pgstat_backend_wal_have_pending())
+ return;
+
+ shbackendent = (PgStatShared_Backend *) entry_ref->shared_stats;
+ bktype_shstats = &shbackendent->stats.wal_counters;
+
+ /*
+ * We don't update the WAL usage portion of the local WalStats elsewhere.
+ * Calculate how much WAL usage counters were increased by subtracting the
+ * previous counters from the current ones.
+ */
+ WalUsageAccumDiff(&wal_usage_diff, &pgWalUsage, &prevBackendWalUsage);
+
+#define WALSTAT_ACC(fld, var_to_add) \
+ (bktype_shstats->fld += var_to_add.fld)
+ WALSTAT_ACC(wal_buffers_full, wal_usage_diff);
+ WALSTAT_ACC(wal_records, wal_usage_diff);
+ WALSTAT_ACC(wal_fpi, wal_usage_diff);
+ WALSTAT_ACC(wal_bytes, wal_usage_diff);
+#undef WALSTAT_ACC
+
+ /*
+ * Save the current counters for the subsequent calculation of WAL usage.
+ */
+ prevBackendWalUsage = pgWalUsage;
+}
+
/*
* Flush out locally pending backend statistics
*
@@ -210,6 +269,9 @@ pgstat_flush_backend(bool nowait, bits32 flags)
if (flags & PGSTAT_BACKEND_FLUSH_IO)
pgstat_flush_backend_entry_io(entry_ref);
+ if (flags & PGSTAT_BACKEND_FLUSH_WAL)
+ pgstat_flush_backend_entry_wal(entry_ref);
+
pgstat_unlock_entry(entry_ref);
return false;
@@ -257,6 +319,8 @@ pgstat_create_backend(ProcNumber procnum)
pgstat_unlock_entry(entry_ref);
MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending));
+
+ prevBackendWalUsage = pgWalUsage;
}
/*
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index 830b51a7b93..5051fa596b4 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -54,6 +54,8 @@ pgstat_report_wal(bool force)
/* flush wal stats */
pgstat_flush_wal(nowait);
+ pgstat_flush_backend(nowait, PGSTAT_BACKEND_FLUSH_WAL);
+
/* flush IO stats */
pgstat_flush_io(nowait);
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 13c91515480..4fca5b26fde 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1606,8 +1606,8 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
/*
* pg_stat_wal_build_tuple
*
- * Helper routine for pg_stat_get_wal() returning one tuple based on the contents
- * of wal_counters.
+ * Helper routine for pg_stat_get_wal() and pg_stat_get_backend_wal() returning
+ * one tuple based on the contents of wal_counters.
*/
static Datum
pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters,
@@ -1656,6 +1656,28 @@ pg_stat_wal_build_tuple(PgStat_WalCounters wal_counters,
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
}
+/*
+ * Returns WAL statistics for a backend with given PID.
+ */
+Datum
+pg_stat_get_backend_wal(PG_FUNCTION_ARGS)
+{
+ int pid;
+ PgStat_Backend *backend_stats;
+ PgStat_WalCounters bktype_stats;
+
+ pid = PG_GETARG_INT32(0);
+ backend_stats = pg_stat_get_backend_stats(pid, NULL);
+
+ if (!backend_stats)
+ PG_RETURN_NULL();
+
+ bktype_stats = backend_stats->wal_counters;
+
+ /* save tuples with data from this PgStat_WalCounters */
+ return (pg_stat_wal_build_tuple(bktype_stats, backend_stats->stat_reset_timestamp));
+}
+
/*
* Returns statistics of WAL activity
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index af9546de23d..399fccb1afb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5954,6 +5954,13 @@
proargmodes => '{o,o,o,o,o}',
proargnames => '{wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}',
prosrc => 'pg_stat_get_wal' },
+{ oid => '8037', descr => 'statistics: backend WAL activity',
+ proname => 'pg_stat_get_backend_wal', provolatile => 'v',
+ proparallel => 'r', prorettype => 'record', proargtypes => 'int4',
+ proallargtypes => '{int4,int8,int8,numeric,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_buffers_full,stats_reset}',
+ prosrc => 'pg_stat_get_backend_wal' },
{ oid => '6248', descr => 'statistics: information about WAL prefetching',
proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 3b71c3d4ed6..2665887b1e2 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -340,12 +340,6 @@ typedef struct PgStat_IO
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
-typedef struct PgStat_Backend
-{
- TimestampTz stat_reset_timestamp;
- PgStat_BktypeIO io_stats;
-} PgStat_Backend;
-
/* ---------
* PgStat_BackendPending Non-flushed backend stats.
* ---------
@@ -488,6 +482,13 @@ typedef struct PgStat_WalStats
TimestampTz stat_reset_timestamp;
} PgStat_WalStats;
+typedef struct PgStat_Backend
+{
+ TimestampTz stat_reset_timestamp;
+ PgStat_BktypeIO io_stats;
+ PgStat_WalCounters wal_counters;
+} PgStat_Backend;
+
/*
* Functions in pgstat.c
*/
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 36d228e3558..d5557e6e998 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -622,7 +622,8 @@ extern void pgstat_archiver_snapshot_cb(void);
/* flags for pgstat_flush_backend() */
#define PGSTAT_BACKEND_FLUSH_IO (1 << 0) /* Flush I/O statistics */
-#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO)
+#define PGSTAT_BACKEND_FLUSH_WAL (1 << 1) /* Flush WAL statistics */
+#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL)
extern bool pgstat_flush_backend(bool nowait, bits32 flags);
extern bool pgstat_backend_flush_cb(bool nowait);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 093e6368dbb..b3c303c98cb 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -832,6 +832,8 @@ SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELEC
SELECT num_requested AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
-- Test pg_stat_wal (and make a temp table so our temp schema exists)
SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
+-- Test pg_stat_get_backend_wal (and make a temp table so our temp schema exists)
+SELECT wal_bytes AS backend_wal_bytes_before from pg_stat_get_backend_wal(pg_backend_pid()) \gset
CREATE TEMP TABLE test_stats_temp AS SELECT 17;
DROP TABLE test_stats_temp;
-- Checkpoint twice: The checkpointer reports stats after reporting completion
@@ -851,6 +853,18 @@ SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
t
(1 row)
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT wal_bytes > :backend_wal_bytes_before FROM pg_stat_get_backend_wal(pg_backend_pid());
+ ?column?
+----------
+ t
+(1 row)
+
-- Test pg_stat_get_backend_idset() and some allied functions.
-- In particular, verify that their notion of backend ID matches
-- our temp schema index.
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 0a44e14d9f4..ad3f7b7e66a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -423,6 +423,9 @@ SELECT num_requested AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
-- Test pg_stat_wal (and make a temp table so our temp schema exists)
SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
+-- Test pg_stat_get_backend_wal (and make a temp table so our temp schema exists)
+SELECT wal_bytes AS backend_wal_bytes_before from pg_stat_get_backend_wal(pg_backend_pid()) \gset
+
CREATE TEMP TABLE test_stats_temp AS SELECT 17;
DROP TABLE test_stats_temp;
@@ -435,6 +438,9 @@ CHECKPOINT;
SELECT num_requested > :rqst_ckpts_before FROM pg_stat_checkpointer;
SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
+SELECT pg_stat_force_next_flush();
+SELECT wal_bytes > :backend_wal_bytes_before FROM pg_stat_get_backend_wal(pg_backend_pid());
+
-- Test pg_stat_get_backend_idset() and some allied functions.
-- In particular, verify that their notion of backend ID matches
-- our temp schema index.
--
2.34.1
