Re: Proposal: Expose oldest xmin as SQL function for monitoring

On 2020-Apr-01, Tom Lane wrote:

> James Coleman <jtc331(at)gmail(dot)com> writes:
> > To my knowledge the current oldest xmin (GetOldestXmin() if I'm not
> > mistaken) isn't exposed directly in any view or function by Postgres.
>
> You could do something like
>
> select max(age(backend_xmin)) from pg_stat_activity;
>
> though I'm not sure whether that accounts for absolutely every process.
>
> > Am I missing anything in the above description? And if not, would
> > there be any reason why we would want to avoid exposing that
> > information? And if not, then would exposing it as a function be
> > acceptable?
>
> The fact that I had to use max(age(...)) in that sample query
> hints at one reason: it's really hard to do arithmetic correctly
> on raw XIDs. Dealing with wraparound is a problem, and knowing
> what's past or future is even harder. What use-case do you
> foresee exactly?

Maybe it would make sense to start exposing fullXids in these views and
functions, for this reason. There's no good reason to continue to
expose bare Xids to userspace, we should use them only for storage.

But I think James' point is precisely that it's not easy to know where
to look for things that keep Xmin from advancing. Currently it's
backends, replication slots, prepared transactions, and replicas with
hot_standby_feedback. If you forget to monitor just one of these, your
vacuums might be useless and you won't notice until disaster strikes.

Maybe a useful value to publish in some monitoring view is
RecentGlobalXmin -- which has a valid value when reading a view, since
you had to acquire a snapshot to read the view in the first place.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services