Re: Do we need to handle orphaned prepared transactions in the server?

On Thu, Jan 23, 2020 at 12:56:41PM +0800, Craig Ringer wrote:
> We could definitely improve on that by exposing a view that integrates
> everything that holds down xmin and catalog_xmin. It'd show
>
> * the datfrozenxid and datminmxid for the oldest database
> * if that database is the current database, the relation(s) with the
> oldest relfrozenxid and relminmxd
> * ... and the catalog relation(s) with the oldest relfrozenxid and
> relminmxid if greater
> * the absolute xid and xid-age positions of entries in pg_replication_slots
> * pg_stat_replication connections (joined to pg_stat_replication if
> connected) with their feedback xmin
> * pg_stat_activity backend_xid and backend_xmin for the backend(s)
> with oldest values; this may be different sets of backends
> * pg_prepared_xacts entries by oldest xid

It seems to me that what you are describing here is a set of
properties good for a monitoring tool that we don't necessarily need
to maintain in core. There are already tools able to do that in ways
I think are better than what we could ever design, like
check_pgactivity and such. And there are years of experience behind
that from the maintainers of such scripts and/or extensions.

The argument about Postgres getting more and more complex is true as
the code grows, but I am not really convinced that we need to make it
grow more with more layers that we think are good, because we may
finish by piling up stuff which are not actually that good in the long
term. I'd rather just focus in the core code on the basics with views
that map directly to what we have in memory and/or disk.
--
Michael