Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring

On Mon, Jul 28, 2025 at 9:52 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> May I also suggest a split of the multixact SQL functions into a
> separate file, a src/backend/utils/adt/multixactfuncs.c?  The existing
> pg_get_multixact_members() relies on GetMultiXactIdMembers(),
> available in multixact.h.  The new function pg_get_multixact_count()
> relies on ReadMultiXactCounts(), which would mean adding it in
> multixact.h.  Even if we finish without an agreement about the SQL
> function and the end, publishing ReadMultiXactCounts() would give an
> access to the internals to external code.
>
> +PG_FUNCTION_INFO_V1(pg_get_multixact_count);
>
> There should be no need for that, pg_proc.dat handling the
> declaration AFAIK.
>
> FWIW, these functions are always kind of hard to use for the end-user
> without proper documentation.  You may want to add an example of how
> one can use it for monitoring in the docs.

+1.

Let's say if the user knows that the counts are so high that a
wraparound is imminent, but vacuuming isn't solving the problem, they
would like to know which transactions are holding it back.
pg_get_multixact_members() can be used to get the members of the
oldest multixact if it's reported and then the user can deal with
those transactions. However, the oldest multixact is not reported
anywhere, AFAIK. It's also part of MultiXactState, so can be extracted
via ReadMultiXactCounts(). We could report it through
pg_get_multixact_counts - after renaming it and ReadMultiXactCounts to
pg_get_multixact_stats() and ReadMultiXactStats() respectively. Or we
could write another function to do so. But it comes handy using query
like below
#select oldestmultixact,
pg_get_multixact_members(oldestmultixact::text::xid) from
pg_get_multixact_count();
 oldestmultixact | pg_get_multixact_members
------------------+--------------------------
                1 | (757,sh)
                1 | (768,sh)
(2 rows)

Here's a quick patch implementing the same. Please feel free to
incorporate and refine it in your patch if you like it.

--
Best Wishes,
Ashutosh Bapat