MIME-Version: 1.0
References: <19439-8f02018f9fc3a240@postgresql.org>
In-Reply-To: <19439-8f02018f9fc3a240@postgresql.org>
From: Xuneng Zhou <xunengzhou@gmail.com>
Date: Fri, 27 Mar 2026 10:15:27 +0800
Message-ID: <CABPTF7Wx=WTHa67PJUnH8VaJf-svrcFjt1ZgOVrqWzfgwDuMCA@mail.gmail.com>
Subject: Re: BUG #19439: pg_stat_xact_user_tables stat not currect during the transaction
To: klemen.kobau@gmail.com, pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CABPTF7Wx%3DWTHa67PJUnH8VaJf-svrcFjt1ZgOVrqWzfgwDuMCA%40mail.gmail.com>
Precedence: bulk

Hi klemen,

Thanks for the report.

On Fri, Mar 27, 2026 at 5:36=E2=80=AFAM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      19439
> Logged by:          klemen kobau
> Email address:      klemen.kobau@gmail.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=3D100",
>       "-c", "wal_level=3Dlogical",
>       "-c", "max_wal_senders=3D10",
>       "-c", "max_replication_slots=3D20",
>       "-c", "wal_keep_size=3D10",
>       "-c", "max_slot_wal_keep_size=3D1024"
>     ]
>     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 tex=
t);
> INSERT INTO temp.xact_test (val) VALUES ('a');
> SELECT relname, n_tup_ins FROM pg_stat_xact_user_tables WHERE relname =3D
> '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 =3D
> '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 t=
he
> 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 =E2=80=9Cthroughout the
transaction.=E2=80=9D It also seems more appropriate for these table views =
to
reflect stats within their own scopes, rather than cumulative values
spanning txn boundaries.


--
Best,
Xuneng