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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, James Coleman <jtc331(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal: Expose oldest xmin as SQL function for monitoring
Date: 2020-04-02 19:15:44
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2020-04-01 19:57:32 -0400, Tom Lane wrote:
>> 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.

> +1. This would be extermely useful. It's a very common occurance to
> have to ask for a number of nontrivial queries when debugging xmin
> related bloat issues.

> Having a view that lists something like:

> - shared xmin horizon
> - pid of backend with oldest xmin across all backends

I was envisioning a view that would show you *all* the active processes
and their related xmins, then more entries for all active replication
slots, prepared xacts, etc etc. Picking out the ones causing trouble is
then the user's concern. If the XID column is actually fullXid then
sorting, aggregating, etc. is easy.

The point about database-local vs not is troublesome. Maybe two
such views would be needed?

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-04-02 19:20:30 Re: Should we add xid_current() or a int8->xid cast?
Previous Message Tom Lane 2020-04-02 19:06:50 Re: control max length of parameter values logged