Re: pg15b2: large objects lost on upgrade

Julien Rouhaud <rjuju123@gmail.com>

From: Julien Rouhaud <rjuju123@gmail.com>

To: Robert Haas <robertmhaas@gmail.com>

Cc: Justin Pryzby <pryzby@telsasoft.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>

Date: 2022-07-02T15:52:08Z

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 Sat, Jul 02, 2022 at 08:34:04AM -0400, Robert Haas wrote:
> On Fri, Jul 1, 2022 at 7:14 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > 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.
> 
> Yikes. That's really bad, and I have no idea what might be causing it,
> either. I'll plan to investigate this on Tuesday unless someone gets
> to it before then.

As far as I can see the data is still there, it's just that the new cluster
keeps its default relfilenode instead of preserving the old cluster's value:

regression=# table pg_largeobject;
 loid | pageno | data
------+--------+------
(0 rows)

regression=# select oid, relfilenode from pg_class where relname = 'pg_largeobject';
 oid  | relfilenode
------+-------------
 2613 |        2613
(1 row)

-- using the value from the old cluster
regression=# update pg_class set relfilenode = 39909 where oid = 2613;
UPDATE 1

regression=# table pg_largeobject;
 loid  | pageno |
-------+--------+-----------------
 33211 |      0 | \x0a4920776[...]
 34356 |      0 | \xdeadbeef
(2 rows)