Re: Show WAL write and fsync stats in pg_stat_io

Michael Paquier <michael(at)paquier(dot)xyz> writes:
> The queries for the normal context are not going to have this problem
> even if we have a pg_stat_reset_shared('io'), but the init context
> gets unstable, unfortunately. I don't see a way through here in the
> main regression test suite, so how about moving these into
> 027_stream_regress.pl. It is possible to query the WAL read on the
> standby of this test, and the write part for the init context on the
> primary. The syncs are not relevant as TAP usually runs with
> fsync=off, so better to remove this part entirely.

Yeah, if we want to assume we can see stats counts left over from
initdb, we have to put this in a TAP test, though I dunno if that is
the most appropriate one.

Now that I've looked at the tests a bit, I'm also distressed
by this test pattern:

SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT pg_stat_reset_slru();
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';

This assumes that the execution time of pg_stat_reset_slru() is more
than the system clock resolution. I won't be surprised to see that
fail in the future. We did discover recently that gettimeofday is
good to the microsecond on most modern platforms [1], but it won't
get any better than that, while our machines keep getting faster.
Just for reference, on my hardly-bleeding-edge-anymore workstation:

regression=# select clock_timestamp(), pg_stat_reset_slru(), clock_timestamp();
clock_timestamp | pg_stat_reset_slru | clock_timestamp
-------------------------------+--------------------+-------------------------------
2025-02-05 21:47:54.929221-05 | | 2025-02-05 21:47:54.929223-05
(1 row)

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/be0339cc-1ae1-4892-9445-8e6d8995a44d%40eisentraut.org