From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: d.kovalenko@postgrespro.ru
Subject: BUG #18863: Multixact wraparound and pg_resetwal error "multitransaction ID (-m) must not be 0"
Date: Mon, 24 Mar 2025 18:35:07 +0000
Message-ID: <18863-72f08858855344a2@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18863
Logged by: Dmitry Kovalenko
Email address: d.kovalenko@postgrespro.ru
PostgreSQL version: 17.4
Operating system: Ubuntu 2024.04
Description:
Hello,
A problem of a database cluster migration, where a multixact wraparound was
occurred, is found.
My test creates a cluster where NextMultiXactId becomes equal 0 and run
pg_upgrade.
pg_upgrade fails because pg_resetwal returns the following error:
"multitransaction ID (-m) must not be 0".
---
Test project (python):
https://github.com/dmitry-lipetsk/PostgresTests/tree/main/Heap001/PostgreSQL/Set001/D20250324_001--m...
Source code of test:
https://github.com/dmitry-lipetsk/PostgresTests/blob/main/Heap001/PostgreSQL/Set001/D20250324_001--m...
An archive contains the result of test
"test_004__multixacts[next_4294967295-old_4294967295-cmxids_1]" execution:
https://dropmefiles.com/Y1CpC (this link is valid within two weeks)
001-bad
- it is a result with orignal postgres (master, PG18)
002-ok
- it is a result with modified postgres:
https://github.com/dmitry-lipetsk/postgres/tree/D20250324_001--multixact_and_wraparound
(I tried to fix this problem, of course it is a research only.)
You can recreate these results:
export TEST_CFG__OLD_BIN_DIR="path_to_pg_bin_dir"
export TEST_CFG__NEW_BIN_DIR="path_to_pg_bin_dir"
pytest -l -v -k "next_4294967295-old_4294967295-cmxids_1"
---
Short scenario
1) Set NextMultiXactId and oldestMultiXid equal to 4294967295
2) Create 1 multitransaction
3) Run pg_upgrade
4) Get the error "pg_resetwal: multitransaction ID (-m) must not be 0"
---
Full scenario
0) Build Postgres from master tree (PG18)
1) Create the new cluster - source_cluster
2) Execute the command:
pg_resetwal -m 4294967295,4294967295 -D <path_to_source_cluster>
3) Create files in pg_multixact/offsets folder:
bash -c "dd if=/dev/zero
of=<path_to_source_cluster>/pg_multixact/offsets/FFFF bs=262144 count=1"
4) Create the test table:
CREATE TABLE test_table (id integer NOT NULL PRIMARY KEY, val text);
INSERT INTO test_table VALUES (1, 'a');
5) Create two connection - CN1 и CN2
6) Create a multitransaction:
CN1: BEGIN; select * from test_table where id = 1 for share;
CN2: BEGIN; select * from test_table where id = 1 for share;
7) Commit transactions in CN1 and CN2:
CN1: COMMIT;
CN2: COMMIT;
8) Close connections CN1 and CN2
ControlData of source_cluster says:
- Latest checkpoint's NextMultiXactId: 0
- Latest checkpoint's NextMultiOffset: 3
- Latest checkpoint's oldestMultiXid: 4294967295
9) Create the new cluster - target_cluster
10) Run the command
pg_upgrade --old-datadir <path_to_source_cluster> --new-datadir
<path_to_target_cluster> ....
11) Get the migration error:
Performing Upgrade
------------------
...
Setting next multixact ID and offset for new cluster
*failure*
Consult the last few lines of
"..../target/data/pg_upgrade_output.d/20250324T170921.154/log/pg_upgrade_utility.log"
for the probable cause of the failure.
--
pg_upgrade_utility.log has the following information:
command: ".../pg_resetwal" -O 3 -m 0,4294967295 ".../target/data" >>
".../target/data/pg_upgrade_output.d/20250324T170921.154/log/pg_upgrade_utility.log"
2>&1
pg_resetwal: error: multitransaction ID (-m) must not be 0
Regards,
Dmitry Kovalenko,
Postgres Professional
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: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, d.kovalenko@postgrespro.ru
Subject: Re: BUG #18863: Multixact wraparound and pg_resetwal error "multitransaction ID (-m) must not be 0"
In-Reply-To: <18863-72f08858855344a2@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox