Horton

Re: pg15b2: large objects lost on upgrade

Shruthi Gowda <gowdashru@gmail.com>

View thread
From: Shruthi Gowda <gowdashru@gmail.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Robert Haas <robertmhaas@gmail.com>, PostgreSQL Development <pgsql-hackers@postgresql.org>, Bruce Momjian <bruce@momjian.us>
Date: 2022-07-05T11:33:24Z
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 →

  1. Revert recent changes to 002_pg_upgrade.pl.

  2. Revise test case added in 43746996399541ecb5c7b188725a5f097c15ceae.

  3. Use TRUNCATE to preserve relfilenode for pg_largeobject + index.

  4. Preserve relfilenode of pg_largeobject and its index across pg_upgrade.

  5. Have VACUUM warn on relfrozenxid "in the future".

  6. Set relfrozenxid to oldest extant XID seen by VACUUM.

  7. pg_upgrade: Preserve relfilenodes and tablespace OIDs.

  8. Perform a lot more sanity checks when freezing tuples.

I was able to reproduce the issue. Also, the issue does not occur with code
before to preserve relfilenode commit.
I tested your patch and it fixes the problem.
I am currently analyzing a few things related to the issue. I will come
back once my analysis is completed.

On Sat, Jul 2, 2022 at 9:19 PM Justin Pryzby <pryzby@telsasoft.com> wrote:

> 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.
>
> I suppose it's like Bruce said, here.
>
> https://www.postgresql.org/message-id/20210601140949.GC22012%40momjian.us
>
> |One tricky case is pg_largeobject, which is copied from the old to new
> |cluster since it has user data.  To preserve that relfilenode, you would
> |need to have pg_upgrade perform cluster surgery in each database to
> |renumber its relfilenode to match since it is created by initdb.  I
> |can't think of a case where pg_upgrade already does something like that.
>
> Rather than setting the filenode of the next object as for user tables,
> pg-upgrade needs to UPDATE the relfilenode.
>
> This patch "works for me" but feel free to improve on it.
>