v2-0001-Add-pg_get_multixact_stats-SQL-function-for-monit.patch

From 180ef463808023fa117595ce554326028724cf43 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Mon, 4 Aug 2025 03:17:28 +0000
Subject: [PATCH v2] Add pg_get_multixact_stats() SQL function for monitoring
 MultiXact usage

This patch adds pg_get_multixact_stats(), a SQL-callable function that returns
MultiXact statistics to aid in monitoring wraparound risk and vacuum behavior.
It reports:

multixacts: the number of MultiXact IDs created since the oldest one still needed
members: the number of MultiXact member entries that currently exist
oldest_multixact: the oldest MultiXact ID still needed by any database

The function modifies ReadMultiXactCounts() to expose the oldestMultiXactId and
returns all three values in a composite record. This allows users to monitor
MultiXact usage and identify potential wraparound issues, particularly useful
when combined with pg_get_multixact_members() to investigate specific MultiXacts.

Usage:
SELECT * FROM pg_get_multixact_stats();

Documentation is added to:
- "Transaction ID and Snapshot Information Functions" section in func.sgml
- "Multixacts and Wraparound" section in maintenance.sgml
  (routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND)

Isolation tests are added to verify:
- Initial state with zero MultiXacts
- MultiXact creation with overlapping shared locks
- Correct counting of MultiXacts and members
- Proper tracking of oldest MultiXact ID

Author: Naga Appani <nagnifk@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CAM2BeoX%2BRasKfG6W8w4qYZZz4BnhyEQMA_y5cEDnKEY_z8o9Czg%40mail.gmail.com
---
 doc/src/sgml/func.sgml                        | 32 ++++++++
 doc/src/sgml/maintenance.sgml                 | 37 ++++++++-
 src/backend/access/transam/multixact.c        | 79 ++++++++++++++-----
 src/include/catalog/pg_proc.dat               | 14 ++++
 .../isolation/expected/multixact_stats.out    | 59 ++++++++++++++
 src/test/isolation/specs/multixact_stats.spec | 35 ++++++++
 6 files changed, 233 insertions(+), 23 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 74a16af04ad..0255a51cdad 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27732,6 +27732,38 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+     <row>
+      <entry role="func_table_entry">
+       <para role="func_signature">
+        <indexterm><primary>pg_get_multixact_stats</primary></indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>multixacts</parameter> <type>integer</type>,
+          <parameter>members</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>integer</type> )
+       </para>
+
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>multixacts</literal> is the number of multixact IDs assigned,
+        <literal>members</literal> is the number of multixact member entries created,
+        and <literal>oldest_multixact</literal> is the oldest multixact ID still in use.
+        These values can be used to monitor multixact consumption and anticipate
+        autovacuum behavior. See <xref linkend="vacuum-for-multixact-wraparound"/>
+        for further details on multixact wraparound.
+       </para>
+
+       <para>
+        <literal>SELECT * FROM pg_get_multixact_stats();</literal>
+<programlisting>
+ multixacts |   members   | oldest_multixact
+------------+-------------+------------------
+ 182371396  | 2826221174  | 754321
+</programlisting>
+       </para>
+      </entry>
+     </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..d6bd305b0b0 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,12 +813,41 @@ HINT:  Execute a database-wide VACUUM in that database.
     <para>
      As a safety device, an aggressive vacuum scan will
      occur for any table whose multixact-age is greater than <xref
-     linkend="guc-autovacuum-multixact-freeze-max-age"/>.  Also, if the
-     storage occupied by multixacts members exceeds about 10GB, aggressive vacuum
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if either
+     the storage occupied by multixact members exceeds about 10GB or the number
+     of members created exceeds approximately 2 billion entries, aggressive vacuum
      scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
+     have the oldest multixact-age. Both of these kinds of aggressive
      scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     area can grow up to about 20GB or approximately 4 billion entries before
+     reaching wraparound.
+    </para>
+
+    <para>
+     The <function>pg_get_multixact_stats()</function> function provides a way
+     to monitor multixact allocation and usage patterns in real time. By exposing
+     the age of the oldest multixact ID, number of member entries, and the oldest multixact ID still in use, it helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+         Identify unusual multixact activity from concurrent row-level locks
+         or foreign key operations
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+         Monitor progress toward wraparound thresholds that trigger aggressive
+         autovacuum (approximately 2 billion members or 10GB storage)
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+         Verify whether autovacuum is effectively managing multixact cleanup
+         before reaching critical thresholds
+       </simpara>
+      </listitem>
+     </orderedlist>
+     See <xref linkend="functions-info-snapshot"/> for details.
     </para>
 
     <para>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3cb09c3d598..59e8fc17b7f 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2863,28 +2863,27 @@ 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;
+    MultiXactOffset nextOffset;
+    MultiXactOffset oldestOffset;
+    MultiXactId nextMultiXactId;
+    bool oldestOffsetKnown;
 
-	LWLockAcquire(MultiXactGenLock, LW_SHARED);
-	nextOffset = MultiXactState->nextOffset;
-	oldestMultiXactId = MultiXactState->oldestMultiXactId;
-	nextMultiXactId = MultiXactState->nextMXact;
-	oldestOffset = MultiXactState->oldestOffset;
-	oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
-	LWLockRelease(MultiXactGenLock);
+    LWLockAcquire(MultiXactGenLock, LW_SHARED);
+    nextOffset = MultiXactState->nextOffset;
+    *oldestMultiXactId = MultiXactState->oldestMultiXactId;  /* Use the parameter directly */
+    nextMultiXactId = MultiXactState->nextMXact;
+    oldestOffset = MultiXactState->oldestOffset;
+    oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
+    LWLockRelease(MultiXactGenLock);
 
-	if (!oldestOffsetKnown)
-		return false;
+    if (!oldestOffsetKnown)
+        return false;
 
-	*members = nextOffset - oldestOffset;
-	*multixacts = nextMultiXactId - oldestMultiXactId;
-	return true;
+    *members = nextOffset - oldestOffset;
+    *multixacts = nextMultiXactId - *oldestMultiXactId;  /* Use the parameter */
+    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. */
@@ -3493,3 +3493,44 @@ multixactmemberssyncfiletag(const FileTag *ftag, char *path)
 {
 	return SlruSyncFileTag(MultiXactMemberCtl, ftag, path);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * SQL-callable function to retrieve MultiXact statistics.
+ *
+ * Returns a composite row containing:
+ * - total number of MultiXact IDs created since startup,
+ * - total number of MultiXact members created,
+ * - the oldest existing MultiXact ID.
+ *
+ * This is primarily useful for monitoring MultiXact usage and ensuring
+ * appropriate wraparound protection.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[3];
+	bool		nulls[3] = {false, false, false};
+	MultiXactOffset members;
+	MultiXactId oldestMultiXactId;
+	uint32		multixacts;
+	HeapTuple	tuple;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errmsg("return type must be a row type")));
+
+	if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
+		PG_RETURN_NULL();
+
+	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 3ee8fed7e53..756ba39425c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,18 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+{
+  oid => '9001',
+  descr => 'get current multixact member and multixact ID counts and oldest multixact',
+  proname => 'pg_get_multixact_stats',
+  prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8,int4}',
+  proargmodes => '{o,o,o}',
+  proargnames => '{multixacts,members,oldest_multixact}',
+  provolatile => 'v',
+  proparallel => 's',
+  prosrc => 'pg_get_multixact_stats'
+},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..54e3238d727
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,59 @@
+Parsed test spec with 3 sessions
+
+starting permutation: stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
+step stats_init: 
+    CREATE TEMP TABLE stats_before AS 
+    SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats(); 
+
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         0|      0|               1
+(1 row)
+
+step begin1: BEGIN;
+step lock1: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step begin2: BEGIN;
+step lock2: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         1|      3|               1
+(1 row)
+
+step commit1: COMMIT;
+step commit2: COMMIT;
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         1|      3|               1
+(1 row)
+
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..53fcad38c54
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,35 @@
+setup
+{
+    CREATE TABLE multixact_test(id int PRIMARY KEY, val int);
+    INSERT INTO multixact_test VALUES (1, 10);
+}
+
+teardown
+{
+    DROP TABLE multixact_test;
+}
+
+session s1
+step begin1     { BEGIN; }
+step lock1      { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit1    { COMMIT; }
+
+session s2
+step begin2     { BEGIN; }
+step lock2      { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit2    { COMMIT; }
+
+session s3
+step stats_init { 
+    CREATE TEMP TABLE stats_before AS 
+    SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats(); 
+}
+step check      {
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+}
+
+permutation stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
-- 
2.47.3