On Sat, Oct 24, 2026 at 9:21 PM Tom Lane <[email protected]> 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
