pg_get_multixact_members not documented
Hi,
While looking into another multixact related topic, I realised that
pg_get_multixact_members
is not documented. I saw the lack of documentation was mentioned here [0]/messages/by-id/20150619215231.GT133018@postgresql.org, but
this was never committed.
Any reason it should not be?
[0]: /messages/by-id/20150619215231.GT133018@postgresql.org
--
Sami Imseih
Amazon Web Services (AWS)
On Fri, May 30, 2025 at 02:23:30PM -0500, Sami Imseih wrote:
While looking into another multixact related topic, I realised that
pg_get_multixact_members
is not documented. I saw the lack of documentation was mentioned here [0], but
this was never committed.Any reason it should not be?
It looks to me like �lvaro just never got any feedback on the last patch he
sent. I noticed that there are no in-tree uses, but I do see a couple of
blog posts that recommend it. In any case, I can't think of a reason it
ought to remain undocumented. Want to put together a patch?
--
nathan
Thanks!
blog posts that recommend it. In any case, I can't think of a reason it
ought to remain undocumented.
I agree, especially with blogs referencing it.
Want to put together a patch?
Yes, will do
—
Sami
On Fri, May 30, 2025 at 04:24:43PM -0500, Sami Imseih wrote:
Want to put together a patch?
Yes, will do
For extra credit, maybe we could add a test or two, too...
--
nathan
Want to put together a patch?
Yes, will do
v1-0001 is the documentation only patch. I improved upon the description
suggested in [0]/messages/by-id/20150619215231.GT133018@postgresql.org
For extra credit, maybe we could add a test or two, too...
I can add tests, even though we don't really have system function specific
testing.
A simple test will be a regress/sql which ensure the XID and lock mode
of a transaction using a savepoint, something like the below. To do anything
fancier with concurrency, we will need an isolation test.
```
drop table if exists t;
create table t (v int); insert into t values (1);
begin;
select from t for update ;
savepoint s1;
update t set v = v;
select pg_get_multixact_members(a.relminmxid), a.relminmxid from
(select relminmxid from pg_class where relname = 't') a;
commit;
```
Thoughts?
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
v1-0001-Document-pg_get_multixact_members.patchapplication/octet-stream; name=v1-0001-Document-pg_get_multixact_members.patchDownload
From 0d7708ae3c21b444186fa02f0b5fc00b812a825d Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Mon, 2 Jun 2025 12:37:52 -0500
Subject: [PATCH v1 1/1] Document pg_get_multixact_members
pg_get_multixact_members has widespread usage, yet lacks official
documentation in the system functions section. There was a desire
to document in the past but it appears to have slipped due to a
lack of reviews.
Discussion: https://www.postgresql.org/message-id/flat/CAA5RZ0seQ2KyoSxUQ80OS9f8Yphe1Fu-6cSDkVG1so4ko90Q_A%40mail.gmail.com#3458b5b0c79d44b420a469c7309ea77b
---
doc/src/sgml/func.sgml | 15 +++++++++++++++
1 file changed, 15 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f9..7bcb6e9731c3 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27677,6 +27677,21 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
details.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_multixact_members</primary>
+ </indexterm>
+ <function>pg_get_multixact_members</function> ( <type>xid</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>xid</parameter> <type>xid</type>,
+ <parameter>mode</parameter> <type>text</type> )
+ </para>
+ <para>
+ Returns the transaction ID and lock mode for each member of a given multixact ID.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
--
2.39.5 (Apple Git-154)
On Mon, Jun 02, 2025 at 12:46:51PM -0500, Sami Imseih wrote:
v1-0001 is the documentation only patch. I improved upon the description
suggested in [0]
Your patch adds an entry to the "Transaction ID and Snapshot Information
Functions" table, while �lvaro's introduced a new "Multixact Functions"
table. His also added a note to maintenance.sgml. Any reason for the
differences?
A simple test will be a regress/sql which ensure the XID and lock mode
of a transaction using a savepoint, something like the below. To do anything
fancier with concurrency, we will need an isolation test.```
drop table if exists t;
create table t (v int); insert into t values (1);
begin;
select from t for update ;
savepoint s1;
update t set v = v;
select pg_get_multixact_members(a.relminmxid), a.relminmxid from
(select relminmxid from pg_class where relname = 't') a;
commit;
```
That seems reasonable to me.
--
nathan
--
Sami Imseih
Amazon Web Services (AWS)
On Mon, Jun 2, 2025 at 3:03 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:
On Mon, Jun 02, 2025 at 12:46:51PM -0500, Sami Imseih wrote:
v1-0001 is the documentation only patch. I improved upon the description
suggested in [0]Your patch adds an entry to the "Transaction ID and Snapshot Information
Functions" table, while Álvaro's introduced a new "Multixact Functions"
table. His also added a note to maintenance.sgml. Any reason for the
differences?
My initial thoughts were to add a new section,
but was not sure if it made sense since there
will only be a single function. Well, that’s not a good
reason and we may add some more in the future.
Also, the “Transaction ID and Snapshot Function” section is
probably the wrong section because the mxid’s/xid’s may
not have been committed. So I will correct. I will also add
a reference In maintenance.sgml
—
Sami
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]https://www.postgresql.org/docs/current/routine-vacuuming.html, 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]https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025, 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)
Attachments:
v2-0001-Document-pg_get_multixact_members.patchapplication/octet-stream; name=v2-0001-Document-pg_get_multixact_members.patchDownload
From 90201f9d4e1368499679baae862bcdf33f93ee6e Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Wed, 4 Jun 2025 14:33:38 -0500
Subject: [PATCH v2 1/1] Document pg_get_multixact_members
pg_get_multixact_members has widespread usage, yet lacks official
documentation in the system functions section. There was a desire
to document in the past but it appears to have slipped due to a
lack of reviews.
Discussion: https://www.postgresql.org/message-id/flat/CAA5RZ0seQ2KyoSxUQ80OS9f8Yphe1Fu-6cSDkVG1so4ko90Q_A%40mail.gmail.com#3458b5b0c79d44b420a469c7309ea77b
---
doc/src/sgml/func.sgml | 43 +++++++++++++++++++++++++++++++++++
doc/src/sgml/maintenance.sgml | 4 +++-
2 files changed, 46 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f9..5c6a8b3b3db4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28496,6 +28496,49 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-info-multixact">
+ <title>Multixact Information Functions</title>
+
+ <para>
+ The functions shown in <xref linkend="functions-multixact"/>
+ provide information about about current Multixact usage.
+ </para>
+
+ <table id="functions-multixact">
+ <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_members</primary>
+ </indexterm>
+ <function>pg_get_multixact_members</function> ( <type>xid</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>xid</parameter> <type>xid</type>,
+ <parameter>mode</parameter> <type>text</type> )
+ </para>
+ <para>
+ Returns the transaction ID and lock mode for each member of a given multixact ID.
+ </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 600e4b3f2f3b..a21b42a6bd35 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -779,7 +779,9 @@ HINT: Execute a database-wide VACUUM in that database.
careful aging management, storage cleanup, and wraparound handling.
There is a separate storage area which holds the list of members in
each multixact, which also uses a 32-bit counter and which must also
- be managed.
+ be managed. The system function <function>pg_get_multixact_members</function>
+ described in <xref linkend="functions-info-multixact"/> can be used to
+ examine the transaction IDs associated with a multixact ID.
</para>
<para>
--
2.39.5 (Apple Git-154)
On Thu, Jun 5, 2025 at 2:11 AM Sami Imseih <samimseih@gmail.com> wrote:
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.
+1. PFA diff of some edits. Please incorporate them in your patch if you
find them correct.
The table description from Alvaro's patch is better. The change is included
in the diff.
We developers may understand the mode text "sh", "keysh" etc. But it may
not be user friendly. It would have been better if the function would
have reported standard modes as described in [1]https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES -- Best Wishes, Ashutosh Bapat but we can't change that
now. It might help if we add mapping in the documentation.
"" ... each member of the multixact identified by the given mxid' sounds
more accurate compared to " ... each member of a given multixact ID". That
document page seems to differentiate between a transaction and transaction
ID. I think we should try to do the same for 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.
If somebody starts from a row itself e.g. select xmax, * from t1 ... , this
function gives the transactions that have locked a given row. So not that
useless.
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.
+1. Multixacts are highly under documented in user facing as well as
internal documentations. Thanks for the patch.
[1]: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES -- Best Wishes, Ashutosh Bapat
https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES
--
Best Wishes,
Ashutosh Bapat
Attachments:
diff.patch.txttext/plain; charset=US-ASCII; name=diff.patch.txtDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5c6a8b3b3db..831f878e463 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28501,7 +28501,7 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<para>
The functions shown in <xref linkend="functions-multixact"/>
- provide information about about current Multixact usage.
+ provide information about multixacts in the system.
</para>
<table id="functions-multixact">
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index a21b42a6bd3..94f75f3da8f 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -779,7 +779,7 @@ HINT: Execute a database-wide VACUUM in that database.
careful aging management, storage cleanup, and wraparound handling.
There is a separate storage area which holds the list of members in
each multixact, which also uses a 32-bit counter and which must also
- be managed. The system function <function>pg_get_multixact_members</function>
+ be managed. The system function <function>pg_get_multixact_members()</function>
described in <xref linkend="functions-info-multixact"/> can be used to
examine the transaction IDs associated with a multixact ID.
</para>
Thanks for the review!
+1. PFA diff of some edits. Please incorporate them in
your patch if you find them correct.
sure, the diff looks fine to me. will fix.
We developers may understand the mode text "sh", "keysh" etc. But it may not be user friendly.
yes, I can see that being a point of confusion.
It would have been better if the function would have reported standard modes as described in [1]
but we can't change that now.
Really, the section this relates to is row-level locks [0]https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS, and for
multixact, there are 2 additional
modes "nokeyupd" which refer to locks takes by updated in no key mode,
and "upd" which
are other types of updates and deletes.
It might help if we add mapping in the documentation.
I am not too thrilled with a new table mapping, because of the maintenance
aspect of keeping it up-to-date ( not that locks change often or ever
). I rather
describe that the modes are abbreviated names for locks referenced in
[1]: /messages/by-id/CAA5RZ0vJat3-VumsTGagxzY35T=rCSNyH9GXwkdB3-FFY4as8A@mail.gmail.com
there are also some additional modes specific to multixact.
"" ... each member of the multixact identified by the given mxid' sounds more
accurate compared to " ... each member of a given multixact ID". That document
page seems to differentiate between a transaction and transaction ID. I think we
should try to do the same for multixact.
I like my wording more. It fits the style of the way we describe
functions better. Also,
we don't use "mxid" in the docs, but do use "Transaction ID" and "multixact ID"
I did change " for each " to "of each", which is more accurate.
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.If somebody starts from a row itself e.g. select xmax, * from t1 ... ,
this function gives the transactions that have locked a given row. So not that useless.
I did not mean "useless", but I did say "not practical", as it
requires a bit of understanding
of the multixact system and multixact ID generation to use. The
example usage you mention
and the example I mentioned earlier [1]/messages/by-id/CAA5RZ0vJat3-VumsTGagxzY35T=rCSNyH9GXwkdB3-FFY4as8A@mail.gmail.com demonstrates this.
[0]: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS
[1]: /messages/by-id/CAA5RZ0vJat3-VumsTGagxzY35T=rCSNyH9GXwkdB3-FFY4as8A@mail.gmail.com
See v3 addressing the comments.
--
Sami
Attachments:
v3-0001-Document-pg_get_multixact_members.patchapplication/octet-stream; name=v3-0001-Document-pg_get_multixact_members.patchDownload
From 5129e28c268e2f4088b6d6d4b2c304c7f76ff7ca Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Wed, 4 Jun 2025 14:33:38 -0500
Subject: [PATCH v3 1/1] Document pg_get_multixact_members
pg_get_multixact_members has widespread usage, yet lacks official
documentation in the system functions section. There was a desire
to document in the past but it appears to have slipped due to a
lack of reviews.
Discussion: https://www.postgresql.org/message-id/flat/CAA5RZ0seQ2KyoSxUQ80OS9f8Yphe1Fu-6cSDkVG1so4ko90Q_A%40mail.gmail.com#3458b5b0c79d44b420a469c7309ea77b
---
doc/src/sgml/func.sgml | 48 +++++++++++++++++++++++++++++++++++
doc/src/sgml/maintenance.sgml | 4 ++-
2 files changed, 51 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..00f32d6ce3b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28496,6 +28496,54 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-info-multixact">
+ <title>Multixact Information Functions</title>
+
+ <para>
+ The functions shown in <xref linkend="functions-multixact"/>
+ provide information about multixacts in the system.
+ </para>
+
+ <table id="functions-multixact">
+ <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_members</primary>
+ </indexterm>
+ <function>pg_get_multixact_members</function> ( <type>xid</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>xid</parameter> <type>xid</type>,
+ <parameter>mode</parameter> <type>text</type> )
+ </para>
+ <para>
+ Returns the transaction ID and lock mode for each member of the specified
+ multixact ID. The lock modes are abbreviated names for the types of row-level
+ locks described in <xref linkend="locking-rows"/>. Two additional modes are
+ specific to multixacts: <literal>nokeyupd</literal>, used for updates that
+ do not modify key columns, and <literal>upd</literal>, used for updates or
+ deletes that do.
+ </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..94f75f3da8f 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -779,7 +779,9 @@ HINT: Execute a database-wide VACUUM in that database.
careful aging management, storage cleanup, and wraparound handling.
There is a separate storage area which holds the list of members in
each multixact, which also uses a 32-bit counter and which must also
- be managed.
+ be managed. The system function <function>pg_get_multixact_members()</function>
+ described in <xref linkend="functions-info-multixact"/> can be used to
+ examine the transaction IDs associated with a multixact ID.
</para>
<para>
--
2.43.0
On Fri, Jun 6, 2025 at 4:15 AM Sami Imseih <samimseih@gmail.com> wrote:
We developers may understand the mode text "sh", "keysh" etc. But it may
not be user friendly.
yes, I can see that being a point of confusion.
It would have been better if the function would have reported standard
modes as described in [1]
but we can't change that now.
Really, the section this relates to is row-level locks [0], and for
multixact, there are 2 additional
modes "nokeyupd" which refer to locks takes by updated in no key mode,
and "upd" which
are other types of updates and deletes.
It might help if we add mapping in the documentation.
I am not too thrilled with a new table mapping, because of the maintenance
aspect of keeping it up-to-date ( not that locks change often or ever
). I rather
describe that the modes are abbreviated names for locks referenced in
[1] and that
there are also some additional modes specific to multixact.
+ .... The lock modes are abbreviated names for the types of
row-level
+ locks described in <xref linkend="locking-rows"/>.It's not clear, without some effort, which lock mode go with which row lock
in that description. For example, "keysh" does not have "for" in it but
"fornokeyupd" does. How about rephrasing this as "The lock modes "keysh",
"sh", fornokeyupd", and "forupd" correspond to <literal>FOR KEY
SHARE</literal>, <literal>FOR SHARE</literal>, <literal>FOR NO KEY
UPDATE</literal>, and <literal>FOR UPDATE</literal> row level locks
respectively as described in <xref linkend="locking-rows"/>."
It will still need an update if the row lock modes are changed in the
future, but that risk goes with the two multixact specific modes as well.
And the risk is minimal.
Do we want to add the section "Multixact Information Functions" after other
transaction related sections like "Transaction ID and Snapshot Information
Function" and " Committed Transaction Information Functions" instead of
adding it at the end?
--
Best Wishes,
Ashutosh Bapat
It's not clear, without some effort, which lock mode go with which row lock in that description.
For example, "keysh" does not have "for" in it but "fornokeyupd" does. How about rephrasing this
as "The lock modes "keysh", "sh", fornokeyupd", and "forupd" correspond to
<literal>FOR KEY SHARE</literal>, <literal>FOR SHARE</literal>, <literal>FOR NO KEY UPDATE</literal>,
and <literal>FOR UPDATE</literal> row level locks respectively as described in <xref linkend="locking-rows"/>."
That works for me.
Do we want to add the section "Multixact Information Functions" after other transaction related
sections like "Transaction ID and Snapshot Information Function" and
" Committed Transaction Information Functions" instead of adding it at the end?
makes sense.
See v4 which addresses the points above.
--
Sami
Attachments:
v4-0001-Document-pg_get_multixact_members.patchapplication/octet-stream; name=v4-0001-Document-pg_get_multixact_members.patchDownload
From 39f1f0f4f749d8b07a8987fa9b9986ec1b17172d Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Wed, 4 Jun 2025 14:33:38 -0500
Subject: [PATCH v4 1/1] Document pg_get_multixact_members
pg_get_multixact_members has widespread usage, yet lacks official
documentation in the system functions section. There was a desire
to document in the past but it appears to have slipped due to a
lack of reviews.
Discussion: https://www.postgresql.org/message-id/flat/CAA5RZ0seQ2KyoSxUQ80OS9f8Yphe1Fu-6cSDkVG1so4ko90Q_A%40mail.gmail.com#3458b5b0c79d44b420a469c7309ea77b
---
doc/src/sgml/func.sgml | 51 +++++++++++++++++++++++++++++++++++
doc/src/sgml/maintenance.sgml | 4 ++-
2 files changed, 54 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f9..c5bae54c6708 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27955,6 +27955,57 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-info-multixact">
+ <title>Multixact Information Functions</title>
+
+ <para>
+ The functions shown in <xref linkend="functions-multixact"/>
+ provide information about multixacts in the system.
+ </para>
+
+ <table id="functions-multixact">
+ <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_members</primary>
+ </indexterm>
+ <function>pg_get_multixact_members</function> ( <type>xid</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>xid</parameter> <type>xid</type>,
+ <parameter>mode</parameter> <type>text</type> )
+ </para>
+ <para>
+ Returns the transaction ID and lock mode for each member of the specified
+ multixact ID. The lock modes <literal>forupd</literal>, <literal>fornokeyupd</literal>,
+ <literal>sh</literal>, and <literal>keysh</literal> correspond to
+ the row-level locks <literal>FOR UPDATE</literal>, <literal>FOR NO KEY UPDATE</literal>,
+ <literal>FOR SHARE</literal>, and <literal>FOR KEY SHARE</literal>, respectively,
+ as described in <xref linkend="locking-rows"/>. Two additional modes are specific to
+ multixacts: <literal>nokeyupd</literal>, used by updates that do not modify key
+ columns, and <literal>upd</literal>, used by updates or deletes that modify key
+ columns.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
<sect2 id="functions-info-controldata">
<title>Control Data Functions</title>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 600e4b3f2f3b..94f75f3da8fe 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -779,7 +779,9 @@ HINT: Execute a database-wide VACUUM in that database.
careful aging management, storage cleanup, and wraparound handling.
There is a separate storage area which holds the list of members in
each multixact, which also uses a 32-bit counter and which must also
- be managed.
+ be managed. The system function <function>pg_get_multixact_members()</function>
+ described in <xref linkend="functions-info-multixact"/> can be used to
+ examine the transaction IDs associated with a multixact ID.
</para>
<para>
--
2.39.5 (Apple Git-154)
On Wed, Jun 11, 2025 at 9:44 PM Sami Imseih <samimseih@gmail.com> wrote:
It's not clear, without some effort, which lock mode go with which row
lock in that description.
For example, "keysh" does not have "for" in it but "fornokeyupd" does.
How about rephrasing this
as "The lock modes "keysh", "sh", fornokeyupd", and "forupd" correspond
to
<literal>FOR KEY SHARE</literal>, <literal>FOR SHARE</literal>,
<literal>FOR NO KEY UPDATE</literal>,
and <literal>FOR UPDATE</literal> row level locks respectively as
described in <xref linkend="locking-rows"/>."
That works for me.
Do we want to add the section "Multixact Information Functions" after
other transaction related
sections like "Transaction ID and Snapshot Information Function" and
" Committed Transaction Information Functions" instead of adding it atthe end?
makes sense.
Attached patch removing extra comma. Otherwise LGTM.
Please add the patch to CF, if not already done and mark it as "ready for
committer".
--
Best Wishes,
Ashutosh Bapat
Attachments:
0001-Document-pg_get_multixact_members-20250612.patchtext/x-patch; charset=US-ASCII; name=0001-Document-pg_get_multixact_members-20250612.patchDownload
From dd240a151a5a17f2872d4df0a353e03c73f2daf2 Mon Sep 17 00:00:00 2001
From: Sami Imseih <simseih@amazon.com>
Date: Wed, 4 Jun 2025 14:33:38 -0500
Subject: [PATCH] Document pg_get_multixact_members
pg_get_multixact_members has widespread usage, yet lacks official
documentation in the system functions section. There was a desire
to document in the past but it appears to have slipped due to a
lack of reviews.
Discussion: https://www.postgresql.org/message-id/flat/CAA5RZ0seQ2KyoSxUQ80OS9f8Yphe1Fu-6cSDkVG1so4ko90Q_A%40mail.gmail.com#3458b5b0c79d44b420a469c7309ea77b
---
doc/src/sgml/func.sgml | 51 +++++++++++++++++++++++++++++++++++
doc/src/sgml/maintenance.sgml | 4 ++-
2 files changed, 54 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..5af113cfc84 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27955,6 +27955,57 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-info-multixact">
+ <title>Multixact Information Functions</title>
+
+ <para>
+ The functions shown in <xref linkend="functions-multixact"/>
+ provide information about multixacts in the system.
+ </para>
+
+ <table id="functions-multixact">
+ <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_members</primary>
+ </indexterm>
+ <function>pg_get_multixact_members</function> ( <type>xid</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>xid</parameter> <type>xid</type>,
+ <parameter>mode</parameter> <type>text</type> )
+ </para>
+ <para>
+ Returns the transaction ID and lock mode for each member of the specified
+ multixact ID. The lock modes <literal>forupd</literal>, <literal>fornokeyupd</literal>,
+ <literal>sh</literal>, and <literal>keysh</literal> correspond to
+ the row-level locks <literal>FOR UPDATE</literal>, <literal>FOR NO KEY UPDATE</literal>,
+ <literal>FOR SHARE</literal> and <literal>FOR KEY SHARE</literal>, respectively,
+ as described in <xref linkend="locking-rows"/>. Two additional modes are specific to
+ multixacts: <literal>nokeyupd</literal>, used by updates that do not modify key
+ columns, and <literal>upd</literal>, used by updates or deletes that modify key
+ columns.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
<sect2 id="functions-info-controldata">
<title>Control Data Functions</title>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 600e4b3f2f3..94f75f3da8f 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -779,7 +779,9 @@ HINT: Execute a database-wide VACUUM in that database.
careful aging management, storage cleanup, and wraparound handling.
There is a separate storage area which holds the list of members in
each multixact, which also uses a 32-bit counter and which must also
- be managed.
+ be managed. The system function <function>pg_get_multixact_members()</function>
+ described in <xref linkend="functions-info-multixact"/> can be used to
+ examine the transaction IDs associated with a multixact ID.
</para>
<para>
base-commit: f85f6ab051b7cf6950247e5fa6072c4130613555
--
2.34.1
Attached patch removing extra comma. Otherwise LGTM.
Thanks! For v4, the final comma in the list is grammatically correct,
and it’s the style we use throughout the docs.
I marked the patch RFC.
--
Sami
On Thu, Jun 12, 2025 at 12:15:03AM -0500, Sami Imseih wrote:
Attached patch removing extra comma. Otherwise LGTM.
Thanks! For v4, the final comma in the list is grammatically correct,
and it�s the style we use throughout the docs.
+1
I marked the patch RFC.
Barring comments/objections, I'll plan on committing/back-patching this in
the near future.
--
nathan
On Thu, Jun 12, 2025 at 03:10:56PM -0500, Nathan Bossart wrote:
Barring comments/objections, I'll plan on committing/back-patching this in
the near future.
Here is what I have staged for commit. I ended up moving it to the
"Transaction ID and Snapshot Information Functions" table, which is what I
think you had originally proposed. Creating a new section for this seemed
unnecessary, and this table already has one multixact-related function.
--
nathan
Attachments:
v6-0001-Document-pg_get_multixact_members.patchtext/plain; charset=iso-8859-1Download
From a65eb067ff48d8ea1f4b12e437b0a00d0dac54a2 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 27 Jun 2025 14:33:30 -0500
Subject: [PATCH v6 1/1] Document pg_get_multixact_members().
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Oversight in commit 0ac5ad5134.
Author: Sami Imseih <samimseih@gmail.com>
Co-authored-by: Ãlvaro Herrera <alvherre@kurilemu.de>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Discussion: https://postgr.es/m/20150619215231.GT133018%40postgresql.org
Discussion: https://postgr.es/m/CAA5RZ0sjQDDwJfMRb%3DZ13nDLuRpF13ME2L_BdGxi0op8RKjmDg%40mail.gmail.com
Backpatch-through: 13
---
doc/src/sgml/func.sgml | 25 +++++++++++++++++++++++++
doc/src/sgml/maintenance.sgml | 5 ++++-
2 files changed, 29 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 224d4fe5a9f..d2968a2d175 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27687,6 +27687,31 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
details.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_multixact_members</primary>
+ </indexterm>
+ <function>pg_get_multixact_members</function> ( <parameter>multixid</parameter> <type>xid</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>xid</parameter> <type>xid</type>,
+ <parameter>mode</parameter> <type>text</type> )
+ </para>
+ <para>
+ Returns the transaction ID and lock mode for each member of the
+ specified multixact ID. The lock modes <literal>forupd</literal>,
+ <literal>fornokeyupd</literal>, <literal>sh</literal>, and
+ <literal>keysh</literal> correspond to the row-level locks
+ <literal>FOR UPDATE</literal>, <literal>FOR NO KEY UPDATE</literal>,
+ <literal>FOR SHARE</literal>, and <literal>FOR KEY SHARE</literal>,
+ respectively, as described in <xref linkend="locking-rows"/>. Two
+ additional modes are specific to multixacts:
+ <literal>nokeyupd</literal>, used by updates that do not modify key
+ columns, and <literal>upd</literal>, used by updates or deletes that
+ modify key columns.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 600e4b3f2f3..e7a9f58c015 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -779,7 +779,10 @@ HINT: Execute a database-wide VACUUM in that database.
careful aging management, storage cleanup, and wraparound handling.
There is a separate storage area which holds the list of members in
each multixact, which also uses a 32-bit counter and which must also
- be managed.
+ be managed. The system function
+ <function>pg_get_multixact_members()</function> described in
+ <xref linkend="functions-pg-snapshot"/> can be used to examine the
+ transaction IDs associated with a multixact ID.
</para>
<para>
--
2.39.5 (Apple Git-154)
On Sat, Jun 28, 2025 at 4:02 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Thu, Jun 12, 2025 at 03:10:56PM -0500, Nathan Bossart wrote:
Barring comments/objections, I'll plan on committing/back-patching this in
the near future.Here is what I have staged for commit. I ended up moving it to the
"Transaction ID and Snapshot Information Functions" table, which is what I
think you had originally proposed. Creating a new section for this seemed
unnecessary, and this table already has one multixact-related function.
WFM.
--
Best Wishes,
Ashutosh Bapat
On Mon, Jun 30, 2025 at 7:29 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
On Sat, Jun 28, 2025 at 4:02 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Thu, Jun 12, 2025 at 03:10:56PM -0500, Nathan Bossart wrote:
Barring comments/objections, I'll plan on committing/back-patching this in
the near future.Here is what I have staged for commit. I ended up moving it to the
"Transaction ID and Snapshot Information Functions" table, which is what I
think you had originally proposed. Creating a new section for this seemed
unnecessary, and this table already has one multixact-related function.WFM.
Correct, I originally proposed the "Transaction ID and Snapshot
Information Functions"
section, but as stated in [0]/messages/by-id/CAA5RZ0vtSPzx+8HW1gohcWPgURX2VST5j3Nqh2OL6h9dSk0HSg@mail.gmail.com,
pg_get_multixact_members does not fit the description of the section as it
is described as "The main use of these functions is to determine
which transactions
were committed between two snapshots."
IMO, it makes more sense to keep this function in a new section as we
have in v4.
[0]: /messages/by-id/CAA5RZ0vtSPzx+8HW1gohcWPgURX2VST5j3Nqh2OL6h9dSk0HSg@mail.gmail.com
--
Sami
On Mon, Jun 30, 2025 at 04:08:04PM +0300, Sami Imseih wrote:
Correct, I originally proposed the "Transaction ID and Snapshot
Information Functions" section, but as stated in [0],
pg_get_multixact_members does not fit the description of the section as
it is described as "The main use of these functions is to determine
which transactions were committed between two snapshots."
Well, is that description correct? I've used several of these functions,
but I'm not sure I've ever used them for this purpose.
Looking again, pg_get_multixact_members() might need to be added to this
list of exceptions:
However, the functions shown in Table 9.84, except age and mxid_age,
use a 64-bit type xid8 that does not wrap around during the life of an
installation and can be converted to xid by casting if required; see
Section 67.1 for details.
I'm also a little wary of adding a new section on the back-branches because
it will change some section numbers.
--
nathan
On Mon, Jun 30, 2025 at 04:08:04PM +0300, Sami Imseih wrote:
Correct, I originally proposed the "Transaction ID and Snapshot
Information Functions" section, but as stated in [0],
pg_get_multixact_members does not fit the description of the section as
it is described as "The main use of these functions is to determine
which transactions were committed between two snapshots."Well, is that description correct? I've used several of these functions,
but I'm not sure I've ever used them for this purpose.
I suppose the description is not applicable to all of these functions;
for example
pg_current_xact_id_if_assigned() returns the current transactions xid, so the
description does not apply. Neither does it apply to pg_snapshot_xip().
this description has been around since 8.3 from what I can tell, and
when the inventory
of functions in this section was much smaller and most required a snapshot as an
argument.
Perhaps we should think about removing this description, what do you think?
Looking again, pg_get_multixact_members() might need to be added to this
list of exceptions:However, the functions shown in Table 9.84, except age and mxid_age,
use a 64-bit type xid8 that does not wrap around during the life of an
installation and can be converted to xid by casting if required; see
Section 67.1 for details.
This function returns an xid and not an int8 such as for example
```
{ oid => '3819', descr => 'view members of a multixactid',
proname => 'pg_get_multixact_members', prorows => '1000', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => 'xid',
proallargtypes => '{xid,xid,text}', proargmodes => '{i,o,o}',
proargnames => '{multixid,xid,mode}', prosrc => 'pg_get_multixact_members' },
```
```
{ oid => '2943', descr => 'get current transaction ID',
proname => 'txid_current', provolatile => 's', proparallel => 'u',
prorettype => 'int8', proargtypes => '', prosrc => 'pg_current_xact_id' },
```
am I missing something?
I'm also a little wary of adding a new section on the back-branches because
it will change some section numbers.
Sure, I am not against keeping the function in an existing section, but
we should remove the description mentioned above for clarity.
--
Sami
Sure, I am not against keeping the function in an existing section, but
we should remove the description mentioned above for clarity.
to be clear, I am suggesting we just remove the second sentence
in the description. Therefore, instead of:
"The functions shown in Table 9.84 provide server transaction information
in an exportable form. The main use of these functions is to determine which
transactions were committed between two snapshots."
mention only:
"The functions shown in Table 9.84 provide server transaction
information in an exportable form."
I don't think we need to add anything else.
--
Sami
On Mon, Jun 30, 2025 at 06:19:10PM +0300, Sami Imseih wrote:
Perhaps we should think about removing this description, what do you think?
I think it's a good topic for another patch/thread. Chances are it's not
the only description that could be updated.
Looking again, pg_get_multixact_members() might need to be added to this
list of exceptions:However, the functions shown in Table 9.84, except age and mxid_age,
use a 64-bit type xid8 that does not wrap around during the life of an
installation and can be converted to xid by casting if required; see
Section 67.1 for details.This function returns an xid and not an int8 such as for example
```
{ oid => '3819', descr => 'view members of a multixactid',
proname => 'pg_get_multixact_members', prorows => '1000', proretset => 't',
provolatile => 'v', prorettype => 'record', proargtypes => 'xid',
proallargtypes => '{xid,xid,text}', proargmodes => '{i,o,o}',
proargnames => '{multixid,xid,mode}', prosrc => 'pg_get_multixact_members' },
```
```
{ oid => '2943', descr => 'get current transaction ID',
proname => 'txid_current', provolatile => 's', proparallel => 'u',
prorettype => 'int8', proargtypes => '', prosrc => 'pg_current_xact_id' },
```
am I missing something?
That's what I mean. I think it should say
However, the functions shown in Table 9.84, except age, mxid_age, and
pg_get_multixact_members, use a 64-bit type xid8 that...
I noticed that this list of exceptions doesn't exist on v13-v15, presumably
because the docs for age() and mxid_age() were only back-patched to v16
(see commits 48b5aa3 and 15afb7d), which is strange because I believe those
functions are much older. I don't see any discussion about this choice,
either. We should probably back-patch those commits to v13 as a
prerequisite to applying this patch.
--
nathan
On Mon, Jun 30, 2025 at 02:54:28PM -0500, Nathan Bossart wrote:
I noticed that this list of exceptions doesn't exist on v13-v15, presumably
because the docs for age() and mxid_age() were only back-patched to v16
(see commits 48b5aa3 and 15afb7d), which is strange because I believe those
functions are much older. I don't see any discussion about this choice,
either. We should probably back-patch those commits to v13 as a
prerequisite to applying this patch.
Spun off a new thread for this:
/messages/by-id/aGMCxHxLfeMdQk8m@nathan
--
nathan
Perhaps we should think about removing this description, what do you think?
I think it's a good topic for another patch/thread. Chances are it's not
the only description that could be updated.
Sure, that could be taken up after this patch.
That's what I mean. I think it should say
However, the functions shown in Table 9.84, except age, mxid_age, and
pg_get_multixact_members, use a 64-bit type xid8 that...
I agree. v7 includes this change.
--
Sami
Attachments:
v7-0001-Document-pg_get_multixact_members.patchapplication/octet-stream; name=v7-0001-Document-pg_get_multixact_members.patchDownload
From f249d6b93c51e5cfb8de5b63e73768631d2bd15c Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 27 Jun 2025 14:33:30 -0500
Subject: [PATCH v7 1/1] Document pg_get_multixact_members().
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Oversight in commit 0ac5ad5134.
Author: Sami Imseih <samimseih@gmail.com>
Co-authored-by: Álvaro Herrera <alvherre@kurilemu.de>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Discussion: https://postgr.es/m/20150619215231.GT133018%40postgresql.org
Discussion: https://postgr.es/m/CAA5RZ0sjQDDwJfMRb%3DZ13nDLuRpF13ME2L_BdGxi0op8RKjmDg%40mail.gmail.com
Backpatch-through: 13
---
doc/src/sgml/func.sgml | 29 +++++++++++++++++++++++++++--
doc/src/sgml/maintenance.sgml | 5 ++++-
2 files changed, 31 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 126b8cfbad8..350acacb3e6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27704,6 +27704,31 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
details.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_multixact_members</primary>
+ </indexterm>
+ <function>pg_get_multixact_members</function> ( <parameter>multixid</parameter> <type>xid</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>xid</parameter> <type>xid</type>,
+ <parameter>mode</parameter> <type>text</type> )
+ </para>
+ <para>
+ Returns the transaction ID and lock mode for each member of the
+ specified multixact ID. The lock modes <literal>forupd</literal>,
+ <literal>fornokeyupd</literal>, <literal>sh</literal>, and
+ <literal>keysh</literal> correspond to the row-level locks
+ <literal>FOR UPDATE</literal>, <literal>FOR NO KEY UPDATE</literal>,
+ <literal>FOR SHARE</literal>, and <literal>FOR KEY SHARE</literal>,
+ respectively, as described in <xref linkend="locking-rows"/>. Two
+ additional modes are specific to multixacts:
+ <literal>nokeyupd</literal>, used by updates that do not modify key
+ columns, and <literal>upd</literal>, used by updates or deletes that
+ modify key columns.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -27712,8 +27737,8 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
The internal transaction ID type <type>xid</type> is 32 bits wide and
wraps around every 4 billion transactions. However,
the functions shown in <xref linkend="functions-pg-snapshot"/>, except
- <function>age</function> and <function>mxid_age</function>, use a
- 64-bit type <type>xid8</type> that does not wrap around during the life
+ <function>age</function>, <function>mxid_age</function> and <function>pg_get_multixact_members</function>,
+ use a 64-bit type <type>xid8</type> that does not wrap around during the life
of an installation and can be converted to <type>xid</type> by casting if
required; see <xref linkend="transaction-id"/> for details.
The data type <type>pg_snapshot</type> stores information about
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 600e4b3f2f3..e7a9f58c015 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -779,7 +779,10 @@ HINT: Execute a database-wide VACUUM in that database.
careful aging management, storage cleanup, and wraparound handling.
There is a separate storage area which holds the list of members in
each multixact, which also uses a 32-bit counter and which must also
- be managed.
+ be managed. The system function
+ <function>pg_get_multixact_members()</function> described in
+ <xref linkend="functions-pg-snapshot"/> can be used to examine the
+ transaction IDs associated with a multixact ID.
</para>
<para>
--
2.43.0