pg15b2: large objects lost on upgrade

Justin Pryzby <pryzby@telsasoft.com>

From: Justin Pryzby <pryzby@telsasoft.com>

To: pgsql-hackers@postgresql.org

Cc: Robert Haas <rhaas@postgresql.org>

Date: 2022-07-01T23:14:13Z

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

I noticed this during beta1, but dismissed the issue when it wasn't easily
reproducible.  Now, I saw the same problem while upgrading from beta1 to beta2,
so couldn't dismiss it.  It turns out that LOs are lost if VACUUM FULL was run.

| /usr/pgsql-15b1/bin/initdb --no-sync -D pg15b1.dat -k
| /usr/pgsql-15b1/bin/postgres -D pg15b1.dat -c logging_collector=no -p 5678 -k /tmp&
| psql -h /tmp postgres -p 5678 -c '\lo_import /etc/shells' -c 'VACUUM FULL pg_largeobject'
| rm -fr pg15b2.dat && /usr/pgsql-15b2/bin/initdb --no-sync -k -D pg15b2.dat && /usr/pgsql-15b2/bin/pg_upgrade -d pg15b1.dat -D pg15b2.dat -b /usr/pgsql-15b1/bin
| /usr/pgsql-15b2/bin/postgres -D pg15b2.dat -c logging_collector=no -p 5678 -k /tmp&

Or, for your convenience, with paths in tmp_install:
| ./tmp_install/usr/local/pgsql/bin/initdb --no-sync -D pg15b1.dat -k
| ./tmp_install/usr/local/pgsql/bin/postgres -D pg15b1.dat -c logging_collector=no -p 5678 -k /tmp&
| psql -h /tmp postgres -p 5678 -c '\lo_import /etc/shells' -c 'VACUUM FULL pg_largeobject'
| rm -fr pg15b2.dat && ./tmp_install/usr/local/pgsql/bin/initdb --no-sync -k -D pg15b2.dat && ./tmp_install/usr/local/pgsql/bin/pg_upgrade -d pg15b1.dat -D pg15b2.dat -b ./tmp_install/usr/local/pgsql/bin
| ./tmp_install/usr/local/pgsql/bin/postgres -D pg15b2.dat -c logging_collector=no -p 5678 -k /tmp&

postgres=# table pg_largeobject_metadata ;
 16384 |       10 | 

postgres=# \lo_list 
 16384 | pryzbyj | 

postgres=# \lo_export 16384 /dev/stdout
lo_export
postgres=# table pg_largeobject;

postgres=# \dt+ pg_largeobject
 pg_catalog | pg_largeobject | table | pryzbyj | permanent   | heap          | 0 bytes | 

I reproduced the problem at 9a974cbcba but not its parent commit.

commit 9a974cbcba005256a19991203583a94b4f9a21a9
Author: Robert Haas <rhaas@postgresql.org>
Date:   Mon Jan 17 13:32:44 2022 -0500

    pg_upgrade: Preserve relfilenodes and tablespace OIDs.

-- 
Justin