BUG #18865: pg_resetwal error: multitransaction offset (-O) must not be -1 - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18865
Logged by:          Dmitry Kovalenko
Email address:      d.kovalenko@postgrespro.ru
PostgreSQL version: 17.4
Operating system:   Ubuntu 2024.04
Description:

Hello,

A problem of database cluster migration, where next-multixact-offset became
4294967295 (-1), is found.

I created a test where database has NextMultiOffset equal 4294967295 (-1).

pg_upgrade can't process the such database because pg_resetwal returns the
error "multitransaction offset (-O) must not be -1".   

---
Test project (python):

https://github.com/dmitry-lipetsk/PostgresTests/tree/main/Heap001/PostgreSQL/Set001/D20250325_001--next_multixact_offsets_is_minus_one/Iter000

Source code of test:

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

An archive has the result of test
test_006__multixact_offsets[next_4294967293-citems_2-cmxids_1] execution:

https://dropmefiles.com/JzmZ8 (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/D20250325_001--next_multixact_offsets_is_minus_one

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_4294967293-citems_2-cmxids_1"

---
Short scenario

- In empty (new) cluster set NextMultiOffset equal 4294967293 with
pg_resetwal
- Create a multitransaction that is based on two normal transactions
3) Run pg_upgrade
4) Get the pg_resetwal error "multitransaction offset (-O) must not be -1"

---
Full scenario

0) Build Postgres from master tree (PG18)

1) Create the new cluster - source_cluster

2) Execute the command: 
pg_resetwal -O 4294967293 -D <path_to_source_cluster>

3) Create files in  in pg_multixact/members folder:
bash -c "dd if=/dev/zero
of=<path_to_source_cluster>/pg_multixact/members/14078 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:  2
- Latest checkpoint's NextMultiOffset:  4294967295
- Latest checkpoint's oldestMultiXid:   1

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
------------------
...
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          
*failure*
...
Consult the last few lines of
".../target/data/pg_upgrade_output.d/20250325T101628.822/log/pg_upgrade_utility.log"
for the probable cause of the failure.
Failure, exiting

--
pg_upgrade_utility.log has the following information:

command: ".../pg_resetwal" -O 4294967295 -m 2,1 ".../target/data" >>
".../target/data/pg_upgrade_output.d/20250325T101628.822/log/pg_upgrade_utility.log"
2>&1
pg_resetwal: error: multitransaction offset (-O) must not be -1

Regards,
Dmitry Kovalenko
Postgres Professional