Re: PoC: true in-place major version upgrades

On Sun, Oct 25, 2026 at 8:21 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> John Naylor <johncnaylorls(at)gmail(dot)com> writes:
> > *** Catalogs (TODO)
>
> > While the PoC sidesteps this entirely for now, this is actually how I
> > started. About a year and half ago, I experimented with catalog header
> > annotations so that the usual couple hundred lines of Perl would
> > generate the code that transforms the catalog tuples. Earlier this
> > year, I turned that into a throwaway "offline upgrade" (i.e. no WAL)
> > prototype. That exercise indicated that this method could feasibly
> > handle all catalog schema changes since PG12. Example from pg_class:
>
> I'm just really, really skeptical that this is practical for
> anything except the most trivial changes (such as your two examples).
> You already mentioned that stored expressions (node trees) won't work,
> but what about cases where we move information across catalogs, or
> create things that just weren't there before? A handy example is the
> recent changes to make pg_constraint entries for column NOT NULL
> constraints.

Okay, I misjudged where my effort should have gone when deciding what
part was worth getting into halfway functional shape and describing in
some detail. That's also useful feedback.

Yeah, that's one of the messy cases. The offline prototype only fully
exercised upgrading from 18 to 20devel (only punting on
pg_subscription), which didn't have to deal with that.

> Adding pg_depend entries for linkages where we didn't
> formerly require one seems like another plausible-and-hard-to-implement
> case.

The offline prototype had to rebuild rule and function dependencies
for node trees, and that much wasn't hard. If you're concerned about
discovering when we'd need to add new entries, that's sounds like a
valid concern.

> > 2) At some point, catalog OIDs (e.g. between 10k and 12k) that now
> > vary between versions should be made immutable. Technically, it's not
> > necessary, but it makes it a lot nicer.
>
> The core reason why that's not so today is that the set of
> pg_collation entries made by initdb depends on the platform. I don't
> think you're going to be able to make that not be the case, at least
> not without significant downsides.

The platform collation (and information schema etc) entries are >12k.
The immutability would apply only to genbki-assigned oids for pg_amop,
pg_amproc, pg_cast, pg_opclass, pg_auth_members. We do have control
over those.

--
John Naylor
Amazon Web Services