On Mon, Jul 28, 2025 at 9:52 AM Michael Paquier <mich...@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
diff --git a/src/backend/access/transam/multixact.c
b/src/backend/access/transam/multixact.c
index 6b3f38a2fd6..8b31b57140d 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2863,17 +2863,16 @@ find_multixact_start(MultiXactId multi, MultiXactOffset
*result)
* exist. Return false if unable to determine.
*/
static bool
-ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
+ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members, MultiXactId
*oldestMultiXactId)
{
MultiXactOffset nextOffset;
MultiXactOffset oldestOffset;
- MultiXactId oldestMultiXactId;
MultiXactId nextMultiXactId;
bool oldestOffsetKnown;
LWLockAcquire(MultiXactGenLock, LW_SHARED);
nextOffset = MultiXactState->nextOffset;
- oldestMultiXactId = MultiXactState->oldestMultiXactId;
+ *oldestMultiXactId = MultiXactState->oldestMultiXactId;
nextMultiXactId = MultiXactState->nextMXact;
oldestOffset = MultiXactState->oldestOffset;
oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
@@ -2883,7 +2882,7 @@ ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset
*members)
return false;
*members = nextOffset - oldestOffset;
- *multixacts = nextMultiXactId - oldestMultiXactId;
+ *multixacts = nextMultiXactId - *oldestMultiXactId;
return true;
}
@@ -2922,9 +2921,10 @@ MultiXactMemberFreezeThreshold(void)
uint32 victim_multixacts;
double fraction;
int result;
+ MultiXactId oldestMultiXactId;
/* If we can't determine member space utilization, assume the worst. */
- if (!ReadMultiXactCounts(&multixacts, &members))
+ if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
return 0;
/* If member space utilization is low, no special action is required. */
@@ -3503,22 +3503,24 @@ Datum
pg_get_multixact_count(PG_FUNCTION_ARGS)
{
TupleDesc tupdesc;
- Datum values[2];
- bool nulls[2] = {false, false};
+ Datum values[3];
+ bool nulls[3] = {false, false, false};
MultiXactOffset members;
uint32 multixacts;
HeapTuple tuple;
+ MultiXactId oldestMultiXactId;
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
ereport(ERROR,
(errmsg("return type must be a row type")));
- if (!ReadMultiXactCounts(&multixacts, &members))
+ if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
ereport(ERROR,
(errmsg("could not read multixact counts")));
values[0] = UInt32GetDatum(multixacts);
values[1] = UInt32GetDatum(members);
+ values[2] = UInt32GetDatum(oldestMultiXactId);
tuple = heap_form_tuple(tupdesc, values, nulls);
PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0e511dd3473..2be0cd35e12 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12579,13 +12579,13 @@
# Returns current counts of multixact members and multixact IDs
{
oid => '9001',
- descr => 'get current multixact member and multixact ID counts',
+ descr => 'get current multixact member and multixact ID counts and oldest
multixact',
proname => 'pg_get_multixact_count',
prorettype => 'record',
proargtypes => '',
- proallargtypes => '{int4,int8}',
- proargmodes => '{o,o}',
- proargnames => '{multixacts,members}',
+ proallargtypes => '{int4,int8,int4}',
+ proargmodes => '{o,o,o}',
+ proargnames => '{multixacts,members,oldestmultixact}',
provolatile => 'v',
proparallel => 's',
prosrc => 'pg_get_multixact_count'
