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

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