pgsql: Check that oldestXID and oldestMulti are consistent at pg_upgrad

Started by Heikki Linnakangas2 months ago2 messagescommitters
Jump to latest
#1Heikki Linnakangas
heikki.linnakangas@iki.fi

Check that oldestXID and oldestMulti are consistent at pg_upgrade

Now that pg_upgrade will rewrite multixid members, starting from
oldestMulti, it's important that oldestMulti is valid. Add a sanity
check that oldestMulti is not newer than the oldest datminmxid value
in pg_database.

One case where this could happen is if the cluster was previously
upgraded to version 9.3 with a buggy pg_upgrade version that didn't
have commit a61daa14d5. This new pg_upgrade check is similar to the
defence that was added in commit 78db307bb2 to VACUUM to avoid
truncating away multixids if oldestMulti is too new. This pg_upgrade
check differs in that we don't try to soldier on with the upgrade if
the oldestMultiXID is inconsistent, but rather just abort the upgrade.

Reported-by: Noah Misch <noah@leadboat.com>
Discussion: /messages/by-id/20260827231757.78.noahmisch@microsoft.com
Backpatch-through: 19

Branch
------
REL_19_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/5cd84ed6274ecd552f2991c54710608c0ef4cdc7

Modified Files
--------------
src/backend/access/transam/multixact.c | 28 --------------
src/bin/pg_upgrade/check.c | 71 ++++++++++++++++++++++++++++++++++
src/include/access/multixact.h | 31 +++++++++++++--
3 files changed, 99 insertions(+), 31 deletions(-)

#2Andrew Dunstan
andrew@dunslane.net
In reply to: Heikki Linnakangas (#1)
Re: pgsql: Check that oldestXID and oldestMulti are consistent at pg_upgrad

On 2026-09-18 Fr 5:32 PM, Heikki Linnakangas wrote:

Check that oldestXID and oldestMulti are consistent at pg_upgrade

Now that pg_upgrade will rewrite multixid members, starting from
oldestMulti, it's important that oldestMulti is valid. Add a sanity
check that oldestMulti is not newer than the oldest datminmxid value
in pg_database.

One case where this could happen is if the cluster was previously
upgraded to version 9.3 with a buggy pg_upgrade version that didn't
have commit a61daa14d5. This new pg_upgrade check is similar to the
defence that was added in commit 78db307bb2 to VACUUM to avoid
truncating away multixids if oldestMulti is too new. This pg_upgrade
check differs in that we don't try to soldier on with the upgrade if
the oldestMultiXID is inconsistent, but rather just abort the upgrade.

Reported-by: Noah Misch <noah@leadboat.com>
Discussion: /messages/by-id/20260827231757.78.noahmisch@microsoft.com
Backpatch-through: 19

Branch
------
REL_19_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/5cd84ed6274ecd552f2991c54710608c0ef4cdc7

Modified Files
--------------
src/backend/access/transam/multixact.c | 28 --------------
src/bin/pg_upgrade/check.c | 71 ++++++++++++++++++++++++++++++++++
src/include/access/multixact.h | 31 +++++++++++++--
3 files changed, 99 insertions(+), 31 deletions(-)

This has broken upgrade from release 9.2 still supposedly supported in
release 19 (but not 20, for which the minimum will be 10). The log
complains that datminmxid doesn't exist. Since we didn't have multixids
until 9.3 could we just skip this check if upgrading from 9.2?

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com