MIME-Version: 1.0
References: 
 <CA+QeY+AAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg@mail.gmail.com>
 <CA+QeY+CuTcKSFmw3H-_OtCv+bv0T4uTSFgaAY1N9ssL-WaiiSA@mail.gmail.com>
 <CALdSSPi3Gh08NtcCn44uVeUAYGOT74sU6uei_06qUTa5rMK43g@mail.gmail.com>
 <CA+QeY+DTggHskCXOa39nag2sFds9BD-7k__zPbvL-_VVyJw7Sg@mail.gmail.com>
 <CAExHW5uxBnodmpXygUQUxZAyxYvbXczadxGOVeL=0DxpkXUR-g@mail.gmail.com>
 <aIb67MiYhVy7gwV-@paquier.xyz>
In-Reply-To: <aIb67MiYhVy7gwV-@paquier.xyz>
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Mon, 28 Jul 2025 11:30:08 +0530
Message-ID: 
 <CAExHW5vba_ecL2c27giAOOaRxoNdVX2BNQVCN4PFOZJC17NrqQ@mail.gmail.com>
Subject: Re: [Proposal] Expose internal MultiXact member count function for
 efficient monitoring
To: Michael Paquier <michael@paquier.xyz>, Naga Appani <nagnrik@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>, pgsql-hackers@postgresql.org
Content-Type: multipart/mixed; boundary="000000000000cb5e53063af7013c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAExHW5vba_ecL2c27giAOOaRxoNdVX2BNQVCN4PFOZJC17NrqQ%40mail.gmail.com>
Precedence: bulk

--000000000000cb5e53063af7013c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jul 28, 2025 at 9:52=E2=80=AFAM Michael Paquier <michael@paquier.xy=
z> wrote:
>
> May I also suggest a split of the multixact SQL functions into a
> separate file, a src/backend/utils/adt/multixactfuncs.c?  The existing
> pg_get_multixact_members() relies on GetMultiXactIdMembers(),
> available in multixact.h.  The new function pg_get_multixact_count()
> relies on ReadMultiXactCounts(), which would mean adding it in
> multixact.h.  Even if we finish without an agreement about the SQL
> function and the end, publishing ReadMultiXactCounts() would give an
> access to the internals to external code.
>
> +PG_FUNCTION_INFO_V1(pg_get_multixact_count);
>
> There should be no need for that, pg_proc.dat handling the
> declaration AFAIK.
>
> FWIW, these functions are always kind of hard to use for the end-user
> without proper documentation.  You may want to add an example of how
> one can use it for monitoring in the docs.

+1.

Let's say if the user knows that the counts are so high that a
wraparound is imminent, but vacuuming isn't solving the problem, they
would like to know which transactions are holding it back.
pg_get_multixact_members() can be used to get the members of the
oldest multixact if it's reported and then the user can deal with
those transactions. However, the oldest multixact is not reported
anywhere, AFAIK. It's also part of MultiXactState, so can be extracted
via ReadMultiXactCounts(). We could report it through
pg_get_multixact_counts - after renaming it and ReadMultiXactCounts to
pg_get_multixact_stats() and ReadMultiXactStats() respectively. Or we
could write another function to do so. But it comes handy using query
like below
#select oldestmultixact,
pg_get_multixact_members(oldestmultixact::text::xid) from
pg_get_multixact_count();
 oldestmultixact | pg_get_multixact_members
------------------+--------------------------
                1 | (757,sh)
                1 | (768,sh)
(2 rows)

Here's a quick patch implementing the same. Please feel free to
incorporate and refine it in your patch if you like it.

--=20
Best Wishes,
Ashutosh Bapat

--000000000000cb5e53063af7013c
Content-Type: text/plain; charset="US-ASCII";
 name="oldest_multixact.patch.txt"
Content-Disposition: attachment; filename="oldest_multixact.patch.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_mdmp6jt30>
X-Attachment-Id: f_mdmp6jt30

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL211bHRpeGFjdC5jIGIvc3Jj
L2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0vbXVsdGl4YWN0LmMKaW5kZXggNmIzZjM4YTJmZDYuLjhi
MzFiNTcxNDBkIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9hY2Nlc3MvdHJhbnNhbS9tdWx0aXhh
Y3QuYworKysgYi9zcmMvYmFja2VuZC9hY2Nlc3MvdHJhbnNhbS9tdWx0aXhhY3QuYwpAQCAtMjg2
MywxNyArMjg2MywxNiBAQCBmaW5kX211bHRpeGFjdF9zdGFydChNdWx0aVhhY3RJZCBtdWx0aSwg
TXVsdGlYYWN0T2Zmc2V0ICpyZXN1bHQpCiAgKiBleGlzdC4gIFJldHVybiBmYWxzZSBpZiB1bmFi
bGUgdG8gZGV0ZXJtaW5lLgogICovCiBzdGF0aWMgYm9vbAotUmVhZE11bHRpWGFjdENvdW50cyh1
aW50MzIgKm11bHRpeGFjdHMsIE11bHRpWGFjdE9mZnNldCAqbWVtYmVycykKK1JlYWRNdWx0aVhh
Y3RDb3VudHModWludDMyICptdWx0aXhhY3RzLCBNdWx0aVhhY3RPZmZzZXQgKm1lbWJlcnMsIE11
bHRpWGFjdElkICpvbGRlc3RNdWx0aVhhY3RJZCkKIHsKIAlNdWx0aVhhY3RPZmZzZXQgbmV4dE9m
ZnNldDsKIAlNdWx0aVhhY3RPZmZzZXQgb2xkZXN0T2Zmc2V0OwotCU11bHRpWGFjdElkIG9sZGVz
dE11bHRpWGFjdElkOwogCU11bHRpWGFjdElkIG5leHRNdWx0aVhhY3RJZDsKIAlib29sCQlvbGRl
c3RPZmZzZXRLbm93bjsKIAogCUxXTG9ja0FjcXVpcmUoTXVsdGlYYWN0R2VuTG9jaywgTFdfU0hB
UkVEKTsKIAluZXh0T2Zmc2V0ID0gTXVsdGlYYWN0U3RhdGUtPm5leHRPZmZzZXQ7Ci0Jb2xkZXN0
TXVsdGlYYWN0SWQgPSBNdWx0aVhhY3RTdGF0ZS0+b2xkZXN0TXVsdGlYYWN0SWQ7CisJKm9sZGVz
dE11bHRpWGFjdElkID0gTXVsdGlYYWN0U3RhdGUtPm9sZGVzdE11bHRpWGFjdElkOwogCW5leHRN
dWx0aVhhY3RJZCA9IE11bHRpWGFjdFN0YXRlLT5uZXh0TVhhY3Q7CiAJb2xkZXN0T2Zmc2V0ID0g
TXVsdGlYYWN0U3RhdGUtPm9sZGVzdE9mZnNldDsKIAlvbGRlc3RPZmZzZXRLbm93biA9IE11bHRp
WGFjdFN0YXRlLT5vbGRlc3RPZmZzZXRLbm93bjsKQEAgLTI4ODMsNyArMjg4Miw3IEBAIFJlYWRN
dWx0aVhhY3RDb3VudHModWludDMyICptdWx0aXhhY3RzLCBNdWx0aVhhY3RPZmZzZXQgKm1lbWJl
cnMpCiAJCXJldHVybiBmYWxzZTsKIAogCSptZW1iZXJzID0gbmV4dE9mZnNldCAtIG9sZGVzdE9m
ZnNldDsKLQkqbXVsdGl4YWN0cyA9IG5leHRNdWx0aVhhY3RJZCAtIG9sZGVzdE11bHRpWGFjdElk
OworCSptdWx0aXhhY3RzID0gbmV4dE11bHRpWGFjdElkIC0gKm9sZGVzdE11bHRpWGFjdElkOwog
CXJldHVybiB0cnVlOwogfQogCkBAIC0yOTIyLDkgKzI5MjEsMTAgQEAgTXVsdGlYYWN0TWVtYmVy
RnJlZXplVGhyZXNob2xkKHZvaWQpCiAJdWludDMyCQl2aWN0aW1fbXVsdGl4YWN0czsKIAlkb3Vi
bGUJCWZyYWN0aW9uOwogCWludAkJCXJlc3VsdDsKKwlNdWx0aVhhY3RJZCBvbGRlc3RNdWx0aVhh
Y3RJZDsKIAogCS8qIElmIHdlIGNhbid0IGRldGVybWluZSBtZW1iZXIgc3BhY2UgdXRpbGl6YXRp
b24sIGFzc3VtZSB0aGUgd29yc3QuICovCi0JaWYgKCFSZWFkTXVsdGlYYWN0Q291bnRzKCZtdWx0
aXhhY3RzLCAmbWVtYmVycykpCisJaWYgKCFSZWFkTXVsdGlYYWN0Q291bnRzKCZtdWx0aXhhY3Rz
LCAmbWVtYmVycywgJm9sZGVzdE11bHRpWGFjdElkKSkKIAkJcmV0dXJuIDA7CiAKIAkvKiBJZiBt
ZW1iZXIgc3BhY2UgdXRpbGl6YXRpb24gaXMgbG93LCBubyBzcGVjaWFsIGFjdGlvbiBpcyByZXF1
aXJlZC4gKi8KQEAgLTM1MDMsMjIgKzM1MDMsMjQgQEAgRGF0dW0KIHBnX2dldF9tdWx0aXhhY3Rf
Y291bnQoUEdfRlVOQ1RJT05fQVJHUykKIHsKIAlUdXBsZURlc2MJCXR1cGRlc2M7Ci0JRGF0dW0J
CQl2YWx1ZXNbMl07Ci0JYm9vbAkJCW51bGxzWzJdID0ge2ZhbHNlLCBmYWxzZX07CisJRGF0dW0J
CQl2YWx1ZXNbM107CisJYm9vbAkJCW51bGxzWzNdID0ge2ZhbHNlLCBmYWxzZSwgZmFsc2V9Owog
CU11bHRpWGFjdE9mZnNldAltZW1iZXJzOwogCXVpbnQzMgkJCW11bHRpeGFjdHM7CiAJSGVhcFR1
cGxlCQl0dXBsZTsKKwlNdWx0aVhhY3RJZCBvbGRlc3RNdWx0aVhhY3RJZDsKIAogCWlmIChnZXRf
Y2FsbF9yZXN1bHRfdHlwZShmY2luZm8sIE5VTEwsICZ0dXBkZXNjKSAhPSBUWVBFRlVOQ19DT01Q
T1NJVEUpCiAJCWVyZXBvcnQoRVJST1IsCiAJCQkJKGVycm1zZygicmV0dXJuIHR5cGUgbXVzdCBi
ZSBhIHJvdyB0eXBlIikpKTsKIAotCWlmICghUmVhZE11bHRpWGFjdENvdW50cygmbXVsdGl4YWN0
cywgJm1lbWJlcnMpKQorCWlmICghUmVhZE11bHRpWGFjdENvdW50cygmbXVsdGl4YWN0cywgJm1l
bWJlcnMsICZvbGRlc3RNdWx0aVhhY3RJZCkpCiAJCWVyZXBvcnQoRVJST1IsCiAJCQkJKGVycm1z
ZygiY291bGQgbm90IHJlYWQgbXVsdGl4YWN0IGNvdW50cyIpKSk7CiAKIAl2YWx1ZXNbMF0gPSBV
SW50MzJHZXREYXR1bShtdWx0aXhhY3RzKTsKIAl2YWx1ZXNbMV0gPSBVSW50MzJHZXREYXR1bSht
ZW1iZXJzKTsKKwl2YWx1ZXNbMl0gPSBVSW50MzJHZXREYXR1bShvbGRlc3RNdWx0aVhhY3RJZCk7
CiAKIAl0dXBsZSA9IGhlYXBfZm9ybV90dXBsZSh0dXBkZXNjLCB2YWx1ZXMsIG51bGxzKTsKIAlQ
R19SRVRVUk5fREFUVU0oSGVhcFR1cGxlR2V0RGF0dW0odHVwbGUpKTsKZGlmZiAtLWdpdCBhL3Ny
Yy9pbmNsdWRlL2NhdGFsb2cvcGdfcHJvYy5kYXQgYi9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3By
b2MuZGF0CmluZGV4IDBlNTExZGQzNDczLi4yYmUwY2QzNWUxMiAxMDA2NDQKLS0tIGEvc3JjL2lu
Y2x1ZGUvY2F0YWxvZy9wZ19wcm9jLmRhdAorKysgYi9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3By
b2MuZGF0CkBAIC0xMjU3OSwxMyArMTI1NzksMTMgQEAKICMgUmV0dXJucyBjdXJyZW50IGNvdW50
cyBvZiBtdWx0aXhhY3QgbWVtYmVycyBhbmQgbXVsdGl4YWN0IElEcwogewogICBvaWQgPT4gJzkw
MDEnLAotICBkZXNjciA9PiAnZ2V0IGN1cnJlbnQgbXVsdGl4YWN0IG1lbWJlciBhbmQgbXVsdGl4
YWN0IElEIGNvdW50cycsCisgIGRlc2NyID0+ICdnZXQgY3VycmVudCBtdWx0aXhhY3QgbWVtYmVy
IGFuZCBtdWx0aXhhY3QgSUQgY291bnRzIGFuZCBvbGRlc3QgbXVsdGl4YWN0JywKICAgcHJvbmFt
ZSA9PiAncGdfZ2V0X211bHRpeGFjdF9jb3VudCcsCiAgIHByb3JldHR5cGUgPT4gJ3JlY29yZCcs
CiAgIHByb2FyZ3R5cGVzID0+ICcnLAotICBwcm9hbGxhcmd0eXBlcyA9PiAne2ludDQsaW50OH0n
LAotICBwcm9hcmdtb2RlcyA9PiAne28sb30nLAotICBwcm9hcmduYW1lcyA9PiAne211bHRpeGFj
dHMsbWVtYmVyc30nLAorICBwcm9hbGxhcmd0eXBlcyA9PiAne2ludDQsaW50OCxpbnQ0fScsCisg
IHByb2FyZ21vZGVzID0+ICd7byxvLG99JywKKyAgcHJvYXJnbmFtZXMgPT4gJ3ttdWx0aXhhY3Rz
LG1lbWJlcnMsb2xkZXN0bXVsdGl4YWN0fScsCiAgIHByb3ZvbGF0aWxlID0+ICd2JywKICAgcHJv
cGFyYWxsZWwgPT4gJ3MnLAogICBwcm9zcmMgPT4gJ3BnX2dldF9tdWx0aXhhY3RfY291bnQnCg==
--000000000000cb5e53063af7013c--