Re: pg15b2: large objects lost on upgrade

Peter Geoghegan <pg@bowt.ie>

From: Peter Geoghegan <pg@bowt.ie>

To: Tom Lane <tgl@sss.pgh.pa.us>

Cc: "Jonathan S. Katz" <jkatz@postgresql.org>, Noah Misch <noah@leadboat.com>, Robert Haas <robertmhaas@gmail.com>, Andrew Dunstan <andrew@dunslane.net>, Bruce Momjian <bruce@momjian.us>, Michael Paquier <michael@paquier.xyz>, Justin Pryzby <pryzby@telsasoft.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Shruthi Gowda <gowdashru@gmail.com>

Date: 2022-08-03T18:52:18Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Revert recent changes to 002_pg_upgrade.pl.
- 6f7e7d0c482d 15.0 landed
- 87e22f675fd8 16.0 landed
Revise test case added in 43746996399541ecb5c7b188725a5f097c15ceae.
- d92f2bc0dae3 15.0 landed
- 212bdc0cbc32 16.0 landed
Use TRUNCATE to preserve relfilenode for pg_largeobject + index.
- bbe08b8869bd 16.0 landed
- 4ab5dae9472c 15.0 landed
Preserve relfilenode of pg_largeobject and its index across pg_upgrade.
- a2996478c32d 15.0 landed
- d498e052b4b8 16.0 landed
Have VACUUM warn on relfrozenxid "in the future".
- e83ebfe6d767 15.0 cited
Set relfrozenxid to oldest extant XID seen by VACUUM.
- 0b018fabaaba 15.0 cited
pg_upgrade: Preserve relfilenodes and tablespace OIDs.
- 9a974cbcba00 15.0 cited
Perform a lot more sanity checks when freezing tuples.
- 699bf7d05c68 11.0 cited

On Tue, Aug 2, 2022 at 12:32 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hmmm ... now that you mention it, I see nothing in 002_pg_upgrade.pl
> that attempts to turn off autovacuum on either the source server or
> the destination.  So one plausible theory is that autovac moved the
> numbers since we checked.

It's very easy to believe that my work in commit 0b018fab could make
that happen, which is only a few months old. It's now completely
routine for non-aggressive autovacuums to advance relfrozenxid by at
least a small amount.

For example, when autovacuum runs against either the tellers table or
the branches table during a pgbench run, it will now advance
relfrozenxid, every single time. And to a very recent value. This will
happen in spite of the fact that no freezing ever takes place -- it's
just a consequence of the oldest extant XID consistently being quite
young each time, due to workload characteristics.

-- 
Peter Geoghegan