[PG19] pg_upgrade from <= 18 loses the newest multixid if its members end at offset wraparound

Started by Zsolt Parragiabout 1 month ago5 messageshackers
Jump to latest
#1Zsolt Parragi
zsolt.parragi@percona.com

Hello

I/Claude found another corner case in the multixact offset conversion,
not covered by the issues in Noah's thread [1]/messages/by-id/20260827231757.78.noahmisch@microsoft.com.

In 18 and below if the members of the newest multixid end exactly at
2^32 the shutdown checkpoint stores NextMultiOffset = 0, and the
server handles this scenario properly.

But PG19 after the upgrade doesn't:

select count(*) from t;
ERROR: MultiXact 1 has invalid offset
vacuum freeze t;
ERROR: MultiXact 1 has invalid offset
CONTEXT: while scanning block 0 offset 1 of relation "public.t"

pg_upgrade --check doesn't notice anything.

Reproducing it on an 18 cluster:

pg_resetwal -O 4294967294 old
# create the zeroed members segment for that offset (14078)
create table t(id int primary key, v int); insert into t values (1,0);
-- session 1
begin; select 1 from t where id = 1 for key share;
-- session 2
update t set v = 1 where id = 1;
-- session 1
commit;

then stop the server and upgrade it.

This needs the last multixid before the upgrade to end exactly at the
boundary, so it is definitely a corner case.

The attached patch moves the zero check into the branch that reads the
next multixid's slot, matching what the server does. The existing
length calculation already handles the wrap, since both offsets are
uint32.

It also adds a scenario to 007_multixact_conversion.pl (only runs with
oldinstall set to a pre-19 installation, like the wraparound one).

[1]: /messages/by-id/20260827231757.78.noahmisch@microsoft.com

Attachments:

t254221_1
v1-0001-pg_upgrade-Fix-last-multixid-ending-at-offset-wra.patchapplication/octet-stream; name=v1-0001-pg_upgrade-Fix-last-multixid-ending-at-offset-wra.patchDownload+55-7
#2shihao zhong
zhong950419@gmail.com
In reply to: Zsolt Parragi (#1)
Re: [PG19] pg_upgrade from <= 18 loses the newest multixid if its members end at offset wraparound

Hi Zsolt,

This is rare and hard to hit. But 18 accepts this state, and the
damage only shows up after the upgrade.

Someone told me a story today. If you build a payroll app and it
breaks one time in x, would you ship it? That one time is someone
who does not get paid. A rare bug is still a real bug for whoever
hits it.

So I think it is worth fixing

The patch looks right to me

Thanks,
Shihao

#3Alvaro Herrera
alvherre@kurilemu.de
In reply to: shihao zhong (#2)
Re: [PG19] pg_upgrade from <= 18 loses the newest multixid if its members end at offset wraparound

On 2026-Oct-15, shihao zhong wrote:

Someone told me a story today. If you build a payroll app and it
breaks one time in x, would you ship it? That one time is someone
who does not get paid. A rare bug is still a real bug for whoever
hits it.

Some definitely would.
https://en.wikipedia.org/wiki/British_Post_Office_scandal

The court cases, criminal convictions, imprisonments, loss of
livelihoods and homes, debts, and bankruptcies led to stress, illness
and family breakdowns, and were linked to at least thirteen suicides.
In 2024, Prime Minister Rishi Sunak described the scandal as one of
the greatest miscarriages of justice in British history.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)

#4Heikki Linnakangas
hlinnaka@iki.fi
In reply to: Zsolt Parragi (#1)
Re: [PG19] pg_upgrade from <= 18 loses the newest multixid if its members end at offset wraparound

On 15/10/2026 22:27, Zsolt Parragi wrote:

I/Claude found another corner case in the multixact offset conversion,
not covered by the issues in Noah's thread [1].

In 18 and below if the members of the newest multixid end exactly at
2^32 the shutdown checkpoint stores NextMultiOffset = 0, and the
server handles this scenario properly.

But PG19 after the upgrade doesn't:

select count(*) from t;
ERROR: MultiXact 1 has invalid offset
vacuum freeze t;
ERROR: MultiXact 1 has invalid offset
CONTEXT: while scanning block 0 offset 1 of relation "public.t"

pg_upgrade --check doesn't notice anything.

Reproducing it on an 18 cluster:

pg_resetwal -O 4294967294 old
# create the zeroed members segment for that offset (14078)
create table t(id int primary key, v int); insert into t values (1,0);
-- session 1
begin; select 1 from t where id = 1 for key share;
-- session 2
update t set v = 1 where id = 1;
-- session 1
commit;

then stop the server and upgrade it.

This needs the last multixid before the upgrade to end exactly at the
boundary, so it is definitely a corner case.

The attached patch moves the zero check into the branch that reads the
next multixid's slot, matching what the server does. The existing
length calculation already handles the wrap, since both offsets are
uint32.

It also adds a scenario to 007_multixact_conversion.pl (only runs with
oldinstall set to a pre-19 installation, like the wraparound one).

Committed, thanks!

- Heikki

#5shihao zhong
zhong950419@gmail.com
In reply to: Heikki Linnakangas (#4)
Re: [PG19] pg_upgrade from <= 18 loses the newest multixid if its members end at offset wraparound

Hi,

In 2024, Prime Minister Rishi Sunak described the scandal as one of
the greatest miscarriages of justice in British history.

Well, that escalated quickly. I was going for "someone misses a
paycheck", and you raised me "thirteen suicides and a Prime Minister
apologizing in Parliament". 😓

On the bright side, this particular 1-in-2^32 bug will not be sending
anyone to prison.

Looks like Heikki committed the fix before I finished reading
the Wikipedia article. Thanks Zsolt for finding it and Heikki for the
quick commit.

Thanks,
Shihao