Re: Add last_commit_lsn to pg_stat_database

At Tue, 20 Feb 2024 07:56:28 +0900, Michael Paquier <michael(at)paquier(dot)xyz> wrote in
> On Mon, Feb 19, 2024 at 10:26:43AM +0100, Tomas Vondra wrote:
> It just means that I am not much a fan of the signature changes done
> for RecordTransactionCommit() and AtEOXact_PgStat_Database(), adding a
> dependency to a specify LSN. Your suggestion to switching to
> XactLastRecEnd should avoid that.
>
> > - stats_fetch_consistency=cache: always the same min/max value
> >
> > - stats_fetch_consistency=none: min != max
> >
> > Which would suggest you're right and this should be VOLATILE and not
> > STABLE. But then again - the existing pg_stat_get_db_* functions are all
> > marked as STABLE, so (a) is that correct and (b) why should this
> > function be marked different?
>
> This can look like an oversight of 5891c7a8ed8f to me. I've skimmed
> through the threads related to this commit and messages around [1]
> explain why this GUC exists and why we have both behaviors, but I'm
> not seeing a discussion about the volatibility of these functions.
> The current situation is not bad either, the default makes these
> functions stable, and I'd like to assume that users of this GUC know
> what they do. Perhaps Andres or Horiguchi-san can comment on that.
>
> https://www.postgresql.org/message-id/382908.1616343275@sss.pgh.pa.us

I agree that we cannot achieve, nor do we need, perfect MVCC behavior,
and that completely volatile behavior is unusable. I believe the
functions are kept marked as stable, as this is the nearest and most
usable volatility for the default behavior, since function volatility
is not switchable on-the-fly. This approach seems least trouble-prone
to me.

The consistency of the functions are discussed here.

https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-STATS-VIEWS

> This is a feature, not a bug, because ... Conversely, if it's known
> that statistics are only accessed once, caching accessed statistics is
> unnecessary and can be avoided by setting stats_fetch_consistency to
> none.

It seems to me that this description already implies such an
incongruity in the functions' behavior from the "stable" behavior, but
we might want to explicitly mention that incongruity.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center