Re: PoC: true in-place major version upgrades

On Mon, Oct 26, 2026 at 7:29 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> But it's unclear how we could
> handle more complex cases. For example, imagine we remove a function
> and, in a particular user's install, there's an object with a
> dependency on the removed object. Keeping the old object isn't
> feasible, and implementing DROP CASCADE in the middle of pg_upgrade is
> a bad idea both because having a second copy of that logic that only
> runs in mid-upgrade would be terrifying and also because the resulting
> behavior might quite possibly not be what the user wants.
>
> Today, such cases result in pg_upgrade erroring out, which isn't
> great, but it's not clear what would be better. I think that this new
> system would have to mimic that behavior, and it probably could, but

I don't know offhand exactly what pg_upgrade's error is, but the
offline prototype throws

ERROR: "an old row references pg_proc XYZ, which no longer exists in
this version"

...and you'd need to roll back and fix it.

> every major release would need a matching pg_upgrade patch to teach it
> about all the transformations that are required, and when they should
> error out,

Most of the time, it's just going to be new columns containing '0' or
'null', isn't it? But maybe I'm missing something, but it seems to me
that most catalogs' form or semantics don't change all that much. As I
mentioned to Tom, the only upgrade that the prototype tried with the
(almost) full regression database was 18 -> 20devel.

> and what would happen if a particular transformation is so
> complicated that we can't figure out how to code it up in the middle
> of a catalog rewrite?

In the offline prototype, if you're hacking on a feature, catalog
columns can be marked TODO, which will just cause the emitted
transform function to throw ERROR when called during an upgrade, but
everything else would be unaffected. It shouldn't interfere with
writing the feature itself, until it's time to commit. If you don't
replace the TODO then you'd get failures in the cross version upgrade
tests. If you mean if we can't figure it out _at all_, well then yes
we have a problem.

> Tom's example of the NOT NULL changes is a good
> one: we need to allocate OIDs and we can't know if any given OID is
> available and I think we might need to worry about constraint names
> colliding and I suspect there are more problems. I think we'll find
> that being able to do transformations like this without a
> fully-running system is extremely difficult, and I suspect that's
> enough reason to give up on this idea.

For complex cases like that, you'd have to have a separate step when
the system is fully running, and that's after the main row-by-row
catalog migration has completed (i.e. the merge with the new binary's
bootstrap data) and after the node trees have been updated. So you can
allocate OIDs and call ChooseConstraintName() etc. So, it'd be a pain
in the rear at times, but I don't think it's going to be the same
level of sadness that some of our users encounter with pg_upgrade.

Perhaps we can arrange for the relevant info to be gathered during the
prepare step on the old version, so here the orchestrator would create
a .sql file with a bunch of

SELECT pg_upgrade_create_constraint_notnull(foo_rel, bar_col, ...);

...which would do the right thing (and not validate). Maybe that's
what your next paragraph was hinting at?

> I don't really like that conclusion, because I've always felt that
> pg_upgrade is a pretty half-baked version of upgrade-in-place. Both
> the rather high possibility of failures requiring manual correction
> and also the time it takes to run are real downsides, and I would like
> to have something better. But I suspect this proposal is not the way.
> I do wonder if some more limited version of it might make sense. For
> example, what if we could directly write pg_class tuples for all the
> relations we're porting over, and then re-add the rest of the objects
> using SQL? But even here I see problems: pg_class entries for
> partitioned tables store node trees, and pg_index entries can as well,
> and right there things start to get complicated, because those node
> trees might not be valid any more on the new version. We could try to
> rescue the idea by explicitly excluding the node trees from what gets
> reconstructed directly and add back partitioning expressions and index
> predicates and expressions as a second step that uses SQL as a medium,
> and maybe that would work.

That's pretty much what the offline prototype did for node trees. The
node trees are deparsed into SQL and saved to disk during the prepare
phase on the old version, and "recompiled" back into new-version node
trees in the new catalogs. It's not without challenges, I'll admit,
but it already works.

I don't really like "re-add the rest of the objects using SQL" as a
starting point, since that sounds like re-implementing most of dump
and restore, which is where the performance problems come from, but
SQL could be an escape hatch.

--
John Naylor
Amazon Web Services