Re: pgstat: Flush some statistics within running transactions, take 2

> Hi,
>
> On Wed, Nov 04, 2026 at 07:08:54PM -0600, Sami Imseih wrote:
> > PFA a mandatory rebase
>
> Thanks for the updated patch!
>
> + xact_boundary = !IsTransactionOrTransactionBlock();
>
> I looks like that procedure commits are missed.
>
> For example, in session 1:
>
> CREATE TABLE flush_test (id integer);
>
> CREATE PROCEDURE flush_probe()
> LANGUAGE plpgsql AS $$
> BEGIN
> INSERT INTO flush_test VALUES (1);
> PERFORM pg_stat_force_next_flush();
> COMMIT;
>
> INSERT INTO flush_test VALUES (2);
> PERFORM pg_stat_force_next_flush();
> PERFORM pg_sleep(20);
> END
> $$;
>
> CALL flush_probe();
>
> While it sleeps, session 2 reports one row:
>
> SELECT count(*) FROM flush_test;
> count
> -------
> 1
>
> but no inserts:
>
> SELECT n_tup_ins FROM pg_stat_user_tables
> WHERE relname = 'flush_test';
> n_tup_ins
> -----------
> 0
>
> I would have expected n_tup_ins = 1.
>
> I wonder if the committed transaction’s counters should be flushed before the
> procedure starts the next transaction?
>
> Regards,

Thanks for raising this!

In v18, pgstat_relation_flush_cb() held back tab.counts_xact whenever
tab.trans != NULL, assuming it could have counts of the running
transaction. For inserts, updates and deletes it never does. Those
stay in tab.trans until the (sub)transaction ends. The only exception
was the HOT and new-page update counts.

In your example, the procedure never goes idle, so there is no flush
at idle. After the COMMIT, counts_xact has the committed counts, the
second INSERT creates tab.trans again, and the forced flush holds them
back. A multi-statement query string hits the same thing, e.g.
"BEGIN \; INSERT ... \; COMMIT \; INSERT ... \;
SELECT pg_stat_force_next_flush()".

So I don't think we need to flush before the procedure starts the next
transaction, as you suggest. That would add a flush to every COMMIT in
a procedure, even when nobody asked for one. Instead, v19 just lets
pgstat_relation_flush_cb() flush counts_xact, along with the
nontransactional tab.counts, every time, the same way
pgstat_index_flush_cb() already does.

The one catch was the HOT and new-page update counts, since
pgstat_count_heap_update() added those to counts_xact right away. v19
now counts them in tab.trans along with tuples_updated, so a flush
can't publish the running transaction's HOT updates ahead of its
updates.

This only changes the relation callback. We still need
PGSTAT_FLUSH_PARTIAL for kinds that defer transactional counters, like
the custom stats example; the relation callback just doesn't return it
anymore.

With v19, your example reports n_tup_ins = 1 during the sleep. I added
a test for this in stats.sql.

I also spent some more time looking at this patch, and found a few
unrelated fixes:

1/ I removed the CHECK_FOR_INTERRUPTS() from the flush loop. The loop
is short anyway, and the check could let a cancel request turn the
flush at idle into an ERROR after the query had already finished.

2/ I restored the custom stats reset test in 001_custom_stats.pl,
which I dropped by mistake back in v8.

3/ I renamed the partial_flush* tables in stats.sql to xact_flush*,
since relation flushes are no longer partial.

4/ The docs and the pg_stat_force_next_flush() comment said it is
mainly useful in tests. Now that it flushes within a transaction, it
is also useful for long-running transactions and extensions, so I
updated both.

5/ A few comment fixes where the comments still described the old
behavior.

Attached is v19.

--
Sami Imseih
Amazon Web Services (AWS)