Re: PoC: true in-place major version upgrades

On Mon, Nov 2, 2026 at 7:51 AM John Naylor <johncnaylorls(at)gmail(dot)com> wrote:
> Okay, here's an idea, continuing with the not-null constraint example:
> The old way up to version N is a v1 constraint. The N+1 release that
> introduces the v2 constraint must still recognize the v1 constraint,
> but all new constraints will be created as v2 constraints. A future
> release, at least N+2, can drop support for v1 constraints. When the
> user tries to upgrade off of N+1, the check step complains "The system
> still has v1 constraints. These must be converted to v2 constraints",
> and we would have some documented method to do that. This way, complex
> conversions steps don't need to be happen during an already stressful
> downtime window and in this case can probably just be DROP and CREATE
> statements. It has a downside that it puts more responsibility on the
> user, not to mention the development window with excess complexity,
> but it would be less risky if complex transforms are not done
> on-the-fly.

The problem with this is it moves the burden from your patch to other
people's future patches. Obviously, sometimes that's the right
trade-off: for example, we agreed to support pg_upgrade many years
ago, even though that has greatly constrained our ability to evolve
the on-disk format. But we have to be convinced as a group that it's
not going to create more problems than it solves, and Tom basically
said in his reply upthread that he thinks precisely the opposite:
we'll end up not being able to do important things that we want to do
because of this feature. I think Bruce's reply suggests a similar
concern, and I think that's also what Nathan and I are worried about.
That's why I suggested a "last 7 years" test. If you go back through
all of those catalog changes and make all that stuff work, then we
know it's possible and about how much work it would take. If you find
that there are cases you can't make work, where something like the
above would be needed, then you could work out what the old release
would have needed to do and what the new release would have needed to
do and code it up, and then we'll have an approximate idea about how
much of a problem this feature would be have created if we'd added it
years ago, and everyone can then compare that to what they think the
benefit of the feature will be. As we've both said, past performance
is no guarantee of future results, but it's often indicative.

I think it's pretty clear that nobody who has responded so far, other
than maybe you, is keen to give up the ability to make complex catalog
changes in future major releases. So for this effort to succeed, I
think you need to show that it will still be possible and the effort
won't be disproportionate. I think the pg_constraint sketch you give
above is a good example of what is going to make people nervous:
whatever benefits could be realized from the pg_constraint changes now
couldn't be realized until at least release N+2. Plus, the code for
release N+1 might have to be quite complicated to be able to
understand both catalog representations. So I doubt people are going
to be too enthusiastic about this design, but it's fair to point out
we may not yet have the full picture. If this is the only thing that
has happened in a long time that would have caused a serious issue,
maybe it would have been worth taking the hit. If history is littered
with such examples, it probably isn't. And then, maybe this isn't even
the best design possible for that particular case.

--
Robert Haas
Databricks