v3-0002-count-client-xacts-only.patch

From df6f10bd9ddf2b5659bf19d928763530a9af685c Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <nik@postgres.ai>
Date: Wed, 8 Jul 2026 08:04:58 -0700
Subject: [PATCH v3 2/2] Count xact_commit/xact_rollback only for client
 backends

pg_stat_database.xact_commit and xact_rollback have historically counted
every top-level transaction end in a database, including the internal
transactions run by other server processes such as autovacuum workers and
walsenders.  Those transactions are not user-visible transaction outcomes,
and counting them makes the ratio of commits to rollbacks harder to
interpret for monitoring.

Adopt the narrower definition discussed on the list: count a transaction in
xact_commit/xact_rollback only when it is executed by a regular client
backend (AmRegularBackendProcess()).  The "parallel" argument, previously
used to keep parallel workers out of these counters, is now redundant --
parallel workers are background workers and are excluded by the backend-type
test -- so it is removed from both AtEOXact_PgStat() and
AtEOXact_PgStat_Database().

This is a monitoring-visible behavior change beyond the server processes
themselves: logical replication apply workers are background workers, so the
transactions they replay on a subscriber are no longer counted in the
subscriber's xact_commit.  That is intended, and is called out here so the
change in subscriber statistics is not mistaken for a regression.

Because it changes long-standing counter semantics, this is master-only.
The back-patchable fix for the specific logical-decoding case that prompted
this is handled separately; this commit implements the general definition on
top of it.

Discussion: https://postgr.es/m/CAM527d_EbU5Li4a5FdKQjYsdF-4Lqr_i3jXmZOm7Wbb%3DQ2KzTw%40mail.gmail.com
---
 doc/src/sgml/monitoring.sgml                 | 12 ++++++++----
 src/backend/access/transam/twophase.c        |  2 +-
 src/backend/access/transam/xact.c            |  4 ++--
 src/backend/utils/activity/pgstat_database.c | 14 +++++++++++---
 src/backend/utils/activity/pgstat_xact.c     |  4 ++--
 src/include/pgstat.h                         |  2 +-
 src/include/utils/pgstat_internal.h          |  2 +-
 7 files changed, 26 insertions(+), 14 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 12b9ee20d4a..7e0a7550fa6 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3895,8 +3895,10 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>xact_commit</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of transactions in this database that have been
-       committed
+       Number of transactions in this database that have been committed by
+       regular client backends.  Transactions performed by other server
+       processes, such as autovacuum workers, walsenders, and logical
+       replication apply workers, are not counted.
       </para></entry>
      </row>
 
@@ -3905,8 +3907,10 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>xact_rollback</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of transactions in this database that have been
-       rolled back
+       Number of transactions in this database that have been rolled back by
+       regular client backends.  Transactions performed by other server
+       processes, such as autovacuum workers, walsenders, and logical
+       replication apply workers, are not counted.
       </para></entry>
      </row>
 
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index 1035e8b3fc7..0408f8794b4 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -1676,7 +1676,7 @@ FinishPreparedTransaction(const char *gid, bool isCommit)
 	LWLockRelease(TwoPhaseStateLock);
 
 	/* Count the prepared xact as committed or aborted */
-	AtEOXact_PgStat(isCommit, false);
+	AtEOXact_PgStat(isCommit);
 
 	/*
 	 * And now we can clean up any files we may have left.
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d74b231caf3..4e627e3306b 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2515,7 +2515,7 @@ CommitTransaction(void)
 	AtEOXact_ComboCid();
 	AtEOXact_HashTables(true);
 	AtEOXact_RI(true);
-	AtEOXact_PgStat(true, is_parallel_worker);
+	AtEOXact_PgStat(true);
 	AtEOXact_Snapshot(true, false);
 	AtEOXact_ApplyLauncher(true);
 	AtEOXact_LogicalRepWorkers(true);
@@ -3042,7 +3042,7 @@ AbortTransaction(void)
 		AtEOXact_ComboCid();
 		AtEOXact_HashTables(false);
 		AtEOXact_RI(false);
-		AtEOXact_PgStat(false, is_parallel_worker);
+		AtEOXact_PgStat(false);
 		AtEOXact_ApplyLauncher(false);
 		AtEOXact_LogicalRepWorkers(false);
 		AtEOXact_LogicalCtl();
diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c
index 7f3bc016593..efe546931e3 100644
--- a/src/backend/utils/activity/pgstat_database.c
+++ b/src/backend/utils/activity/pgstat_database.c
@@ -17,6 +17,7 @@
 
 #include "postgres.h"
 
+#include "miscadmin.h"
 #include "storage/standby.h"
 #include "utils/pgstat_internal.h"
 #include "utils/timestamp.h"
@@ -292,10 +293,17 @@ pgstat_fetch_stat_dbentry(Oid dboid)
 }
 
 void
-AtEOXact_PgStat_Database(bool isCommit, bool parallel)
+AtEOXact_PgStat_Database(bool isCommit)
 {
-	/* Don't count parallel worker transaction stats */
-	if (!parallel)
+	/*
+	 * Only count transactions of regular client backends in
+	 * xact_commit/xact_rollback.  Transactions performed by other server
+	 * processes -- autovacuum workers, walsenders, and, less obviously, logical
+	 * replication apply workers -- are not user-visible transaction outcomes and
+	 * would otherwise inflate these counters.  (This subsumes the previous
+	 * !parallel check, since parallel workers are bgworkers.)
+	 */
+	if (AmRegularBackendProcess())
 	{
 		/*
 		 * Count transaction commit or abort.  (We use counters, not just
diff --git a/src/backend/utils/activity/pgstat_xact.c b/src/backend/utils/activity/pgstat_xact.c
index d1be3733e4d..6c3944b5f22 100644
--- a/src/backend/utils/activity/pgstat_xact.c
+++ b/src/backend/utils/activity/pgstat_xact.c
@@ -38,7 +38,7 @@ static bool pgStatSkipXactCounters = false;
  * Called from access/transam/xact.c at top-level transaction commit/abort.
  */
 void
-AtEOXact_PgStat(bool isCommit, bool parallel)
+AtEOXact_PgStat(bool isCommit)
 {
 	PgStat_SubXactStatus *xact_state;
 	bool		skip_xact_counters = pgStatSkipXactCounters;
@@ -49,7 +49,7 @@ AtEOXact_PgStat(bool isCommit, bool parallel)
 	 */
 	pgStatSkipXactCounters = false;
 	if (!skip_xact_counters)
-		AtEOXact_PgStat_Database(isCommit, parallel);
+		AtEOXact_PgStat_Database(isCommit);
 
 	/* handle transactional stats information */
 	xact_state = pgStatXactStack;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ff0a2e16467..4fa84061057 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -827,7 +827,7 @@ extern PgStat_StatSubEntry *pgstat_fetch_stat_subscription(Oid subid);
  * Functions in pgstat_xact.c
  */
 
-extern void AtEOXact_PgStat(bool isCommit, bool parallel);
+extern void AtEOXact_PgStat(bool isCommit);
 extern void pgstat_suppress_xact_counters(void);
 extern void pgstat_clear_xact_counter_suppression(void);
 extern void AtEOSubXact_PgStat(bool isCommit, int nestDepth);
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index b3dc3ff7d8b..ad9bb649e34 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -737,7 +737,7 @@ extern void pgstat_checkpointer_snapshot_cb(void);
 
 extern void pgstat_report_disconnect(Oid dboid);
 extern void pgstat_update_dbstats(TimestampTz ts);
-extern void AtEOXact_PgStat_Database(bool isCommit, bool parallel);
+extern void AtEOXact_PgStat_Database(bool isCommit);
 
 extern PgStat_StatDBEntry *pgstat_prep_database_pending(Oid dboid);
 extern void pgstat_reset_database_timestamp(Oid dboid, TimestampTz ts);
-- 
2.50.1 (Apple Git-155)