Re: PoC: true in-place major version upgrades

On Sat, Oct 24, 2026 at 9:21 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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. Adding pg_depend entries for linkages where we didn't
> formerly require one seems like another plausible-and-hard-to-implement
> case. So I'm afraid where this is going to lead us is either a
> straitjacket on what we can change in the catalog representation,
> or an additional requirement for even more complex, hard-to-test code
> that has to be written in order to put forward such changes. We
> built pg_upgrade the way we did precisely to avoid such problems,
> and I don't want to give that up.

I agree. I actually had an idea like what John has done here over a
decade ago, and I think for many cases it would work fine, and the
cost would be that we would have to write transformation scripts for
new major releases. Back then, I was thinking about relistemp ->
relpersistence, which would be easy, since true and false would map to
't' and 'p', leaving 'u' as a new value. 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
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, 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? 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.

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. But there might be other problems there,
and I'm not even sure how much of the actual speed problem it would
fix. Regardless, the question I'd ask is whether there is some more
restrained proposal that could still deliver enough of the benefit to
be worth doing.

--
Robert Haas
Databricks