Re: Flush some statistics within running transactions

Hi,

On Mon, Mar 16, 2026 at 03:26:33PM +0900, Michael Paquier wrote:
> On Tue, Feb 24, 2026 at 12:01:30PM +0000, Bertrand Drouvot wrote:
> > Though I don't think that adresses Michael's concern: "main worries are
> > mainly around 1), I guess, with the new SIGALRM handler requirements for all
> > auxiliary processes" in [1].
>
> FWIW, I am still concerned about that, and I have pondered about what
> we could do here. While reviewing the existing code, one thing that I
> have noticed we could do is rely on the existing interface of
> pgstat_report_stat() without changing the existing callers, and not
> touching at all the flush callbacks. If we begin to require the
> "force" mode when the routine the called inside a transaction block,
> things seem to work pretty smoothly in combination with a stats kind
> property that allows the stats data to be flushed if we are inside a
> transaction while a report happens.

Yeah, "force" makes use of GetCurrentTimestamp() (and so we avoid a failed
assertion that we would get if using GetCurrentTransactionStopTimestamp()).

> So please find attached my shot at that:

Thanks!

> - Introduction of a new system function called pg_stat_report(), based
> on a procsignal that gives a way to signal backends for a stats
> update, reusing the existing code where we only do flushes when idle
> and not in a transaction.
> - Property that tracks under which contexts the reports are allowed.
> Here I have decided to stick with simple, as in only allowing IO and
> WAL stats to be flushed if we are inside a transaction.
>
> Using that, I have done a few tests with three backends:
> - One with a long-running transaction.
> - One that periodically triggers the reports.
> - One that looks at IO and WAL stat.
> And the third session is able to get refreshes for both of these stats
> kinds, while the other stats remain the same.

I did not look closely at the code but did some testing too. I confirm that
pg_stat_io and pg_stat_wal are updated when pg_stat_report(<backend_pid>) is
triggered. But the stats update is not visible if requested through
pg_stat_get_backend_io(<same_backend_pid>) or pg_stat_get_backend_wal(<same_backend_pid>)).
I guess that PGSTAT_KIND_BACKEND should also get the PGSTAT_REPORT_TRANSACTION
report_context?

> Note that this is a WIP, which is check-world stable. One thing that
> sticks a bit in mind now is that perhaps we should not allow the
> function for auxiliary processes at all.

Why?

> A second thing is the
> requirement of allowing partial flushes at the end of the report path,
> which is OK because the variable-sized stats can have pending data.

Right.

> Perhaps we should just have pgstat_flush_pending_entries() provide a
> correct status in line with the property set in a stats kind when we
> try a flush while in a transaction.

The idea would be to avoid trying to flush stats that don't have pending
entries?

> Thoughts or tomatoes?

That looks "simpler" that the previous proposal but who would be responsible to
call pg_stat_report()? If that's the client responsabilty that kind of look weird
to me. If that's the core, how would that be scheduled? I think that the
end solution should prevent to find similar issues as 039549d70f6 fixed, without
delegating to the client.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com