Date: Mon, 16 Mar 2026 09:20:41 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Sami Imseih <samimseih@gmail.com>, Fujii Masao <masao.fujii@gmail.com>,
	pgsql-hackers@lists.postgresql.org,
	Zsolt Parragi <zsolt.parragi@percona.com>
Subject: Re: Flush some statistics within running transactions
Message-ID: <abfLaVT5qIbPtzsQ@ip-10-97-1-34.eu-west-3.compute.internal>
References: <aZVQ3nT9B6ti8whJ@ip-10-97-1-34.eu-west-3.compute.internal>
 <aZaKVOrRO_bHsevn@paquier.xyz>
 <aZbDYMrOkeCyIubO@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0u2Vi4-PvHsFBS6aHpoi-uNQjDaLxnAwXxRuUf6ZDMs3Q@mail.gmail.com>
 <aZiD74ZVehxfgqxM@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0t1DsB5x_reGAv0AcKdKuF5FTowUx54SLnWkD3w5vH4Lg@mail.gmail.com>
 <aZwMdVBYobV41duU@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0vaB+fXHt76bi_Koj33JoerdgfMYFxHn-D1N+SPSGwL2w@mail.gmail.com>
 <aZ2TGo5bkDvYOfIw@ip-10-97-1-34.eu-west-3.compute.internal>
 <abeimWKYq-n34FrH@paquier.xyz>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <abeimWKYq-n34FrH@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/abfLaVT5qIbPtzsQ%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk

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