Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w7VL1-005Nrn-1Y for pgsql-hackers@arkaria.postgresql.org; Tue, 31 Mar 2026 09:24:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w7VKz-009AAL-1t for pgsql-hackers@arkaria.postgresql.org; Tue, 31 Mar 2026 09:24:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w7VKz-009AAD-0k for pgsql-hackers@lists.postgresql.org; Tue, 31 Mar 2026 09:24:41 +0000 Received: from mail-wm1-x32c.google.com ([2a00:1450:4864:20::32c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w7VKw-000000029HX-3Ahd for pgsql-hackers@lists.postgresql.org; Tue, 31 Mar 2026 09:24:41 +0000 Received: by mail-wm1-x32c.google.com with SMTP id 5b1f17b1804b1-482f454be5bso61552955e9.0 for ; Tue, 31 Mar 2026 02:24:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1774949078; x=1775553878; darn=lists.postgresql.org; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:from:to :cc:subject:date:message-id:reply-to; bh=m00OidQzL8oaMwZFK5nd0JK1m0lSdWvSPv6QilqcHLw=; b=g7ByFgJZJSGb2B1ZvABPD98v0u/4MFA75q9pqz0Ijn7bD9OVSbcaiQsnTO1MBrb7Ij vRnldQ1hQLhgD9KlbG4wIiu/vtclGh69B/BTjPKZUcUXitQ401XnzHzFuUv22YjMkl8B 5G9P1we9XO9MWGoihnxVB1/326p90kOQduh5B7k4159DIRxEZkCeMwCLbvQz6kHbXZHP VqdwhzkK93vWcY2D7Bun1ppLLag/Czday68J1HVxBYBavgT+JyM73wBn8PxpgTtmvkqq nqVmj/wAJZC/6ZeNUqtRZxvurwOyeVRf9jlytReFNfB8yD/rkDoS84yh4rixETWIT+17 XLcw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1774949078; x=1775553878; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=m00OidQzL8oaMwZFK5nd0JK1m0lSdWvSPv6QilqcHLw=; b=opCOpY7Xe3T+LFjv8nBgf5iOM3U8MbNaPdSOUwGYCAd+WK7ax36cL7OgFeIKOisSCx CkGiIJXyU+1ZA16MrwIYLvY4/7TaxJxtSMeLeECNIKHsW0D/t2UhtdH8y33jPEbtCBmo mIW2KI+66LuYeuyAlkNeiEAqEMbmPLRZOYZBgdPu9bX7p5ZeGKwv9/PJYuDIxO09WcyR DDqdihvod3tH0jnL986dQM2I807ocYu3GQYn2TIixjOwjvQgemMrlpDXGXxOOB3CWs3v ODe13zpr41ZWon75+E+aKWCjYGmYEcEwQh36APffEpkxYl0cRDvgwzH4AtoHwZgRyDfE rkng== X-Gm-Message-State: AOJu0YyPuHv/YfVmGW7QEl0flgVfEdHHTiNdyO9Bh2iMwKamBFdh6HRm jufN8N+gWtQdcIX/4ikNyCKyPsRiSlVVFQ7adPHXtzV1+iIiU42LRxpGaqiVrQ== X-Gm-Gg: ATEYQzyU6wPbxvv5Z8Fxb4+Tk4pksRvm6xqtnfKrkB2h2q2KSUFZwpOwdBCx0N8pZ77 Egy4wkdKWtUmFww4P/huBSUKCx2sHW+rvkXerjpAlLT9+S3AqU0fnng4jIg24B245Be8AHzv5nH zQav//hkzzz593M8aa24R280tIP6/zT8RiwxtgSa4O1E3QmItb+8xPJQcJhN/Ji7no7etUHXJ7j v5MiqNgULNFUV9N7tOCOcV61dn3NFSr+qlQjrxpLTLfFov1F1Xdz8tHLEDxxOgvb/YizyTcmyyE uV+sPvmx03FK9c0PzsJTMWTwW/feI7V4lRHeW6FpOBSudnb1oi7mjE3XeMfTx0nnWqXKcDw1kK9 UczF87w/hMF+hU0HfYeykZyp0TID5PRicvAYh3WiD5ygWxSNMD/+jWqQMz/lC/xOnigBehW8y3G yTSHf+xTN33KFLXRRYndQ00XUzovSztA7BXpoULR6u0q/7U7dxLON+VQpp+IaCW7+a0wCTFJFTX PGfLTJnNi4VY6X11GMsLcXd55k7cRRT8aooPHt5p4eKD+QsVnEYFCtp3A== X-Received: by 2002:a05:600c:1986:b0:486:faa8:9e4 with SMTP id 5b1f17b1804b1-488783a6913mr42546695e9.12.1774949077500; Tue, 31 Mar 2026 02:24:37 -0700 (PDT) Received: from ip-10-97-1-34.eu-west-3.compute.internal (ec2-15-237-197-144.eu-west-3.compute.amazonaws.com. [15.237.197.144]) by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-4887e884dacsm30811795e9.15.2026.03.31.02.24.36 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 31 Mar 2026 02:24:36 -0700 (PDT) Date: Tue, 31 Mar 2026 09:24:35 +0000 From: Bertrand Drouvot To: Kuba Knysiak Cc: pgsql-hackers@lists.postgresql.org Subject: Re: Adding per backend commit and rollback counters Message-ID: References: <177490195327.942.4612952714548351097.pgcf@coridan.postgresql.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="uN5gGi2rtCNSVbpC" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <177490195327.942.4612952714548351097.pgcf@coridan.postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --uN5gGi2rtCNSVbpC Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit Hi, On Mon, Mar 30, 2026 at 08:19:13PM +0000, Kuba Knysiak wrote: > Hello, > after reviewing the patch together with Miłosz, we found the following: Thanks for the review! > - In pgstatfuncs.c, we call pgstat_fetch_stat_backend_by_pid(beentry->st_procpid, NULL) > for each backend row. That path acquires ProcArrayLock via BackendPidGetProc(), > so this repeats lock acquisition for every row. We could simplify this and avoid > taking the lock altogether by fetching directly with > pgstat_fetch_stat_backend(local_beentry->proc_number). Yeah, I think that's a good point. Done that way in the attached. Also adding a check on the backend type as it was done in pgstat_fetch_stat_backend_by_pid(). > Also, shouldn't this patch bump catversion? Yes and that was mentioned in the 0003 commit message. We usually don't change it in the patch itself (could easily produce rebase noise) but just put an XXX in the commit message so that it's not forgoten when pushed. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com --uN5gGi2rtCNSVbpC Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0001-Adding-per-backend-commit-and-rollback-counters.patch" From b0f2d971866e9ebc32de8b6129fcb02c4cf4f37e Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot Date: Mon, 4 Aug 2025 08:14:02 +0000 Subject: [PATCH v5 1/3] Adding per backend commit and rollback counters It relies on the existing per backend statistics that has been added in 9aea73fc61d. The new pending counters are updated when the database ones are flushed (to reduce the overhead of incrementing new counters). --- src/backend/utils/activity/pgstat_backend.c | 42 +++++++++++++++++++- src/backend/utils/activity/pgstat_database.c | 7 ++++ src/include/pgstat.h | 15 +++++++ src/include/utils/pgstat_internal.h | 3 +- 4 files changed, 65 insertions(+), 2 deletions(-) 74.4% src/backend/utils/activity/ 7.8% src/include/utils/ 17.7% src/include/ diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index 7727fed3bda..42bfb9cd38f 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -37,7 +37,7 @@ * reported within critical sections so we use static memory in order to avoid * memory allocation. */ -static PgStat_BackendPending PendingBackendStats; +PgStat_BackendPending PendingBackendStats; static bool backend_has_iostats = false; /* @@ -48,6 +48,11 @@ static bool backend_has_iostats = false; */ static WalUsage prevBackendWalUsage; +/* + * For backend commit and rollback statistics. + */ +bool backend_has_xactstats = false; + /* * Utility routines to report I/O stats for backends, kept here to avoid * exposing PendingBackendStats to the outside world. @@ -261,6 +266,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 * @@ -285,6 +318,10 @@ pgstat_flush_backend(bool nowait, uint32 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; @@ -300,6 +337,9 @@ pgstat_flush_backend(bool nowait, uint32 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; diff --git a/src/backend/utils/activity/pgstat_database.c b/src/backend/utils/activity/pgstat_database.c index 933dcb5cae5..218ff5e653c 100644 --- a/src/backend/utils/activity/pgstat_database.c +++ b/src/backend/utils/activity/pgstat_database.c @@ -352,6 +352,13 @@ pgstat_update_dbstats(TimestampTz ts) dbentry->blk_read_time += pgStatBlockReadTime; dbentry->blk_write_time += pgStatBlockWriteTime; + /* Do the same for backend stats */ + PendingBackendStats.pending_xact_commit += pgStatXactCommit; + PendingBackendStats.pending_xact_rollback += pgStatXactRollback; + + backend_has_xactstats = true; + pgstat_report_fixed = true; + if (pgstat_should_report_connstat()) { long secs; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 8e3549c3752..5d73c4dfbcf 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -523,6 +523,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; /* --------- @@ -535,6 +537,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; /* @@ -844,6 +852,13 @@ extern PGDLLIMPORT int pgstat_track_functions; extern PGDLLIMPORT int pgstat_fetch_consistency; +/* + * Variables in pgstat_backend.c + */ + +extern PGDLLIMPORT PgStat_BackendPending PendingBackendStats; +extern PGDLLIMPORT bool backend_has_xactstats; + /* * Variables in pgstat_bgwriter.c */ diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index eed4c6b359c..8077c65e938 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -707,7 +707,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_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, uint32 flags); extern bool pgstat_backend_flush_cb(bool nowait); -- 2.34.1 --uN5gGi2rtCNSVbpC Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0002-Adding-XID-generation-count-per-backend.patch" From f85e8d368415003fe42337706d99d9d3fc4dc805 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot Date: Fri, 8 Aug 2025 15:58:05 +0000 Subject: [PATCH v5 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. --- src/backend/access/transam/varsup.c | 4 ++++ src/backend/utils/activity/pgstat_backend.c | 2 ++ src/include/pgstat.h | 2 ++ 3 files changed, 8 insertions(+) 43.8% src/backend/access/transam/ 36.6% src/backend/utils/activity/ 19.4% src/include/ diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c index 1441a051773..fe625d00f7d 100644 --- a/src/backend/access/transam/varsup.c +++ b/src/backend/access/transam/varsup.c @@ -24,6 +24,7 @@ #include "storage/pmsignal.h" #include "storage/proc.h" #include "utils/lsyscache.h" +#include "utils/pgstat_internal.h" #include "utils/syscache.h" @@ -261,6 +262,9 @@ GetNewTransactionId(bool isSubXact) /* LWLockRelease acts as barrier */ MyProc->xid = xid; ProcGlobal->xids[MyProc->pgxactoff] = xid; + PendingBackendStats.pending_xid_count++; + backend_has_xactstats = true; + pgstat_report_fixed = true; } else { diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index 42bfb9cd38f..32ee48eab51 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -287,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 += PendingBackendStats.pending_xid_count; PendingBackendStats.pending_xact_commit = 0; PendingBackendStats.pending_xact_rollback = 0; + PendingBackendStats.pending_xid_count = 0; backend_has_xactstats = false; } diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 5d73c4dfbcf..780ca4b3ac0 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -525,6 +525,7 @@ typedef struct PgStat_Backend PgStat_WalCounters wal_counters; PgStat_Counter xact_commit; PgStat_Counter xact_rollback; + PgStat_Counter xid_count; } PgStat_Backend; /* --------- @@ -543,6 +544,7 @@ typedef struct PgStat_BackendPending */ PgStat_Counter pending_xact_commit; PgStat_Counter pending_xact_rollback; + PgStat_Counter pending_xid_count; } PgStat_BackendPending; /* -- 2.34.1 --uN5gGi2rtCNSVbpC Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v5-0003-Adding-the-pg_stat_backend_transaction-view.patch" From f2235b15f2d57b7cbc207a42eb719476f9f0cf91 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot Date: Sat, 9 Aug 2025 14:22:36 +0000 Subject: [PATCH v5 3/3] Adding the pg_stat_backend_transaction view This view displays one row per server process, showing transaction 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_transactions()). 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 | 65 +++++++++++++++ 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, 231 insertions(+) 49.8% doc/src/sgml/ 3.1% src/backend/catalog/ 25.8% src/backend/utils/adt/ 6.8% src/include/catalog/ 9.2% 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 bb75ed1069b..86200553293 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 + + + pg_stat_backend_transaction + pg_stat_backend_transaction + + + One row per server process, showing statistics related to + the current activity of that process, such as number of commits and + rollbacks. + See + pg_stat_backend_transaction for details. + + + pg_stat_replicationpg_stat_replication One row per WAL sender process, showing statistics about @@ -1195,6 +1209,91 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + <structname>pg_stat_backend_transaction</structname> + + + pg_stat_backend_transaction + + + + The pg_stat_backend_transaction view will have one row + per server process, showing statistics related to + the current activity of that process. + + + + <structname>pg_stat_backend_transaction</structname> View + + + + + Column Type + + + Description + + + + + + + + pid integer + + + Process ID of this backend + + + + + + xid_count bigint + + + The number of XID that have been generated by the backend. It does not take + into account virtual transaction ID on purpose. + + + + + + xact_commit bigint + + + The number of transactions that have been committed. + + + + + + xact_rollback bigint + + + The number of transactions that have been rolled back. + + + + + + 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> @@ -5342,6 +5441,22 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + + pg_stat_get_backend_transactions + + pg_stat_get_backend_transactions ( integer ) + setof record + + + Returns a record of transaction statistics about 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_transaction view. + + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index e54018004db..e005eaeb579 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -946,6 +946,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_transaction AS + SELECT + S.pid, + S.xid_count, + S.xact_commit, + S.xact_rollback, + S.stats_reset + FROM pg_stat_get_backend_transactions(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 9185a8e6b83..77ee9222ccf 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -708,6 +708,71 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) return (Datum) 0; } +/* + * Returns transactions statistics of PG backends. + */ +Datum +pg_stat_get_backend_transactions(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; + + /* check if the backend type tracks statistics */ + if (!pgstat_tracks_backend_bktype(beentry->st_backendType)) + continue; + + /* + * Don't use pgstat_fetch_stat_backend_by_pid() to avoid holding the + * ProcArrayLock during each iteration. + */ + backend_stats = pgstat_fetch_stat_backend(local_beentry->proc_number); + + 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 3579cec5744..6a686e93fdd 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5680,6 +5680,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_transactions', 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_transactions' }, { 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 2b3cf6d8569..2b4ea519677 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1860,6 +1860,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_transaction| SELECT pid, + xid_count, + xact_commit, + xact_rollback, + stats_reset + FROM pg_stat_get_backend_transactions(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 ea7f7846895..e3f55a4c0ea 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_transaction 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_transaction 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 65d8968c83e..e6b35593a95 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_transaction 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_transaction 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 --uN5gGi2rtCNSVbpC--