Hi,
On Thu, Aug 07, 2025 at 08:17:26AM +0000, Bertrand Drouvot wrote:
> Hi,
>
> On Mon, Aug 04, 2025 at 02:20:48PM +0000, Bertrand Drouvot wrote:
> > This patch is pretty straightforward as it relies on the existing per
> > backend
> > statistics machinery that has been added in 9aea73fc61d (so that there is
> > not
> > that much design to discuss).
>
> Still, while working on adding more backend stats (more on that later), I
> realized that in v1, I missed to use pgstat_report_fixed (recently added in
> 793928c2d5a): the attached fixes that.
>
> Also, I think it's better to put the new xact pending counters in the existing
> PgStat_BackendPending, done that way in the attached.
Another metric that could be useful is to track the XIDs generated by backend.
That would help to see if a backend is consuming XIDs at a high rate.
We can not rely on the number of commits or rollbacks as they take into account
the virtual transactions.
So a new counter has been added in 0002 attached. Also, v3 changes the way
the statistics are displayed. I've in mind to add much more statistics per
backend
(such a number of seqscans, vacuum count, analyze count..., I'll open a
dedicated
thread for those) and I think that a single view to display them all makes
more sense than a lot of individual functions. This view is added in 0003.
To sum up, v3 contains:
0001 -
Adding per backend commit and rollback counters
It relies on the existing per backend statistics that has been added in
9aea73fc61d. A new function is called in AtEOXact_PgStat() to increment those
two new counters.
0002 -
Adding XID generation count per backend
This patch adds a new counter to record the number of XIDs generated per
backend. It will help to detect if a backend is consuming XIDs at a high rate.
Virtual transactions are not taken into account on purpose, we do want to track
only the XID where there is a risk of wraparound.
The counter is not part of PgStat_BackendPending, because we want to avoid
an extra function call in this code path to increment the counter in
PendingBackendStats. The counter increment here behaves more or less the same as
we do for WAL statistics.
0003 -
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 XIDs generated, the number of commits, the number of rollbacks 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.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From cea5e221c3972d162ddead3f0b7a81cc1555461e Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 4 Aug 2025 08:14:02 +0000
Subject: [PATCH v3 1/3] Adding per backend commit and rollback counters
It relies on the existing per backend statistics that has been added in
9aea73fc61d. A new function is called in AtEOXact_PgStat() to increment those
two new counters.
---
src/backend/utils/activity/pgstat_backend.c | 57 +++++++++++++++++++++
src/backend/utils/activity/pgstat_xact.c | 1 +
src/include/pgstat.h | 8 +++
src/include/utils/pgstat_internal.h | 4 +-
4 files changed, 69 insertions(+), 1 deletion(-)
78.9% src/backend/utils/activity/
11.2% src/include/utils/
9.8% src/include/
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index 8714a85e2d9..bf164854c4b 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -47,6 +47,11 @@ static bool backend_has_iostats = false;
*/
static WalUsage prevBackendWalUsage;
+/*
+ * For backend commit and rollback statistics.
+ */
+static bool backend_has_xactstats = false;
+
/*
* Utility routines to report I/O stats for backends, kept here to avoid
* exposing PendingBackendStats to the outside world.
@@ -259,6 +264,34 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref)
prevBackendWalUsage = pgWalUsage;
}
+/*
+ * Flush out locally pending backend transaction statistics. Locking is managed
+ * by the caller.
+ */
+static void
+pgstat_flush_backend_entry_xact(PgStat_EntryRef *entry_ref)
+{
+ PgStatShared_Backend *shbackendent;
+
+ /*
+ * This function can be called even if nothing at all has happened for
+ * transaction statistics. In this case, avoid unnecessarily modifying
+ * the stats entry.
+ */
+ if (!backend_has_xactstats)
+ return;
+
+ shbackendent = (PgStatShared_Backend *) entry_ref->shared_stats;
+
+ shbackendent->stats.xact_commit += PendingBackendStats.pending_xact_commit;
+ shbackendent->stats.xact_rollback += PendingBackendStats.pending_xact_rollback;
+
+ PendingBackendStats.pending_xact_commit = 0;
+ PendingBackendStats.pending_xact_rollback = 0;
+
+ backend_has_xactstats = false;
+}
+
/*
* Flush out locally pending backend statistics
*
@@ -283,6 +316,10 @@ pgstat_flush_backend(bool nowait, bits32 flags)
pgstat_backend_wal_have_pending())
has_pending_data = true;
+ /* Some transaction data pending? */
+ if ((flags & PGSTAT_BACKEND_FLUSH_XACT) && backend_has_xactstats)
+ has_pending_data = true;
+
if (!has_pending_data)
return false;
@@ -298,6 +335,9 @@ pgstat_flush_backend(bool nowait, bits32 flags)
if (flags & PGSTAT_BACKEND_FLUSH_WAL)
pgstat_flush_backend_entry_wal(entry_ref);
+ if (flags & PGSTAT_BACKEND_FLUSH_XACT)
+ pgstat_flush_backend_entry_xact(entry_ref);
+
pgstat_unlock_entry(entry_ref);
return false;
@@ -400,3 +440,20 @@ pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
{
((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
}
+
+void
+AtEOXact_PgStat_Backend(bool isCommit, bool parallel)
+{
+ /* Don't count parallel worker transaction stats */
+ if (!parallel)
+ {
+ /* Count transaction commit or abort */
+ if (isCommit)
+ PendingBackendStats.pending_xact_commit++;
+ else
+ PendingBackendStats.pending_xact_rollback++;
+
+ backend_has_xactstats = true;
+ pgstat_report_fixed = true;
+ }
+}
diff --git a/src/backend/utils/activity/pgstat_xact.c b/src/backend/utils/activity/pgstat_xact.c
index bc9864bd8d9..cd1c501c165 100644
--- a/src/backend/utils/activity/pgstat_xact.c
+++ b/src/backend/utils/activity/pgstat_xact.c
@@ -42,6 +42,7 @@ AtEOXact_PgStat(bool isCommit, bool parallel)
PgStat_SubXactStatus *xact_state;
AtEOXact_PgStat_Database(isCommit, parallel);
+ AtEOXact_PgStat_Backend(isCommit, parallel);
/* handle transactional stats information */
xact_state = pgStatXactStack;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 202bd2d5ace..9efc0e10ebf 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -490,6 +490,8 @@ typedef struct PgStat_Backend
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO io_stats;
PgStat_WalCounters wal_counters;
+ PgStat_Counter xact_commit;
+ PgStat_Counter xact_rollback;
} PgStat_Backend;
/* ---------
@@ -502,6 +504,12 @@ typedef struct PgStat_BackendPending
* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO.
*/
PgStat_PendingIO pending_io;
+
+ /*
+ * Transaction statistics pending flush.
+ */
+ PgStat_Counter pending_xact_commit;
+ PgStat_Counter pending_xact_rollback;
} PgStat_BackendPending;
/*
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 6cf00008f63..23ea8ffd618 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -616,12 +616,14 @@ 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_WAL (1 << 1) /* Flush WAL statistics */
-#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL)
+#define PGSTAT_BACKEND_FLUSH_XACT (1 << 2) /* Flush xact statistics */
+#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL | PGSTAT_BACKEND_FLUSH_XACT)
extern bool pgstat_flush_backend(bool nowait, bits32 flags);
extern bool pgstat_backend_flush_cb(bool nowait);
extern void pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header,
TimestampTz ts);
+extern void AtEOXact_PgStat_Backend(bool isCommit, bool parallel);
/*
* Functions in pgstat_bgwriter.c
--
2.34.1
>From 2c861d9c9e6d23bb1af7cfc3fee015a34141d184 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Fri, 8 Aug 2025 15:58:05 +0000
Subject: [PATCH v3 2/3] Adding XID generation count per backend
This commit adds a new counter to record the number of XIDs generated per
backend. It will help to detect if a backend is consuming XIDs at a high rate.
Virtual transactions are not taken into account on purpose, we do want to track
only the XID where there is a risk of wraparound.
The counter is not part of PgStat_BackendPending, because we want to avoid
an extra function call in this code path to increment the counter in
PendingBackendStats. The counter increment here behaves more or less the same as
we do for WAL statistics.
---
src/backend/access/transam/varsup.c | 2 ++
src/backend/utils/activity/pgstat_backend.c | 6 +++++-
src/include/pgstat.h | 3 +++
3 files changed, 10 insertions(+), 1 deletion(-)
10.1% src/backend/access/transam/
69.3% src/backend/utils/activity/
20.5% src/include/
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index fe895787cb7..26c72cbe091 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -21,6 +21,7 @@
#include "access/xlogutils.h"
#include "commands/dbcommands.h"
#include "miscadmin.h"
+#include "pgstat.h"
#include "postmaster/autovacuum.h"
#include "storage/pmsignal.h"
#include "storage/proc.h"
@@ -257,6 +258,7 @@ GetNewTransactionId(bool isSubXact)
/* LWLockRelease acts as barrier */
MyProc->xid = xid;
ProcGlobal->xids[MyProc->pgxactoff] = xid;
+ XidGenCount++;
}
else
{
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index bf164854c4b..8b530c510b9 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -52,6 +52,8 @@ static WalUsage prevBackendWalUsage;
*/
static bool backend_has_xactstats = false;
+PgStat_Counter XidGenCount = 0;
+
/*
* Utility routines to report I/O stats for backends, kept here to avoid
* exposing PendingBackendStats to the outside world.
@@ -285,9 +287,11 @@ pgstat_flush_backend_entry_xact(PgStat_EntryRef *entry_ref)
shbackendent->stats.xact_commit += PendingBackendStats.pending_xact_commit;
shbackendent->stats.xact_rollback += PendingBackendStats.pending_xact_rollback;
+ shbackendent->stats.xid_count += XidGenCount;
PendingBackendStats.pending_xact_commit = 0;
PendingBackendStats.pending_xact_rollback = 0;
+ XidGenCount = 0;
backend_has_xactstats = false;
}
@@ -317,7 +321,7 @@ pgstat_flush_backend(bool nowait, bits32 flags)
has_pending_data = true;
/* Some transaction data pending? */
- if ((flags & PGSTAT_BACKEND_FLUSH_XACT) && backend_has_xactstats)
+ if ((flags & PGSTAT_BACKEND_FLUSH_XACT) && (backend_has_xactstats || XidGenCount > 0))
has_pending_data = true;
if (!has_pending_data)
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 9efc0e10ebf..94dd24b3eac 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -492,6 +492,7 @@ typedef struct PgStat_Backend
PgStat_WalCounters wal_counters;
PgStat_Counter xact_commit;
PgStat_Counter xact_rollback;
+ PgStat_Counter xid_count;
} PgStat_Backend;
/* ---------
@@ -571,6 +572,8 @@ extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid,
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
extern void pgstat_create_backend(ProcNumber procnum);
+extern PGDLLIMPORT PgStat_Counter XidGenCount;
+
/*
* Functions in pgstat_bgwriter.c
*/
--
2.34.1
>From 5ccf40591a166ebcce1e268d618915dd31eecd38 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Sat, 9 Aug 2025 14:22:36 +0000
Subject: [PATCH v3 3/3] 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 XIDs generated, the number of commits, the number of rollbacks 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 | 115 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 9 +++
src/backend/utils/adt/pgstatfuncs.c | 57 +++++++++++++
src/include/catalog/pg_proc.dat | 9 +++
src/test/regress/expected/rules.out | 6 ++
src/test/regress/expected/stats.out | 17 ++++
src/test/regress/sql/stats.sql | 10 +++
7 files changed, 223 insertions(+)
51.1% doc/src/sgml/
3.2% src/backend/catalog/
23.9% src/backend/utils/adt/
7.2% src/include/catalog/
9.4% src/test/regress/expected/
4.9% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3f4a27a736e..0146c812b40 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -320,6 +320,20 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry>
+ <structname>pg_stat_backend</structname>
+ <indexterm><primary>pg_stat_backend</primary></indexterm>
+ </entry>
+ <entry>
+ One row per server process, showing statistics related to
+ the current activity of that process, such as number of commits and
+ rollbacks.
+ See <link linkend="monitoring-pg-stat-backend-view">
+ <structname>pg_stat_backend</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_replication</structname><indexterm><primary>pg_stat_replication</primary></indexterm></entry>
<entry>One row per WAL sender process, showing statistics about
@@ -1172,6 +1186,91 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</note>
</sect2>
+ <sect2 id="monitoring-pg-stat-backend-view">
+ <title><structname>pg_stat_backend</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_backend</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_backend</structname> view will have one row
+ per server process, showing statistics related to
+ the current activity of that process.
+ </para>
+
+ <table id="pg-stat-backend-view" xreflabel="pg_stat_backend">
+ <title><structname>pg_stat_backend</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 this backend
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>xid_count</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of XID that have been generated by the backend. It does not take
+ into account virtual transaction ID on purpose.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>xact_commit</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of transactions that have been committed.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>xact_rollback</structfield> <type>bigint</type>
+ </para>
+ <para>
+ The number of transactions that have been rolled back.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The view does not return statistics for the checkpointer,
+ the background writer, the startup process and the autovacuum launcher.
+ </para>
+ </note>
+ </sect2>
+
<sect2 id="monitoring-pg-stat-replication-view">
<title><structname>pg_stat_replication</structname></title>
@@ -4921,6 +5020,22 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_stat_get_backend_statistics</primary>
+ </indexterm>
+ <function>pg_stat_get_backend_statistics</function> ( <type>integer</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Returns a record of statistics bout the backend with the specified
+ process ID, or one record for each active backend in the system
+ if <literal>NULL</literal> is specified. The fields returned are a
+ subset of those in the <structname>pg_stat_backend</structname> view.
+ </para></entry>
+ </row>
+
<row>
<entry id="pg-stat-get-backend-wal" role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1b3c5a55882..96a1a556773 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -911,6 +911,15 @@ 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.xid_count,
+ S.xact_commit,
+ S.xact_rollback,
+ 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..dcb20009bec 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -685,6 +685,63 @@ 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 5
+ 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->xid_count);
+ values[2] = Int64GetDatum(backend_stats->xact_commit);
+ values[3] = Int64GetDatum(backend_stats->xact_rollback);
+
+ if (backend_stats->stat_reset_timestamp != 0)
+ values[4] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
+ else
+ nulls[4] = 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..dc0c0db6358 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,int8,int8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o}',
+ proargnames => '{pid,pid,xid_count,xact_commit,xact_rollback,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..a107f6ca44d 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1847,6 +1847,12 @@ 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,
+ xid_count,
+ xact_commit,
+ xact_rollback,
+ stats_reset
+ FROM pg_stat_get_backend_statistics(NULL::integer) s(pid, xid_count, xact_commit, xact_rollback, 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..a4b6d27c31d 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -135,11 +135,28 @@ INSERT INTO trunc_stats_test1 DEFAULT VALUES;
INSERT INTO trunc_stats_test1 DEFAULT VALUES;
UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2);
DELETE FROM trunc_stats_test1 WHERE id = 3;
+-- in passing, check that backend's commit is incrementing
+SELECT xact_commit AS xact_commit_before
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
BEGIN;
UPDATE trunc_stats_test1 SET id = id + 100;
TRUNCATE trunc_stats_test1;
INSERT INTO trunc_stats_test1 DEFAULT VALUES;
COMMIT;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT xact_commit AS xact_commit_after
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+SELECT :xact_commit_after > :xact_commit_before;
+ ?column?
+----------
+ t
+(1 row)
+
-- use a savepoint: 1 insert, 1 live
BEGIN;
INSERT INTO trunc_stats_test2 DEFAULT VALUES;
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 54e72866344..f25368d1d25 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -58,12 +58,22 @@ INSERT INTO trunc_stats_test1 DEFAULT VALUES;
UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2);
DELETE FROM trunc_stats_test1 WHERE id = 3;
+-- in passing, check that backend's commit is incrementing
+SELECT xact_commit AS xact_commit_before
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+
BEGIN;
UPDATE trunc_stats_test1 SET id = id + 100;
TRUNCATE trunc_stats_test1;
INSERT INTO trunc_stats_test1 DEFAULT VALUES;
COMMIT;
+SELECT pg_stat_force_next_flush();
+SELECT xact_commit AS xact_commit_after
+ FROM pg_stat_backend WHERE pid = pg_backend_pid() \gset
+
+SELECT :xact_commit_after > :xact_commit_before;
+
-- use a savepoint: 1 insert, 1 live
BEGIN;
INSERT INTO trunc_stats_test2 DEFAULT VALUES;
--
2.34.1
