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

On Thu, 2 Apr 2020 at 07:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alvaro Herrera <alvherre(at)2ndquadrant(dot)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.

Strongly agree.

https://www.postgresql.org/message-id/CAMsr+YGSS6JBHmEHbxqMdc1XJ7sobDSq62YwaEkOHN-KBQYr-A@mail.gmail.com

I was aiming to write such a view, but folks seemed opposed. I still think
it'd be a very good thing to have built-in as Pg grows more complex.

--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise