Horton

Re: pg15b2: large objects lost on upgrade

Bruce Momjian <bruce@momjian.us>

View thread
From: Bruce Momjian <bruce@momjian.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>, Shruthi Gowda <gowdashru@gmail.com>
Date: 2022-07-07T20:15:55Z
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.

On Tue, Jul  5, 2022 at 12:43:54PM -0400, Robert Haas wrote:
> On Sat, Jul 2, 2022 at 11:49 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > I suppose it's like Bruce said, here.
> >
> > https://www.postgresql.org/message-id/20210601140949.GC22012%40momjian.us
> 
> Well, I feel dumb. I remember reading that email back when Bruce sent
> it, but it seems that it slipped out of my head between then and when
> I committed. I think your patch is fine, except that I think maybe we

It happens to us all.

> I had a moment of panic this morning where I thought maybe the whole

Yes, I have had those panics too.

> patch needed to be reverted. I was worried that we might need to
> preserve the OID of every system table and index. Otherwise, what
> happens if the OID counter in the old cluster wraps around and some
> user-created object gets an OID that the system tables are using in
> the new cluster? However, I think this can't happen, because when the
> OID counter wraps around, it wraps around to 16384, and the
> relfilenode values for newly created system tables and indexes are all
> less than 16384. So I believe we only need to fix pg_largeobject and
> its index, and I think your patch does that.

So, let me explain how I look at this.  There are two number-spaces, oid
and relfilenode.  In each number-space, there are system-assigned ones
less than 16384, and higher ones for post-initdb use.

What we did in pre-PG15 was to preserve only oids, and have the
relfilenode match the oid, and we have discussed the negatives of this.

For PG 15+, we preserve relfilenodes too.  These number assignment cases
only work if we handle _all_ numbering, except for non-pg_largeobject
system tables.

In pre-PG15, pg_largeobject was easily handled because initdb already
assigned the oid and relfilenode to be the same for pg_largeobject, so a
simple copy worked fine.  pg_largeobject is an anomaly in PG 15 because
it is assigned a relfilenode in the system number space by initdb, but
then it needs to be potentially renamed into the relfilenode user number
space.  This is the basis for my email as already posted:

	https://www.postgresql.org/message-id/20210601140949.GC22012%40momjian.us

You are right to be concerned since you are spanning number spaces, but
I think you are fine because the relfilenode in the user-space cannot
have been used since it already was being used in each database.  It is
true we never had a per-database rename like this before.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson