v1-0001-Add-pg_get_multixact_count-function-and-related-d.patch

From 0f9bff594eccf2f7aea288e73f8c147edf884857 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Fri, 6 Jun 2025 05:18:15 +0000
Subject: [PATCH v1] Add pg_get_multixact_count function and related

Add pg_get_multixact_count() SQL function for monitoring MultiXact usage

PostgreSQL exposes mxid_age() to track MultiXact ID wraparound risk,
but there is currently no SQL-accessible way to monitor MultiXact member
consumption, which can independently trigger aggressive vacuuming or
wraparound protection. The only workaround today involves scanning the
pg_multixact/members directory, which is I/O intensive and unsuitable
for monitoring tools.

This patch adds pg_get_multixact_count(), a SQL-callable function that
returns a composite record with two fields:
- multixacts:  number of MultiXact IDs that currently exist
- members:     number of MultiXact member entries that currently exist

The function calls ReadMultiXactCounts() and returns the values using the
standard record output convention.

Documentation has been added to func.sgml under a new section titled
"MultiXact Information Functions", with a cross-reference in maintenance.sgml
to help users track MultiXact usage relative to autovacuum thresholds.

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by:
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com

---
 doc/src/sgml/func.sgml                 | 58 ++++++++++++++++++++++++++
 doc/src/sgml/maintenance.sgml          |  8 ++++
 src/backend/access/transam/multixact.c | 30 +++++++++++++
 src/include/catalog/pg_proc.dat        | 15 +++++++
 4 files changed, 111 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..bdb64f83c23 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28496,6 +28496,64 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-info-multixact-information">
+   <title>MultiXact Information Functions</title>
+
+   <para>
+    The function shown in <xref linkend="functions-multixact-information"/>
+    exposes internal MultiXact counters used by
+    <productname>PostgreSQL</productname>'s locking and transaction management subsystems.
+    It is primarily intended for monitoring and diagnostic purposes, such as analyzing
+    MultiXact consumption patterns or anticipating wraparound-related maintenance.
+   </para>
+
+   <table id="functions-multixact-information">
+    <title>MultiXact Information Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry">
+        <para role="func_signature">Function</para>
+        <para>Description</para>
+       </entry>
+      </row>
+     </thead>
+     <tbody>
+      <row>
+       <entry role="func_table_entry">
+        <para role="func_signature">
+         <indexterm><primary>pg_get_multixact_count</primary></indexterm>
+         <function>pg_get_multixact_count</function> ()
+         <returnvalue>record</returnvalue>
+        </para>
+        <para>
+         Returns a record with the fields <structfield>multixacts</structfield> and <structfield>members</structfield>:
+         <itemizedlist>
+          <listitem>
+           <para><structfield>multixacts</structfield>: Number of MultiXacts assigned.
+            PostgreSQL initiates aggressive autovacuum when this value grows beyond the threshold
+            defined by <varname>autovacuum_multixact_freeze_max_age</varname>, which is based on
+            the age of <literal>datminmxid</literal>. For more details, see
+            <ulink url="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND">
+            Routine Vacuuming: Multixact Wraparound</ulink>.</para>
+          </listitem>
+          <listitem>
+           <para><structfield>members</structfield>: Number of MultiXact member entries created.
+            These are stored in files under the <filename>pg_multixact/members</filename> subdirectory.
+            Wraparound occurs after approximately 4.29 billion entries (~20 GiB). PostgreSQL initiates
+            aggressive autovacuum when the number of members created exceeds approximately 2.145 billion
+            or when storage consumption in <filename>pg_multixact/members</filename> approaches 10 GiB.</para>
+          </listitem>
+         </itemizedlist>
+        </para>
+       </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
 
   <sect1 id="functions-admin">
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 600e4b3f2f3..a445d1b061c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -818,6 +818,14 @@ HINT:  Execute a database-wide VACUUM in that database.
      area can grow up to about 20GB before reaching wraparound.
     </para>
 
+    <para>
+     The <function><link linkend="functions-multixact-information">pg_get_multixact_count</link></function>
+     function provides a way to check how many multixacts and member entries have been allocated. This can
+     be useful for identifying unusual multixact activity, monitoring progress toward wraparound, anticipating
+     system-wide aggressive autovacuum as usage approaches critical thresholds, or verifying whether autovacuum
+     is keeping up with demand.
+    </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3c06ac45532..ed29746eaa9 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -3585,3 +3585,33 @@ multixactmemberssyncfiletag(const FileTag *ftag, char *path)
 {
 	return SlruSyncFileTag(MultiXactMemberCtl, ftag, path);
 }
+
+/*
+ * Returns the current count of multixact members and multixact IDs
+ */
+PG_FUNCTION_INFO_V1(pg_get_multixact_count);
+
+Datum
+pg_get_multixact_count(PG_FUNCTION_ARGS)
+{
+	TupleDesc		tupdesc;
+	Datum			values[2];
+	bool			nulls[2] = {false, false};
+	MultiXactOffset	members;
+	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))
+		ereport(ERROR,
+				(errmsg("could not read multixact counts")));
+
+	values[0] = UInt32GetDatum(multixacts);
+	values[1] = UInt32GetDatum(members);
+
+	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 d3d28a263fa..09115ad1b35 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12556,4 +12556,19 @@
   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' },
 
+# Returns current counts of multixact members and multixact IDs
+{
+  oid => '9001',
+  descr => 'get current multixact member and multixact ID counts',
+  proname => 'pg_get_multixact_count',
+  prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8}',
+  proargmodes => '{o,o}',
+  proargnames => '{multixacts,members}',
+  provolatile => 'v',
+  proparallel => 's',
+  prosrc => 'pg_get_multixact_count'
+},
+
 ]
-- 
2.47.1