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.94.2) (envelope-from ) id 1ujvod-00Djqh-Cx for pgsql-hackers@arkaria.postgresql.org; Thu, 07 Aug 2025 08:17:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ujvob-0038c0-K7 for pgsql-hackers@arkaria.postgresql.org; Thu, 07 Aug 2025 08:17:33 +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.94.2) (envelope-from ) id 1ujvob-0038bs-5L for pgsql-hackers@lists.postgresql.org; Thu, 07 Aug 2025 08:17:33 +0000 Received: from mail-wr1-x433.google.com ([2a00:1450:4864:20::433]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ujvoY-001Dtk-0Y for pgsql-hackers@lists.postgresql.org; Thu, 07 Aug 2025 08:17:32 +0000 Received: by mail-wr1-x433.google.com with SMTP id ffacd0b85a97d-3b7892609a5so373079f8f.1 for ; Thu, 07 Aug 2025 01:17:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1754554649; x=1755159449; darn=lists.postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=/Zdi6Iv/B+NASTsCEOk3um1y+IWFw/ysmqcURHkl6B4=; b=aeu0Q+HuKiM43tWONKtaTg3O46GvkkG5Aa8Iop2HEWtGQ76dKj9UN/PCtpaCVk4BjM rIcc8VW8PUEciNJc8J5r3k9lkEP0zZ9s1y7HoT6uvRFLYocJmoh61QQW5lFyfR7z8BDi TJuafFdkDGVVEi4OAKP+RNua3DeGXztQ7pJFlR/QQPOivYbpgKqQwctlNXTqMhPVXu2+ 5Kg8JSFTmOk6i3e52ArjTWM9+P+2QDfB5iU4nMDSN0ziQT+Eojtd/b36hhoG2/2wef6S 9d3aWwhrvaB9O7gJaX5+PNAKlus786kRZajcM9CILrkeY349HP9kHa6zVJkirFhc08yF o3vQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1754554649; x=1755159449; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=/Zdi6Iv/B+NASTsCEOk3um1y+IWFw/ysmqcURHkl6B4=; b=U3W41j0aCCOh7ooIPasExegy6lWFNcrvsR5VccMOW4ybGsgg1HnWy6srPOBGOnRkc8 fnNk3k3bpmJnaKQp+4xXuzDTPJUtyatAFNOdyVMVeCJ1OssOHVRXnO+DtYKio12nwK9X ffa2wFEdMI6I0X7JboNKmcd3utCA4mL0knbPmwk0NzftoFRY7LSefZYud0zrt6MVo8sD cDiW2MumtbI4qr2y+03yGpoSJDhwLtRiRgIkhnu6b6Fal72rELgf/AoxKA5f3XyYvVXy TRQTw9poZWgdu44oTAOE6WQxevr35UEKhugodVaAMbUi4dhnBSiDq25ooHKDwQ5/NXO0 x34w== X-Gm-Message-State: AOJu0YwreE+5N+L3ESdiJWOKQAvADB0epoQ8SklzJMRiwMIALXcMC2mw A4QvjeLHAmEaJbADGT6CVdkN4voMFmbIapWXdO9KkvvdgsFPPzsiHjwtdtv++w== X-Gm-Gg: ASbGncv4fOE0QZ2NxMcFJoEkFJMkL/gsDiwKLCMJkP4uhrUdrqLDboeoZ158KKF7VSK AqEI4kSMIgjV25TTrf2uy5VGg9x1i6Qn14NtodT8t9AvmbFaswS5dzHWhHOR30FZhyAihVp8V+L x/ZhqcgO29M7boGjdZoU+O+WZLEh3zqDMoXta9hG6Powr+hxhNH/5epjb/MsqBU9ZePjHp9GiO7 hZ42/FtNNindT90KEnqYTbxv/j14ITCpfoBW0y3Xt3Ckf0Y1S5+5ErmYDiwWANxaZQICns9mABm uxZ6WaAbN33YAnyyL96rwrfedpesfZvCNYkwdq7IFIoLjDYFCm16NXrJDZIZRG98iTIRRE9WoLE hDz1aljFKRueY30xUJtBKWBX3LEWtI0+TkdNnHeHfkKv2K53haoNT41Hs7n6o8s85O9UNadMFfn GDYj0jwmj1xck6umUQL9GeuLI5J4Py/2oCeWH779OsLqSemPDlemEGFg== X-Google-Smtp-Source: AGHT+IF7q1N3lLuVGp0Tm7N6F+ksZMqF3fQGn68xzunL530vtstnLa/nsRmcN9wqo8eFRiIn8e+UtQ== X-Received: by 2002:a05:6000:188b:b0:3b7:8a28:3e35 with SMTP id ffacd0b85a97d-3b8f9f20d1fmr1458920f8f.2.1754554648850; Thu, 07 Aug 2025 01:17:28 -0700 (PDT) Received: from ip-10-97-1-34.eu-west-3.compute.internal (ec2-15-237-181-182.eu-west-3.compute.amazonaws.com. [15.237.181.182]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-3b79c3b9386sm25848846f8f.18.2025.08.07.01.17.28 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 07 Aug 2025 01:17:28 -0700 (PDT) Date: Thu, 7 Aug 2025 08:17:26 +0000 From: Bertrand Drouvot To: PostgreSQL Hackers Subject: Re: Adding per backend commit and rollback counters Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="SywurcztURg2uk+W" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --SywurcztURg2uk+W Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com --SywurcztURg2uk+W Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-Adding-per-backend-commit-and-rollback-counters.patch" From 711863b09f54db89f74ebfbe822d25322668e7e4 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot Date: Mon, 4 Aug 2025 08:14:02 +0000 Subject: [PATCH v2] Adding per backend commit and rollback counters This commit adds 2 functions: pg_stat_get_backend_xact_commit() and pg_stat_get_backend_xact_rollback() to report the number of transactions that have been committed/rolled back for a given backend PID. It relies on the existing per backend statistics that has been added in 9aea73fc61d. --- doc/src/sgml/monitoring.sgml | 36 +++++++++++++ src/backend/utils/activity/pgstat_backend.c | 60 +++++++++++++++++++++ src/backend/utils/activity/pgstat_xact.c | 1 + src/backend/utils/adt/pgstatfuncs.c | 22 ++++++++ src/include/catalog/pg_proc.dat | 9 ++++ src/include/pgstat.h | 8 +++ src/include/utils/pgstat_internal.h | 4 +- src/test/regress/expected/stats.out | 17 ++++++ src/test/regress/sql/stats.sql | 10 ++++ 9 files changed, 166 insertions(+), 1 deletion(-) 26.1% doc/src/sgml/ 29.1% src/backend/utils/activity/ 11.9% src/backend/utils/adt/ 9.4% src/include/catalog/ 3.9% src/include/utils/ 3.3% src/include/ 8.9% src/test/regress/expected/ 7.1% src/test/regress/sql/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 3f4a27a736e..77b41e02b82 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4921,6 +4921,42 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + + pg_stat_get_backend_xact_commit + + pg_stat_get_backend_xact_commit ( integer ) + bigint + + + Returns the number of transactions that have been committed by the backend + with the specified process ID. + + + The function does not return statistics for the checkpointer, + the background writer, the startup process and the autovacuum launcher. + + + + + + + pg_stat_get_backend_xact_rollback + + pg_stat_get_backend_xact_rollback ( integer ) + bigint + + + Returns the number of transactions that have been rolled back by the backend + with the specified process ID. + + + The function does not return statistics for the checkpointer, + the background writer, the startup process and the autovacuum launcher. + + + diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index 8714a85e2d9..12ae9a8d321 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 xact 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 + * XACT 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 XACT 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,23 @@ 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. (We use counters, not just + * bools, in case the reporting message isn't sent right away.) + */ + 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/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index c756c2bebaa..6436129f516 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1606,6 +1606,28 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS) return (Datum) 0; } +#define PG_STAT_GET_BACKENDENTRY_INT64(stat) \ +Datum \ +CppConcat(pg_stat_get_backend_,stat)(PG_FUNCTION_ARGS) \ +{ \ + int pid; \ + PgStat_Backend *backend_stats; \ + \ + pid = PG_GETARG_INT32(0); \ + backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL);\ + \ + if (!backend_stats) \ + PG_RETURN_NULL(); \ + else \ + PG_RETURN_INT64(backend_stats->stat); \ +} + +/* pg_stat_get_backend_xact_commit */ +PG_STAT_GET_BACKENDENTRY_INT64(xact_commit) + +/* pg_stat_get_backend_xact_rollback */ +PG_STAT_GET_BACKENDENTRY_INT64(xact_rollback) + /* * pg_stat_wal_build_tuple * diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 118d6da1ace..f5085d4e016 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6010,6 +6010,15 @@ proargnames => '{backend_pid,backend_type,object,context,reads,read_bytes,read_time,writes,write_bytes,write_time,writebacks,writeback_time,extends,extend_bytes,extend_time,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}', prosrc => 'pg_stat_get_backend_io' }, +{ oid => '8170', descr => 'statistics: backend transactions committed', + proname => 'pg_stat_get_backend_xact_commit', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'int4', + prosrc => 'pg_stat_get_backend_xact_commit' }, +{ oid => '8916', descr => 'statistics: backend transactions rolled back', + proname => 'pg_stat_get_backend_xact_rollback', provolatile => 's', + proparallel => 'r', prorettype => 'int8', proargtypes => 'int4', + prosrc => 'pg_stat_get_backend_xact_rollback' }, + { oid => '1136', descr => 'statistics: information about WAL activity', proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => '', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 202bd2d5ace..d3491faaff2 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; + + /* + * Xact 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 diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 605f5070376..0d316f94e40 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 pg_stat_get_backend_xact_commit AS xact_commit_before + FROM pg_stat_get_backend_xact_commit(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 pg_stat_get_backend_xact_commit AS xact_commit_after + FROM pg_stat_get_backend_xact_commit(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..d629140d880 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 pg_stat_get_backend_xact_commit AS xact_commit_before + FROM pg_stat_get_backend_xact_commit(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 pg_stat_get_backend_xact_commit AS xact_commit_after + FROM pg_stat_get_backend_xact_commit(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 --SywurcztURg2uk+W--