Re: mxid_age() and age(xid) appear undocumented

On Mon, Nov 13, 2023 at 5:01 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Nov 13, 2023 at 4:43 PM Bruce Momjian <bruce@momjian.us> wrote:
> > I looked into this and all the 4-byte xid functions are marked as
> > deprecated for the 8-byte variants.  I don't think documenting 4-byte
> > mxid_age() and age(xid) makes sense anymore, and I don't see their value
> > enough to create 8-byte versions, so I just added C comments that they
> > were undocumented, in the attached patch.
>
> I'm sympathetic to the goal of making 4 byte XIDs an on-disk
> implementation detail that is all but completely hidden from users.
> However, there are practical problems with taking that to its logical
> extreme. At least right now.
>
> These functions are in fact documented -- albeit only partially. There
> are references to both in "Routine Vacuuming". Moreover, those
> references are rather useful; they're the basis of many
> monitoring/alerting queries. If anything, I'd recommend adding more
> documentation for these two functions.
>
> We also continue to show 32-bit XIDs (alongside 32-bit relfrozenxid)
> in the output of VACUUM VERBOSE/autovacuum log messages. (Though that
> issue can be fixed fairly easily.)
>
> The bottom line is that there is only one way to figure out the age of
> a table right now, and it involves 32-bit XIDs/MXIDs, and these two
> functions. And, if we were to change something in this area, we'd
> definitely need to provide for the needs of those monitoring queries I
> mentioned.

Also, the doc page [1] mentions mxid_age(), but doesn't provide a
snippet to use it. The regular XID wraparound section above has such a
snippet. As a consequence, almost nobody monitors for MultiXact
wraparound – I noticed it recently once again, exploring numerous blog
posts and tools on this topic to write a howto [2] in my collection.

In other words, maybe there should be not only a reference doc for the
function itself present in the doc (the lack of it seems to be an
issue for all older versions), but also a snippet to control MultiXact
ID wraparound – while it's still a potential problem, it would be good
to have both a function reference doc and a how-to-use-it doc.

[1] https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND
[2] https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0044_how_to_monitor_transaction_id_wraparound_risks.md