public inbox for [email protected]
help / color / mirror / Atom feedFrom: Xuneng Zhou <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: BUG #19439: pg_stat_xact_user_tables stat not currect during the transaction
Date: Mon, 30 Mar 2026 11:14:19 +0800
Message-ID: <CABPTF7XO101mBvA9u01nHkvt1gaQ3i60hypYOvNp6DdOFnY0jQ@mail.gmail.com> (raw)
In-Reply-To: <CABPTF7VSNTvHTpS4ga1MfLYO-=xt7mLQ0ONEFobRERnhzXSzxw@mail.gmail.com>
References: <[email protected]>
<CABPTF7Wx=WTHa67PJUnH8VaJf-svrcFjt1ZgOVrqWzfgwDuMCA@mail.gmail.com>
<CABPTF7Wb6VOac1EFopGzhAtiHOU8_XRkFvhC6BnSj=jACBB+Gw@mail.gmail.com>
<CABPTF7VSNTvHTpS4ga1MfLYO-=xt7mLQ0ONEFobRERnhzXSzxw@mail.gmail.com>
On Sun, Mar 29, 2026 at 11:17 AM Xuneng Zhou <[email protected]> wrote:
>
> On Sat, Mar 28, 2026 at 1:47 PM Xuneng Zhou <[email protected]> wrote:
> >
> > On Fri, Mar 27, 2026 at 10:15 AM Xuneng Zhou <[email protected]> wrote:
> > >
> > > Hi klemen,
> > >
> > > Thanks for the report.
> > >
> > > On Fri, Mar 27, 2026 at 5:36 AM PG Bug reporting form
> > > <[email protected]> wrote:
> > > >
> > > > The following bug has been logged on the website:
> > > >
> > > > Bug reference: 19439
> > > > Logged by: klemen kobau
> > > > Email address: [email protected]
> > > > PostgreSQL version: 18.0
> > > > Operating system: Linux (EndeavorOS)
> > > > Description:
> > > >
> > > > I am running postgres:18.0 in a docker container, the configuration is as
> > > > follows:
> > > >
> > > > postgres:
> > > > image: postgres:18.0
> > > > command: [
> > > > "postgres",
> > > > "-N", "200",
> > > > "-c", "max_prepared_transactions=100",
> > > > "-c", "wal_level=logical",
> > > > "-c", "max_wal_senders=10",
> > > > "-c", "max_replication_slots=20",
> > > > "-c", "wal_keep_size=10",
> > > > "-c", "max_slot_wal_keep_size=1024"
> > > > ]
> > > > environment:
> > > > POSTGRES_USER: postgres
> > > > POSTGRES_PASSWORD: postgres
> > > > POSTGRES_DB: postgres
> > > > TZ: UTC
> > > > PGTZ: UTC
> > > > ports:
> > > > - 5432:5432
> > > > volumes:
> > > > - postgres_data:/var/lib/postgresql
> > > >
> > > > I use psql version 18.3.
> > > >
> > > > I run the following:
> > > >
> > > > psql -h localhost -p 5432 -U postgres -d postgres -c "
> > > > -- Transaction 1: insert 1 row, check stats, commit
> > > > BEGIN;
> > > > CREATE TABLE IF NOT EXISTS temp.xact_test (id serial PRIMARY KEY, val text);
> > > > INSERT INTO temp.xact_test (val) VALUES ('a');
> > > > SELECT relname, n_tup_ins FROM pg_stat_xact_user_tables WHERE relname =
> > > > 'xact_test';
> > > > COMMIT;
> > > >
> > > > -- Transaction 2: insert 1 row, check stats
> > > > BEGIN;
> > > > INSERT INTO temp.xact_test (val) VALUES ('b');
> > > > SELECT relname, n_tup_ins FROM pg_stat_xact_user_tables WHERE relname =
> > > > 'xact_test';
> > > > ROLLBACK;
> > > >
> > > > -- Cleanup
> > > > DROP TABLE temp.xact_test;
> > > > "
> > > >
> > > > And the output is
> > > >
> > > > BEGIN
> > > > CREATE TABLE
> > > > INSERT 0 1
> > > > relname | n_tup_ins
> > > > -----------+-----------
> > > > xact_test | 1
> > > > (1 row)
> > > >
> > > > COMMIT
> > > > BEGIN
> > > > INSERT 0 1
> > > > relname | n_tup_ins
> > > > -----------+-----------
> > > > xact_test | 2
> > > > (1 row)
> > > >
> > > > ROLLBACK
> > > > DROP TABLE
> > >
> > > I can also reproduce this behavior on HEAD.
> > >
> > > > This is not what I would expect from reading
> > > > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-VIEWS,
> > > > where it states
> > > >
> > > > > A transaction can also see its own statistics (not yet flushed out to the
> > > > shared memory statistics) in the views pg_stat_xact_all_tables,
> > > > pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and
> > > > pg_stat_xact_user_functions. These numbers do not act as stated above;
> > > > instead they update continuously throughout the transaction.
> > > >
> > > > based on this, I would expect that the numbers are updated each time an
> > > > insert happens and that they are isolated per transaction.
> > > >
> > >
> > > This snippet of the doc feels somewhat ambiguous. The current behavior
> > > does not seem fully consistent with wording such as “throughout the
> > > transaction.” It also seems more appropriate for these table views to
> > > reflect stats within their own scopes, rather than cumulative values
> > > spanning txn boundaries.
> > >
> >
> > I’ve looked into this issue and 'd like to propose a patch to address
> > it. Feedback is very welcome.
> >
> > --- Root cause
> >
> > Since the stats subsystem was rewritten (commit 5891c7a8), each
> > backend keeps a hash of pending stats entries that persist across
> > transaction boundaries. Entries that flush successfully are deleted
> > from the backend-local pending list; entries not flushed remain
> > pending with their accumulated counters. Flushing is driven by
> > pgstat_report_stat(), called from the ReadyForQuery path, and subject
> > to a rate limiter.
> >
> > The pg_stat_xact_* views read these pending entries directly via
> > find_tabstat_entry() / find_funcstat_entry(). Both functions return
> > the raw accumulated counters without per-transaction scoping.
> >
> > The bug is deterministic when multiple top-level transactions are
> > processed within a single simple-query message, because there is no
> > ReadyForQuery boundary between the transactions and therefore no
> > opportunity to flush and remove the pending entry.
> >
> > --- Fix
> >
> > The patch introduces a per-entry "transaction baseline" — a lazy
> > snapshot of the counters taken the first time an entry is touched in
> > each new top-level transaction. The accessor functions
> > (find_tabstat_entry, find_funcstat_entry) then subtract the baseline
> > from the current counters, yielding only the current transaction's
> > delta.
> >
> > The baseline is keyed by MyProc->vxid.lxid. A static inline helper,
> > pgstat_ensure_xact_baseline(), is called at every nontransactional
> > counter-increment site (the 7 event-counter macros in pgstat.h and the
> > 4 non-inline counter functions in pgstat_relation.c). After the first
> > call per entry per transaction, the check reduces to a single integer
> > comparison with a predictably not-taken branch.
> >
> > For function stats, a new PgStat_FunctionPending wrapper struct embeds
> > PgStat_FunctionCounts at offset 0 with the baseline fields appended,
> > so the flush callback requires only a trivial cast change.
> >
> > --- Testing
> >
> > The regression test is a TAP test under src/bin/psql/t/ that uses psql
> > -c to send multi-statement strings as single simple-query messages.
> > src/bin/psql/t look like the right existing harness for the primary
> > regression because psql -c sends the whole multi-statement string as
> > one simple-query message via simple_action_list dispatch. By contrast,
> > ordinary pg_regress SQL files are executed by psql in file/stdin mode,
> > which runs through MainLoop() and dispatches top-level statements one
> > at a time via SendQuery(), creating ReadyForQuery boundaries that
> > could mask the bug.
> >
> > The test covers three scenarios: table n_tup_ins and seq_scan
> > counters, and function calls — each verifying isolation across
> > consecutive top-level transactions within a single message.
> >
>
> postgres % cat output.txt
> BEGIN
> CREATE TABLE
> INSERT 0 1
> relname | n_tup_ins
> -----------+-----------
> xact_test | 1
> (1 row)
>
> COMMIT
> BEGIN
> INSERT 0 1
> relname | n_tup_ins
> -----------+-----------
> xact_test | 2
> (1 row)
>
> ROLLBACK
> DROP TABLE
>
> I can also reproduce this behavior in v13. So this issue pre-existed
> the major refactor 5891c7a8.
>
After further thought of v1, I'd like to also propose an alternative
approach. The root cause and testing rationale are the same as
described upthread; what changes is how the baseline is established.
--- Problems with the lazy (v1) approach
The v1 patch snapshots the baseline lazily -- on every
nontransactional counter increment, it checks whether baseline_lxid
matches the current
transaction and snapshots if not. This has two drawbacks:
1) Hot-path overhead
Every pgstat_count_heap_scan(), pgstat_count_buffer_read_block(), etc.
acquires a branch. After the first call per entry per transaction, the
branch is predictably not-taken, but it could still present on some of
the most frequently executed paths in the backend.
2) Maintenance burden
Every new counter added to PgStat_TableCounts requires a corresponding
baseline snapshot at its increment site. Miss one, and that counter
silently leaks across transactions
--- eager baseline sweep
The attached patch records the baseline eagerly at transaction
boundaries instead of lazily at counter-increment sites.
pgstat_set_pending_baselines() iterates the pgStatPending list and
snapshots each entry's current counts into an xact_baseline field via
struct assignment. It is called from AtEOXact_PgStat() (after folding
transactional counts and removing dropped entries) and from
PostPrepare_PgStat() (after relation cleanup), covering commit, abort,
and PREPARE TRANSACTION. The view accessors unconditionally subtract
the baseline. For entries created in the current transaction,
xact_baseline is zero-initialized, so the subtraction is a no-op.
I don’t have a clear preference between the two approaches; both are
presented for review.
--
Best,
Xuneng
Attachments:
[application/octet-stream] v1-0001-Fix-pg_stat_xact_-views-leaking-stats-across-tran.patch (18.2K, 2-v1-0001-Fix-pg_stat_xact_-views-leaking-stats-across-tran.patch)
download | inline diff:
From 9f0eec731a75f52bd8f3e423c1ae22dea69b0a5a Mon Sep 17 00:00:00 2001
From: alterego655 <[email protected]>
Date: Mon, 30 Mar 2026 10:47:53 +0800
Subject: [PATCH v1] Fix pg_stat_xact_* views leaking stats across transaction
boundaries
Currently, pgStatPending entries persist across transaction boundaries
within a backend. The pg_stat_xact_* view accessors (find_tabstat_entry,
find_funcstat_entry) return raw accumulated counters from these entries,
so they incorrectly report activity from prior transactions rather than
only the current one.
Fix this by recording a baseline snapshot of each pending entry's
counters at every top-level transaction boundary. A new function,
pgstat_set_pending_baselines(), sweeps the pgStatPending list at
commit, abort, and PREPARE TRANSACTION, copying each entry's current
counts into an xact_baseline field. The view accessors then subtract
the baseline, yielding only the current transaction's delta.
For entries first created in the current transaction, xact_baseline is
zero-initialized, so the subtraction is a no-op.
For function stats, introduce a PgStat_FunctionPending wrapper struct
that pairs the existing PgStat_FunctionCounts with a baseline copy.
find_funcstat_entry() now returns a palloc'd delta, or NULL when the
function was not called in the current transaction.
Add a TAP test under src/bin/psql/t/ that verifies counter isolation
across consecutive transactions within a single simple-query message.
---
src/backend/utils/activity/pgstat.c | 43 +++-
src/backend/utils/activity/pgstat_function.c | 42 +++-
src/backend/utils/activity/pgstat_relation.c | 21 ++
src/backend/utils/activity/pgstat_xact.c | 15 ++
src/bin/psql/meson.build | 1 +
src/bin/psql/t/040_pgstat_xact.pl | 203 +++++++++++++++++++
src/include/pgstat.h | 16 ++
src/include/utils/pgstat_internal.h | 1 +
8 files changed, 332 insertions(+), 10 deletions(-)
create mode 100644 src/bin/psql/t/040_pgstat_xact.pl
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index eb8ccbaa628..0d14e753bea 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -326,7 +326,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.shared_size = sizeof(PgStatShared_Function),
.shared_data_off = offsetof(PgStatShared_Function, stats),
.shared_data_len = sizeof(((PgStatShared_Function *) 0)->stats),
- .pending_size = sizeof(PgStat_FunctionCounts),
+ .pending_size = sizeof(PgStat_FunctionPending),
.flush_pending_cb = pgstat_function_flush_cb,
.reset_timestamp_cb = pgstat_function_reset_timestamp_cb,
@@ -1361,6 +1361,47 @@ pgstat_delete_pending_entry(PgStat_EntryRef *entry_ref)
dlist_delete(&entry_ref->pending_node);
}
+/*
+ * Set xact baselines for all pending relation and function entries.
+ *
+ * Called at top-level transaction commit, abort, and successful PREPARE
+ * to record the current counter state as the baseline. The next
+ * transaction on this backend will compute xact-scoped deltas by
+ * subtracting this baseline.
+ *
+ * This sweeps all unflushed pending entries, not just those touched in
+ * the current transaction, because nontransactional counters (scans,
+ * buffer hits, etc.) accumulate in the pending entry regardless of
+ * whether a PgStat_TableXactStatus was created.
+ */
+void
+pgstat_set_pending_baselines(void)
+{
+ dlist_iter iter;
+
+ dlist_foreach(iter, &pgStatPending)
+ {
+ PgStat_EntryRef *entry_ref =
+ dlist_container(PgStat_EntryRef, pending_node, iter.cur);
+ PgStat_Kind kind = entry_ref->shared_entry->key.kind;
+
+ if (kind == PGSTAT_KIND_RELATION)
+ {
+ PgStat_TableStatus *tabstat =
+ (PgStat_TableStatus *) entry_ref->pending;
+
+ tabstat->xact_baseline = tabstat->counts;
+ }
+ else if (kind == PGSTAT_KIND_FUNCTION)
+ {
+ PgStat_FunctionPending *fpending =
+ (PgStat_FunctionPending *) entry_ref->pending;
+
+ fpending->xact_baseline = fpending->counts;
+ }
+ }
+}
+
/*
* Flush out pending variable-numbered stats.
*/
diff --git a/src/backend/utils/activity/pgstat_function.c b/src/backend/utils/activity/pgstat_function.c
index e6b84283c6c..704cd1d8240 100644
--- a/src/backend/utils/activity/pgstat_function.c
+++ b/src/backend/utils/activity/pgstat_function.c
@@ -119,7 +119,7 @@ pgstat_init_function_usage(FunctionCallInfo fcinfo,
}
}
- pending = entry_ref->pending;
+ pending = &((PgStat_FunctionPending *) entry_ref->pending)->counts;
fcu->fs = pending;
@@ -192,10 +192,12 @@ pgstat_end_function_usage(PgStat_FunctionCallUsage *fcu, bool finalize)
bool
pgstat_function_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
{
+ PgStat_FunctionPending *fpending;
PgStat_FunctionCounts *localent;
PgStatShared_Function *shfuncent;
- localent = (PgStat_FunctionCounts *) entry_ref->pending;
+ fpending = (PgStat_FunctionPending *) entry_ref->pending;
+ localent = &fpending->counts;
shfuncent = (PgStatShared_Function *) entry_ref->shared_stats;
/* localent always has non-zero content */
@@ -223,18 +225,40 @@ pgstat_function_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
/*
* find any existing PgStat_FunctionCounts entry for specified function
*
- * If no entry, return NULL, don't create a new one
+ * Returns a palloc'd delta struct showing only the current transaction's
+ * contribution, or NULL if the function was not called in this transaction.
*/
PgStat_FunctionCounts *
find_funcstat_entry(Oid func_id)
{
PgStat_EntryRef *entry_ref;
-
- entry_ref = pgstat_fetch_pending_entry(PGSTAT_KIND_FUNCTION, MyDatabaseId, func_id);
-
- if (entry_ref)
- return entry_ref->pending;
- return NULL;
+ PgStat_FunctionPending *fpending;
+ PgStat_FunctionCounts *result;
+ PgStat_Counter delta_calls;
+
+ entry_ref = pgstat_fetch_pending_entry(PGSTAT_KIND_FUNCTION,
+ MyDatabaseId, func_id);
+ if (!entry_ref)
+ return NULL;
+
+ fpending = (PgStat_FunctionPending *) entry_ref->pending;
+
+ /* Compute delta; return NULL if not called in this transaction */
+ delta_calls = fpending->counts.numcalls - fpending->xact_baseline.numcalls;
+ if (delta_calls == 0)
+ return NULL;
+
+ result = palloc(sizeof(PgStat_FunctionCounts));
+ result->numcalls = delta_calls;
+ INSTR_TIME_SET_ZERO(result->total_time);
+ INSTR_TIME_ACCUM_DIFF(result->total_time,
+ fpending->counts.total_time,
+ fpending->xact_baseline.total_time);
+ INSTR_TIME_SET_ZERO(result->self_time);
+ INSTR_TIME_ACCUM_DIFF(result->self_time,
+ fpending->counts.self_time,
+ fpending->xact_baseline.self_time);
+ return result;
}
/*
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index bc8c43b96aa..4411870ba5e 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -503,6 +503,7 @@ find_tabstat_entry(Oid rel_id)
PgStat_TableXactStatus *trans;
PgStat_TableStatus *tabentry = NULL;
PgStat_TableStatus *tablestatus = NULL;
+ PgStat_TableCounts *tabentry_baseline;
entry_ref = pgstat_fetch_pending_entry(PGSTAT_KIND_RELATION, MyDatabaseId, rel_id);
if (!entry_ref)
@@ -523,6 +524,26 @@ find_tabstat_entry(Oid rel_id)
*/
tablestatus->trans = NULL;
+ /*
+ * Subtract the xact baseline to show only the current top-level
+ * transaction's activity. The baseline is set at the end of each
+ * top-level transaction (commit/abort/prepare) by
+ * pgstat_set_pending_baselines(). For entries created in the current
+ * transaction, the baseline is zero-initialized, so subtracting it is a
+ * no-op.
+ */
+ tabentry_baseline = &tabentry->xact_baseline;
+ tablestatus->counts.numscans -= tabentry_baseline->numscans;
+ tablestatus->counts.tuples_returned -= tabentry_baseline->tuples_returned;
+ tablestatus->counts.tuples_fetched -= tabentry_baseline->tuples_fetched;
+ tablestatus->counts.tuples_inserted -= tabentry_baseline->tuples_inserted;
+ tablestatus->counts.tuples_updated -= tabentry_baseline->tuples_updated;
+ tablestatus->counts.tuples_deleted -= tabentry_baseline->tuples_deleted;
+ tablestatus->counts.tuples_hot_updated -= tabentry_baseline->tuples_hot_updated;
+ tablestatus->counts.tuples_newpage_updated -= tabentry_baseline->tuples_newpage_updated;
+ tablestatus->counts.blocks_fetched -= tabentry_baseline->blocks_fetched;
+ tablestatus->counts.blocks_hit -= tabentry_baseline->blocks_hit;
+
/*
* Live subtransaction counts are not included yet. This is not a hot
* code path so reconcile tuples_inserted, tuples_updated and
diff --git a/src/backend/utils/activity/pgstat_xact.c b/src/backend/utils/activity/pgstat_xact.c
index 5e2d69e6297..6a708beb05b 100644
--- a/src/backend/utils/activity/pgstat_xact.c
+++ b/src/backend/utils/activity/pgstat_xact.c
@@ -55,6 +55,14 @@ AtEOXact_PgStat(bool isCommit, bool parallel)
}
pgStatXactStack = NULL;
+ /*
+ * Record current counter values as the baseline for the next transaction.
+ * Must be after AtEOXact_PgStat_Relations() (which folds transactional
+ * counts into ->counts) and AtEOXact_PgStat_DroppedStats() (which removes
+ * dropped entries from pgStatPending).
+ */
+ pgstat_set_pending_baselines();
+
/* Make sure any stats snapshot is thrown away */
pgstat_clear_snapshot();
}
@@ -226,6 +234,13 @@ PostPrepare_PgStat(void)
}
pgStatXactStack = NULL;
+ /*
+ * Record baselines, same as at commit/abort. AtEOXact_PgStat() is not
+ * called during PREPARE, so without this, nontransactional counters from
+ * the prepared transaction would leak into later transactions.
+ */
+ pgstat_set_pending_baselines();
+
/* Make sure any stats snapshot is thrown away */
pgstat_clear_snapshot();
}
diff --git a/src/bin/psql/meson.build b/src/bin/psql/meson.build
index 922b2845267..25fbb817f1d 100644
--- a/src/bin/psql/meson.build
+++ b/src/bin/psql/meson.build
@@ -78,6 +78,7 @@ tests += {
't/010_tab_completion.pl',
't/020_cancel.pl',
't/030_pager.pl',
+ 't/040_pgstat_xact.pl',
],
},
}
diff --git a/src/bin/psql/t/040_pgstat_xact.pl b/src/bin/psql/t/040_pgstat_xact.pl
new file mode 100644
index 00000000000..cfb5cec3927
--- /dev/null
+++ b/src/bin/psql/t/040_pgstat_xact.pl
@@ -0,0 +1,203 @@
+# Copyright (c) 2021-2026, PostgreSQL Global Development Group
+
+# Test that pg_stat_xact_* views report only current-transaction activity,
+# not accumulated pending stats from prior transactions.
+#
+# Uses "psql -c" so all statements go as a single simple-query message,
+# ensuring no ReadyForQuery boundary (and no pgstat_report_stat() flush)
+# between consecutive top-level transactions.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init;
+$node->append_conf('postgresql.conf', "track_functions = 'all'");
+$node->append_conf('postgresql.conf', "max_prepared_transactions = 5");
+$node->start;
+
+my $dbname = 'postgres';
+
+# Create test objects.
+$node->safe_psql(
+ $dbname, q{
+ CREATE TABLE test_xact_stats (x int);
+ CREATE FUNCTION test_xact_stats_func() RETURNS void
+ LANGUAGE plpgsql AS $$ BEGIN NULL; END; $$;
+});
+
+# Send SQL via "psql -c" so all statements are one simple-query message.
+sub psql_c_like
+{
+ local $Test::Builder::Level = $Test::Builder::Level + 1;
+
+ my ($node, $sql, $expected_stdout, $test_name) = @_;
+
+ my ($stdout, $stderr);
+ my @cmd = (
+ $node->installed_command('psql'),
+ '--no-psqlrc', '--no-align', '--tuples-only', '--quiet',
+ '-d', $node->connstr($dbname),
+ '-c', $sql);
+ my $result = IPC::Run::run \@cmd, '>', \$stdout, '2>', \$stderr;
+
+ is($result, 1, "$test_name: exit code 0");
+ is($stderr, '', "$test_name: no stderr");
+ like($stdout, $expected_stdout, "$test_name: matches");
+
+ return;
+}
+
+##
+## Test 1: Table n_tup_ins must not leak across top-level transactions.
+##
+psql_c_like(
+ $node,
+ q{BEGIN;
+ INSERT INTO test_xact_stats VALUES (1);
+ COMMIT;
+ BEGIN;
+ INSERT INTO test_xact_stats VALUES (2);
+ SELECT 'n_tup_ins:' || n_tup_ins FROM pg_stat_xact_user_tables
+ WHERE relname = 'test_xact_stats';
+ COMMIT;},
+ qr/^n_tup_ins:1$/m,
+ "table n_tup_ins shows only current transaction's inserts");
+
+##
+## Test 2: seq_scan counter must not leak across top-level transactions.
+## Uses labeled output to avoid false-positive matches from count(*) rows.
+##
+psql_c_like(
+ $node,
+ q{BEGIN;
+ SELECT count(*) FROM test_xact_stats;
+ COMMIT;
+ BEGIN;
+ SELECT count(*) FROM test_xact_stats;
+ SELECT 'seq_scan:' || seq_scan FROM pg_stat_xact_user_tables
+ WHERE relname = 'test_xact_stats';
+ COMMIT;},
+ qr/^seq_scan:1$/m,
+ "table seq_scan shows only current transaction's scans");
+
+##
+## Test 3: Function call stats must not leak across top-level transactions.
+##
+psql_c_like(
+ $node,
+ q{BEGIN;
+ SELECT test_xact_stats_func();
+ SELECT test_xact_stats_func();
+ COMMIT;
+ BEGIN;
+ SELECT test_xact_stats_func();
+ SELECT 'calls:' || calls FROM pg_stat_xact_user_functions
+ WHERE funcname = 'test_xact_stats_func';
+ COMMIT;},
+ qr/^calls:1$/m,
+ "function calls shows only current transaction's calls");
+
+##
+## Test 4: seq_scan must not leak across a PREPARE TRANSACTION boundary.
+##
+## COMMIT PREPARED cannot appear in a multi-statement psql -c string.
+## In a multi-statement simple-query message, exec_simple_query() calls
+## BeginImplicitTransactionBlock() before each statement. After PREPARE
+## TRANSACTION ends the explicit block, the loop re-enters the implicit-
+## block path, putting the backend into TBLOCK_IMPLICIT_INPROGRESS.
+## PreventInTransactionBlock() rejects COMMIT PREPARED in that state.
+##
+## A separate psql() call works because it sends a single-statement
+## simple-query message: use_implicit_block is false, so the backend
+## stays in TBLOCK_STARTED and PreventInTransactionBlock() passes.
+##
+psql_c_like(
+ $node,
+ q{BEGIN;
+ SELECT count(*) FROM test_xact_stats;
+ PREPARE TRANSACTION 'test_prep';
+ BEGIN;
+ SELECT count(*) FROM test_xact_stats;
+ SELECT 'seq_scan:' || seq_scan FROM pg_stat_xact_user_tables
+ WHERE relname = 'test_xact_stats';
+ COMMIT;},
+ qr/^seq_scan:1$/m,
+ "table seq_scan doesn't leak across PREPARE boundary");
+
+# Best-effort cleanup: use psql() instead of safe_psql() so that if the
+# preceding test failed before PREPARE TRANSACTION, the missing prepared
+# xact does not abort the rest of the test file.
+$node->psql($dbname, q{COMMIT PREPARED 'test_prep';});
+
+##
+## Test 5: Untouched relation entry must show zero, not prior-txn counts.
+##
+## If a pending entry exists from a prior transaction but the current
+## transaction has not touched the relation, counts - baseline = 0.
+##
+psql_c_like(
+ $node,
+ q{BEGIN;
+ SELECT count(*) FROM test_xact_stats;
+ INSERT INTO test_xact_stats VALUES (99);
+ COMMIT;
+ BEGIN;
+ SELECT 'seq_scan:' || seq_scan || ',n_tup_ins:' || n_tup_ins
+ FROM pg_stat_xact_user_tables
+ WHERE relname = 'test_xact_stats';
+ COMMIT;},
+ qr/^seq_scan:0,n_tup_ins:0$/m,
+ "untouched relation entry shows zeroes in current transaction");
+
+##
+## Test 6: Untouched function entry must not appear in xact view.
+##
+## pg_stat_xact_user_functions filters with IS NOT NULL on the accessor.
+## When delta_calls == 0, find_funcstat_entry() returns NULL, so the
+## function must not appear.
+##
+psql_c_like(
+ $node,
+ q{BEGIN;
+ SELECT test_xact_stats_func();
+ COMMIT;
+ BEGIN;
+ SELECT 'found:' || count(*) FROM pg_stat_xact_user_functions
+ WHERE funcname = 'test_xact_stats_func';
+ COMMIT;},
+ qr/^found:0$/m,
+ "uncalled function not visible in pg_stat_xact_user_functions");
+
+##
+## Test 7: Function call stats must not leak across PREPARE boundary.
+##
+psql_c_like(
+ $node,
+ q{BEGIN;
+ SELECT test_xact_stats_func();
+ SELECT test_xact_stats_func();
+ PREPARE TRANSACTION 'test_func_prep';
+ BEGIN;
+ SELECT test_xact_stats_func();
+ SELECT 'calls:' || calls FROM pg_stat_xact_user_functions
+ WHERE funcname = 'test_xact_stats_func';
+ COMMIT;},
+ qr/^calls:1$/m,
+ "function calls don't leak across PREPARE boundary");
+
+# Best-effort cleanup (see comment above).
+$node->psql($dbname, q{COMMIT PREPARED 'test_func_prep';});
+
+# Cleanup
+$node->safe_psql(
+ $dbname, q{
+ DROP TABLE test_xact_stats;
+ DROP FUNCTION test_xact_stats_func();
+});
+
+$node->stop;
+done_testing();
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 8e3549c3752..660b64e870a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -90,6 +90,21 @@ typedef struct PgStat_FunctionCounts
instr_time self_time;
} PgStat_FunctionCounts;
+/* ----------
+ * PgStat_FunctionPending Backend-local pending state for function stats
+ *
+ * Wraps the accumulated counts with a per-transaction baseline so that
+ * pg_stat_xact_user_functions can report only the current transaction's
+ * contribution. The baseline is set at each top-level transaction boundary
+ * by pgstat_set_pending_baselines().
+ * ----------
+ */
+typedef struct PgStat_FunctionPending
+{
+ PgStat_FunctionCounts counts; /* accumulated counts */
+ PgStat_FunctionCounts xact_baseline; /* snapshot at txn boundary */
+} PgStat_FunctionPending;
+
/*
* Working state needed to accumulate per-function-call timing statistics.
*/
@@ -183,6 +198,7 @@ typedef struct PgStat_TableStatus
bool shared; /* is it a shared catalog? */
struct PgStat_TableXactStatus *trans; /* lowest subxact's counts */
PgStat_TableCounts counts; /* event counts to be sent */
+ PgStat_TableCounts xact_baseline; /* snapshot at txn boundary */
Relation relation; /* rel that is using this entry */
} PgStat_TableStatus;
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 97704421a92..292d9757c02 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -687,6 +687,7 @@ extern PgStat_EntryRef *pgstat_prep_pending_entry(PgStat_Kind kind, Oid dboid,
bool *created_entry);
extern PgStat_EntryRef *pgstat_fetch_pending_entry(PgStat_Kind kind,
Oid dboid, uint64 objid);
+extern void pgstat_set_pending_baselines(void);
extern void *pgstat_fetch_entry(PgStat_Kind kind, Oid dboid, uint64 objid);
extern void pgstat_snapshot_fixed(PgStat_Kind kind);
--
2.51.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19439: pg_stat_xact_user_tables stat not currect during the transaction
In-Reply-To: <CABPTF7XO101mBvA9u01nHkvt1gaQ3i60hypYOvNp6DdOFnY0jQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox