v2 addresses the comments.
Adds a new section called "Multixact Information Functions" and a reference
to pg_get_multixact_members after the description of what multixact members
are in maintenance.sgml.
As I spent some time looking into this, I still think we should document this
function because of its use in blogs and examples that describe multixact.
However, this function does not appear to be very practical to use, because
the only visible MXID to the user is the oldest one, via pg_database.datminmxid
or pg_class.relfrozenxid, or with the help of the contrib extension pgrowlocks.
To actually find all multixact IDs and their associated XIDs that are yet to
be vacuumed, I could write a query like the following:
SELECT
b as mxid,
a.*
FROM
(SELECT min(datminmxid::text::int) min_datminmxid FROM pg_database) d,
generate_series(d.min_datminmxid, mxid_age(d.min_datminmxid::text::xid)) as b,
pg_get_multixact_members(b::text::xid) a
Another thing is in [0], we mention two things:
Running transactions and prepared transactions can be ignored if there is
no chance that they might appear in a multixact.
MXID information is not directly visible in system views such as
pg_stat_activity; however, looking for old XIDs is still a good way of
determining which transactions are causing MXID wraparound problems.
I really think this is an area that needs improvement. We tell users to ignore
transactions that have "no chance" of appearing in a multixact, but what that
really means is the user must somehow figure this out on their own. I don't
think it would be unrealistic to expose real-time information about backends
with transactions involved in multixacts.
Looking at a recent public discussion about multixact [1], it lists
"lack of direct visibility" as one of the areas for improvement. This
will be the
subject of a different thread, but I thought it would be good to mention it
here first as it's relevant.
[0] https://www.postgresql.org/docs/current/routine-vacuuming.html
[1] https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025
--
Sami Imseih
Amazon Web Services (AWS)
