[PG19] pg_upgrade from <= 18 loses the newest multixid if its members end at offset wraparound
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
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
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)
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
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