public inbox for [email protected]
help / color / mirror / Atom feedFrom: Naga Appani <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: torikoshia <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: [email protected]
Subject: Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring
Date: Thu, 11 Sep 2025 22:32:59 -0500
Message-ID: <CA+QeY+A2B7mjWc8kkieNS_nxNyTeZpTzi4Ue+vtHzkmfFxO-8Q@mail.gmail.com> (raw)
In-Reply-To: <CAExHW5tDxjXKcP0XuUZXb_UbpAs_oQ29HyOtvL0xf7dCkk5ypw@mail.gmail.com>
References: <CALdSSPi3Gh08NtcCn44uVeUAYGOT74sU6uei_06qUTa5rMK43g@mail.gmail.com>
<CA+QeY+DTggHskCXOa39nag2sFds9BD-7k__zPbvL-_VVyJw7Sg@mail.gmail.com>
<CAExHW5uxBnodmpXygUQUxZAyxYvbXczadxGOVeL=0DxpkXUR-g@mail.gmail.com>
<[email protected]>
<CAExHW5vba_ecL2c27giAOOaRxoNdVX2BNQVCN4PFOZJC17NrqQ@mail.gmail.com>
<CA+QeY+CFLjh0gb-j9p+0eJK=r19izhEPCBY3ns4+HCgOQojh8Q@mail.gmail.com>
<CAExHW5s5hnxKK_VE=q94sN8uU5BJCRpAescGuamheuM-Ua559A@mail.gmail.com>
<CA+QeY+C3tc12wWX56yHozLYK=MmCkn1fODzFWC6-dSY+TZghTQ@mail.gmail.com>
<CAExHW5s-StFwDpZBeiikvdnrBBr0s+2jJTZRv3Hh914T4C6Euw@mail.gmail.com>
<CA+QeY+AmRSSJU8GKJ1uNVe2ps8GTONXpioYR7gEz91_prKR6sw@mail.gmail.com>
<[email protected]>
<CA+QeY+AwWV=g8z1eDE7OGHxKBer18gVER7nD+SnfhqLmiL7NOA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAExHW5sUU1+Vq9oG6x=u85W7Obr-dPfAc7rQtaN-LJw5uufuMA@mail.gmail.com>
<CA+QeY+DLsjj4RxY-Z2wRC-sYCeFxuPSd8zSkt4Nvs==YV6GP6w@mail.gmail.com>
<CAExHW5tDxjXKcP0XuUZXb_UbpAs_oQ29HyOtvL0xf7dCkk5ypw@mail.gmail.com>
Hi Ashutosh,
Thank you for continuing to review the patch. Attached is v8,
incorporating the feedback. Please see my responses inline below.
On Fri, Sep 5, 2025 at 6:27 AM Ashutosh Bapat
<[email protected]> wrote:
>
> This one is remaining.
> + up to approximately 2^32 entries before reaching wraparound.
>
> ... 2^32 entries (occupying roughly 20GB in the
> <literal>pg_multixact/members</literal> directory) before reaching
> wraparound. ...
Done.
> + See <xref linkend="vacuum-for-multixact-wraparound"/> for further
> details on multixact wraparound.
>
> I don't think we need this reference here. Reference back from that
> section is enough.
I kept the cross-reference for now since other multixact function docs
(such as pg_get_multixact_members()) already use this style, and it helps
readers who land directly on the function reference page. Please let me
know if you would prefer that I remove it.
> + * Returns NULL if the oldest referenced offset is unknown, which
> happens during
> + * system startup or when no MultiXact references exist in any relation.
>
> If no MultiXact references exist, and GetMultiXactInfo() returns
> false, MultiXactMemberFreezeThreshold() will assume the worst, which I
> take as meaning that it will trigger aggressive autovacuum. No
> MultiXact references existing is a common case which shouldn't be
> assumed as the worst case. The comment I quoted means "the oldest
> value of the offset referenced by any multi-xact referenced by a
> relation *may not be always known". You seem to have interpreted "may
> not be known" as "does not exist" That's not right. I would write this
> as "Returns NULL if the oldest referenced offset is unknown which
> happens during system startup".
>
> Similarly I would rephrase the following docs as
> + <para>
> + The function returns <literal>NULL</literal> when multixact
> statistics are unavailable.
> + For example, during startup before multixact initialization completes or when
> + the oldest member offset cannot be determined.
>
> "The function returns <literal>NULL</literal> when multixact
> statistics when the oldest multixact offset corresponding to a
> multixact referenced by a relation is not known after starting the
> system."
>
Updated.
> > >
> > > @@ -0,0 +1,127 @@
> > > +# High-signal invariants for pg_get_multixact_stats()
>
> What does "High-signal" mean here? Is that term defined somewhere?
> Using terms that most of the contributors are familiar with improves
> readability. If a new term is required, it needs to be defined first.
> But I doubt something here requires defining a new term.
Dropped that wording and simplified the isolation test.
> > > What's a driver transaction?
> > A driver transaction is simply the controlling session that stays open
> > while snapshots are taken.
>
> I still don't understand the purpose of this transaction.
> pg_get_multixact_stats() isn't transactional so the driver transaction
> isn't holding any "snapshot" of the stats. It's also not creating any
> multixact and hence does not contribute to testing the output of
> pg_get_multixact_stats. Whatever this session is doing, can be done
> outside a transaction too. Which step in this session requires an
> outer transaction?
Removed this mention; the test now only checks monotonicity without extra
transaction scaffolding.
> Some more comments
> + Returns statistics about current multixact usage:
> + <literal>num_mxids</literal> is the number of multixact IDs assigned,
>
> Is this the number of multixact IDs assigned till now (since whatever
> time) or the number of multixact IDs currently in the system?
>
> + <literal>num_members</literal> is the number of multixact member
> entries created,
Updated.
> + Returns statistics about current multixact usage:
> + <literal>num_mxids</literal> is the number of multixact IDs assigned,
>
> Is this the number of multixact IDs assigned till now (since whatever
> time) or the number of multixact IDs currently in the system?
>
> + <literal>num_members</literal> is the number of multixact member
> entries created,
Updated.
> + multixact allocation and usage patterns in real time. For example:
>
> suggestion: ... real time, for example: ... Otherwise the sentence
> started by "For example" is not a complete sentence.
Updated.
> + values[0] = Int32GetDatum(multixacts);
>
> This should be UInt32GetDatum() multixacts is uint32.
>
> + values[1] = Int64GetDatum(members);
>
> Similarly this since MultiXactOffset is uint32.
>
> + values[4] = Int64GetDatum(oldestOffset);
>
> Similarly this since MultiXactOffset is uint32.
Thanks for pointing this out. I had originally followed the existing
types but drifted, fixed now.
> +# Get MultiXact state
> +{
> + oid => '9001',
> + descr => 'get current multixact member and multixact ID counts and
> oldest values',
>
> suggestion: get current multixact usage statistics.
Updated
> + proname => 'pg_get_multixact_stats',
> + prorettype => 'record',
> + proargtypes => '',
> + proallargtypes => '{int4,int8,int8,xid,int8}',
> + proargmodes => '{o,o,o,o,o}',
> + proargnames =>
> '{num_mxids,num_members,members_size,oldest_multixact,oldest_offset}',
> + provolatile => 'v',
> + proparallel => 's',
> + prosrc => 'pg_get_multixact_stats'
> +},
>
> I like the way you have formatted the new entry, but other entries in
> this file are not formatted this way. It would be good to format it
> like other entries but if other reviewers prefer this way, we can go
> with this too.
I reformatted the pg_proc.dat entry to match the surrounding style.
Best regards,
Naga
Attachments:
[application/octet-stream] v8-0001-Add-pg_get_multixact_stats-function-for-monitorin.patch (18.5K, 2-v8-0001-Add-pg_get_multixact_stats-function-for-monitorin.patch)
download | inline diff:
From 4752fdd586f78da4ca68879f5a8dafdbfbb36445 Mon Sep 17 00:00:00 2001
From: Naga Appani <[email protected]>
Date: Thu, 11 Sep 2025 22:18:51 +0000
Subject: [PATCH v8] Add pg_get_multixact_stats() function for monitoring
MultiXact usage
Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids : number of MultiXact IDs in use
- num_members : number of member entries in use
- members_size : bytes used by num_members in pg_multixact/members directory
- oldest_multixact : oldest MultiXact ID still needed
- oldest_offset : oldest member offset still in use
This patch adds pg_get_multixact_stats() function
- SQL-callable interface to GetMultiXactInfo()
- Returns NULLs if MultiXact system not initialized
- Includes isolation tests for monitoring invariants
Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage
Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry
Author: Naga Appani <[email protected]>
Reviewed-by: Ashutosh Bapat <[email protected]>
Reviewed-by: Michael Paquier <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 35 ++++++
doc/src/sgml/maintenance.sgml | 58 ++++++++-
src/backend/utils/adt/multixactfuncs.c | 47 +++++++
src/include/catalog/pg_proc.dat | 10 ++
.../isolation/expected/multixact_stats.out | 92 ++++++++++++++
src/test/isolation/isolation_schedule | 1 +
src/test/isolation/specs/multixact_stats.spec | 119 ++++++++++++++++++
7 files changed, 356 insertions(+), 6 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/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..2d21d0d3af5 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,41 @@ 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>num_mxids</parameter> <type>integer</type>,
+ <parameter>num_members</parameter> <type>bigint</type>,
+ <parameter>members_size</parameter> <type>bigint</type>,
+ <parameter>oldest_multixact</parameter> <type>xid</type>,
+ <parameter>oldest_offset</parameter> <type>bigint</type> )
+ </para>
+ <para>
+ Returns statistics about current multixact usage:
+ <literal>num_mxids</literal> is the total number of multixact IDs assigned since startup,
+ <literal>num_members</literal> is the total number of multixact member entries created since startup,
+ <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+ in the <literal>pg_multixact/members</literal> directory,
+ <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+ <literal>oldest_offset</literal> is the oldest member offset still in use.
+ See <xref linkend="vacuum-for-multixact-wraparound"/> for further details on multixact wraparound.
+ </para>
+ <para>
+ The function reports statistics at the time it is invoked. Values may vary between calls,
+ even within a single transaction.
+ </para>
+ <para>
+ Returns <literal>NULL</literal> when multixact statistics are unavailable,
+ such as during startup before multixact initialization completes.
+ Specifically, this occurs when the oldest multixact offset
+ corresponding to a multixact referenced by a relation is not known.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..58be621182b 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,60 @@ 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
- scans will occur more often for all tables, starting with those that
- 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.
+ linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+ of multixact member entries created exceeds approximately 2^31 entries
+ (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+ 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
+ scans will occur even if autovacuum is nominally disabled. The members can grow
+ up to approximately 2^32 entries(occupying roughly 20GB in the
+ <literal>pg_multixact/members</literal> directory) before reaching wraparound.
</para>
+ <para>
+ The <function>pg_get_multixact_stats()</function> function described in
+ <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+ multixact allocation and usage patterns in real time, for example:
+ <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | oldest_offset | members_size_pretty
+-----------+-------------+--------------+------------------+---------------+---------------------
+ 311740299 | 2785241176 | 13926205880 | 2 | 3 | 13 GB
+(1 row)
+ </programlisting>
+ This output shows a system with significant multixact activity: about ~312 million
+ multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+ By leveraging this information, the function helps:
+ <orderedlist>
+ <listitem>
+ <simpara>
+ Identify unusual multixact activity from concurrent row-level locks
+ or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+ multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+ concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+ causing lock contention.
+ </simpara>
+ </listitem>
+ <listitem>
+ <simpara>
+ Track multixact cleanup efficiency by monitoring oldest_multixact.
+ If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+ that long-running transactions are preventing cleanup, or autovacuum is
+ not keeping up with the workload.
+ </simpara>
+ </listitem>
+ <listitem>
+ <simpara>
+ Detect potential performance impacts before they become critical.
+ For instance, high multixact usage from frequent row-level locking or
+ foreign key operations can lead to increased I/O and CPU overhead during
+ vacuum operations. Monitoring these stats helps tune autovacuum frequency
+ and transaction patterns.
+ </simpara>
+ </listitem>
+ </orderedlist>
+ </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/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..3117acb19fa 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -85,3 +85,50 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funccxt);
}
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown, which happens during
+ * system startup.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+ TupleDesc tupdesc;
+ Datum values[5];
+ bool nulls[5];
+ MultiXactOffset members;
+ MultiXactId oldestMultiXactId;
+ uint32 multixacts;
+ MultiXactOffset oldestOffset;
+ int64 membersBytes;
+
+ if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("return type must be a row type")));
+
+ if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+ {
+ /*
+ * Calculate storage space for members. Members are stored in groups of 4,
+ * with each group taking 20 bytes, resulting in 5 bytes per member.
+ * Note: This ignores small page overhead (12 bytes per 8KB)
+ */
+ membersBytes = (int64) members * 5;
+
+ values[0] = UInt32GetDatum(multixacts);
+ values[1] = UInt32GetDatum(members);
+ values[2] = Int64GetDatum(membersBytes);
+ values[3] = UInt32GetDatum(oldestMultiXactId);
+ values[4] = UInt32GetDatum(oldestOffset);
+ memset(nulls, false, sizeof(nulls));
+
+ return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+ }
+
+ PG_RETURN_NULL();
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 03e82d28c87..e3bdb187da0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12588,4 +12588,14 @@
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' },
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+ proname => 'pg_get_multixact_stats',
+ provolatile => 'v', proparallel => 's', prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{int4,int8,int8,xid,int8}',
+ proargmodes => '{o,o,o,o,o}',
+ proargnames => '{num_mxids,num_members,members_size,oldest_multixact,oldest_offset}',
+ 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..69845f058e4
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,92 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0:
+ CREATE TEMP TABLE snap0 AS
+ SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+ FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+ 1
+(1 row)
+
+step snap1:
+ CREATE TEMP TABLE snap1 AS
+ SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+ FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+ 1
+(1 row)
+
+step snap2:
+ CREATE TEMP TABLE snap2 AS
+ SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+ FROM pg_get_multixact_stats();
+
+step check_while_pinned:
+ SELECT r.assertion, r.ok
+ FROM snap0 s0
+ JOIN snap1 s1 ON TRUE
+ JOIN snap2 s2 ON TRUE,
+ LATERAL unnest(
+ ARRAY[
+ 'is_init_mxids',
+ 'is_init_members',
+ 'is_init_oldest_mxid',
+ 'is_init_oldest_off',
+ 'is_oldest_mxid_nondec_01',
+ 'is_oldest_mxid_nondec_12',
+ 'is_oldest_off_nondec_01',
+ 'is_oldest_off_nondec_12',
+ 'is_members_increased_ge1',
+ 'is_mxids_nondec_01',
+ 'is_mxids_nondec_12',
+ 'is_members_nondec_01',
+ 'is_members_nondec_12'
+ ],
+ ARRAY[
+ (s2.num_mxids IS NOT NULL),
+ (s2.num_members IS NOT NULL),
+ (s2.oldest_multixact IS NOT NULL),
+ (s2.oldest_offset IS NOT NULL),
+
+ (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+ (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+ (s1.oldest_offset >= COALESCE(s0.oldest_offset, 0)),
+ (s2.oldest_offset >= COALESCE(s1.oldest_offset, 0)),
+
+ (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+ (s1.num_mxids >= COALESCE(s0.num_mxids, 0)),
+ (s2.num_mxids >= COALESCE(s1.num_mxids, 0)),
+ (s1.num_members >= COALESCE(s0.num_members, 0)),
+ (s2.num_members >= COALESCE(s1.num_members, 0))
+ ]
+ ) AS r(assertion, ok);
+
+assertion |ok
+------------------------+--
+is_init_mxids |t
+is_init_members |t
+is_init_oldest_mxid |t
+is_init_oldest_off |t
+is_oldest_mxid_nondec_01|t
+is_oldest_mxid_nondec_12|t
+is_oldest_off_nondec_01 |t
+is_oldest_off_nondec_12 |t
+is_members_increased_ge1|t
+is_mxids_nondec_01 |t
+is_mxids_nondec_12 |t
+is_members_nondec_01 |t
+is_members_nondec_12 |t
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5afae33d370..bab8a8eaf31 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -120,3 +120,4 @@ test: serializable-parallel-2
test: serializable-parallel-3
test: matview-write-skew
test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..af6b091248a
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,119 @@
+# Test invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+# • members increased by ≥ 1 when the second locker arrived,
+# • num_mxids / num_members did not decrease vs earlier snapshots,
+# • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+#
+# Terminology (global counters):
+# num_mxids, num_members : "in-use" deltas derived from global horizons
+# oldest_multixact, offset : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can't advance past our pinned multi).
+
+setup
+{
+ CREATE TABLE mxq(id int PRIMARY KEY, v int);
+ INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+ DROP TABLE mxq;
+}
+
+# Two lockers on the SAME tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin { BEGIN; }
+step s1_lock { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin { BEGIN; }
+step s2_lock { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+ CREATE TEMP TABLE snap0 AS
+ SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+ FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row.
+step snap1 {
+ CREATE TEMP TABLE snap1 AS
+ SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+ FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members.
+step snap2 {
+ CREATE TEMP TABLE snap2 AS
+ SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+ FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+# is_init_mxids : num_mxids is non-NULL
+# is_init_members : num_members is non-NULL
+# is_init_oldest_mxid : oldest_multixact is non-NULL
+# is_init_oldest_off : oldest_offset is non-NULL
+# is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+# is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+# is_oldest_off_nondec_01 : oldest_offset did not decrease (snap0→snap1)
+# is_oldest_off_nondec_12 : oldest_offset did not decrease (snap1→snap2)
+# is_members_increased_ge1 : members increased by at least 1 when s2 joined
+# is_mxids_nondec_01 : num_mxids did not decrease (snap0→snap1)
+# is_mxids_nondec_12 : num_mxids did not decrease (snap1→snap2)
+# is_members_nondec_01 : num_members did not decrease (snap0→snap1)
+# is_members_nondec_12 : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+ SELECT r.assertion, r.ok
+ FROM snap0 s0
+ JOIN snap1 s1 ON TRUE
+ JOIN snap2 s2 ON TRUE,
+ LATERAL unnest(
+ ARRAY[
+ 'is_init_mxids',
+ 'is_init_members',
+ 'is_init_oldest_mxid',
+ 'is_init_oldest_off',
+ 'is_oldest_mxid_nondec_01',
+ 'is_oldest_mxid_nondec_12',
+ 'is_oldest_off_nondec_01',
+ 'is_oldest_off_nondec_12',
+ 'is_members_increased_ge1',
+ 'is_mxids_nondec_01',
+ 'is_mxids_nondec_12',
+ 'is_members_nondec_01',
+ 'is_members_nondec_12'
+ ],
+ ARRAY[
+ (s2.num_mxids IS NOT NULL),
+ (s2.num_members IS NOT NULL),
+ (s2.oldest_multixact IS NOT NULL),
+ (s2.oldest_offset IS NOT NULL),
+
+ (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+ (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+ (s1.oldest_offset >= COALESCE(s0.oldest_offset, 0)),
+ (s2.oldest_offset >= COALESCE(s1.oldest_offset, 0)),
+
+ (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+ (s1.num_mxids >= COALESCE(s0.num_mxids, 0)),
+ (s2.num_mxids >= COALESCE(s1.num_mxids, 0)),
+ (s1.num_members >= COALESCE(s0.num_members, 0)),
+ (s2.num_members >= COALESCE(s1.num_members, 0))
+ ]
+ ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
--
2.47.3
view thread (42+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring
In-Reply-To: <CA+QeY+A2B7mjWc8kkieNS_nxNyTeZpTzi4Ue+vtHzkmfFxO-8Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox