BUG #19439: pg_stat_xact_user_tables stat not currect during the transaction

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: klemen(dot)kobau(at)gmail(dot)com
Subject: BUG #19439: pg_stat_xact_user_tables stat not currect during the transaction
Date: 2026-03-25 14:06:37
Message-ID: 19439-8f02018f9fc3a240@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19439
Logged by: klemen kobau
Email address: klemen(dot)kobau(at)gmail(dot)com
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

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.

Kind regards

Klemen Kobau

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Dmitriy Kuzmin 2026-03-25 14:30:38 Re: BUG #19437: temp_tablespaces doesn't work inside a cursor?
Previous Message David G. Johnston 2026-03-25 13:52:14 BUG #19437: temp_tablespaces doesn't work inside a cursor?