BUG #18863: Multixact wraparound and pg_resetwal error "multitransaction ID (-m) must not be 0" - Mailing list pgsql-bugs

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--multixact_and_wraparound/Iter000

Source code of test:

https://github.com/dmitry-lipetsk/PostgresTests/blob/main/Heap001/PostgreSQL/Set001/D20250324_001--multixact_and_wraparound/Iter000/src/tests/pg_upgrade/test_set001__MultiXacts.py

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