Re: pg15b2: large objects lost on upgrade

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

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

To: Robert Haas <robertmhaas@gmail.com>

Cc: Noah Misch <noah@leadboat.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-07-30T00:02:51Z

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

Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Jul 29, 2022 at 7:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I am suspicious that the problem stems from the nonstandard
>> way you've invoked psql to collect the horizon data.

> Well, I just copied the pg_dump block which occurs directly beforehand
> and modified it. I think that must take care of setting the path
> properly, else we'd have things blowing up all over the place. But the
> lack of -X could be an issue.

Hmm.  Now that I look, I do see two pre-existing "naked" invocations
of psql in 002_pg_upgrade.pl, ie

	$oldnode->command_ok([ 'psql', '-X', '-f', $olddumpfile, 'postgres' ],
		'loaded old dump file');

	$oldnode->command_ok(
		[
			'psql', '-X',
			'-f', "$srcdir/src/bin/pg_upgrade/upgrade_adapt.sql",
			'regression'
		],
		'ran adapt script');

Those suggest that maybe all you need is -X.  However, I don't think
either of those calls is reached by the majority of buildfarm animals,
only ones that are doing cross-version-upgrade tests.  So there
could be more secret sauce needed to get this to pass everywhere.

Personally I'd try to replace the two horizon-collection steps with
$newnode->psql calls, using extra_params to inject the '-o' and target
filename command line words.  But if you want to try adding -X as
a quicker answer, maybe that will be enough.

			regards, tom lane