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

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2020-Apr-01, Tom Lane wrote:
>> 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.

+1, that would help a lot.

> 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.

Agreed, but just knowing what the oldest xmin is doesn't help you
find *where* it is.  Maybe what we need is a view showing all of
these potential sources of an old xmin.

            regards, tom lane