Preserve subscription OIDs during pg_upgrade
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t49241psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 11:14 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t49241_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t49241_1 && git checkout t49241_1Patchset v1 (message #1) is on t49241_1
Hi,
Recently we have supported upgrade of subscriptions,but currently
subscription OIDs can be changed when a cluster is upgraded using
pg_upgrade. It will be better to preserve them as it will be easier to
compare subscription related objects in pg_subscription and
pg_subscription_rel in the old and new clusters.
Attached patch has the changes for the same.
Regards,
Vignesh
vignesh C <vignesh21@gmail.com> writes:
Recently we have supported upgrade of subscriptions,but currently
subscription OIDs can be changed when a cluster is upgraded using
pg_upgrade. It will be better to preserve them as it will be easier to
compare subscription related objects in pg_subscription and
pg_subscription_rel in the old and new clusters.
I do not think that's a sufficient argument. For other object types,
we only go through these pushups if we *have to* do so because the
OIDs may appear in user tables or file names. I don't see a reason
that subscriptions deserve special treatment.
regards, tom lane
On Sun, Feb 25, 2024 at 11:34:35AM -0500, Tom Lane wrote:
vignesh C <vignesh21@gmail.com> writes:
Recently we have supported upgrade of subscriptions,but currently
subscription OIDs can be changed when a cluster is upgraded using
pg_upgrade. It will be better to preserve them as it will be easier to
compare subscription related objects in pg_subscription and
pg_subscription_rel in the old and new clusters.I do not think that's a sufficient argument. For other object types,
we only go through these pushups if we *have to* do so because the
OIDs may appear in user tables or file names. I don't see a reason
that subscriptions deserve special treatment.
I think that the idea behind that it that it would then become
possible to relax the restrictions related to the states of the
relations stored in pg_subscription_rel, which can now be only a
"ready" or "init" state (see check_old_cluster_subscription_state)
when we begin the upgrade.
I am not sure that it is a good idea to relax that for PG17 at this
stage of the development cycle, though, as we have already done a lot
in this area for pg_upgrade and it may require more tweaks during the
beta period depending on the feedback received, so I would suggest to
do more improvements for the 18 cycle instead once we have a cleaner
picture of the whole.
--
Michael
On Mon, Feb 26, 2024 at 6:07 AM Michael Paquier <michael@paquier.xyz> wrote:
I think that the idea behind that it that it would then become
possible to relax the restrictions related to the states of the
relations stored in pg_subscription_rel, which can now be only a
"ready" or "init" state (see check_old_cluster_subscription_state)
when we begin the upgrade.
How would it help with that?
I am not sure that it is a good idea to relax that for PG17 at this
stage of the development cycle, though, as we have already done a lot
in this area for pg_upgrade and it may require more tweaks during the
beta period depending on the feedback received, so I would suggest to
do more improvements for the 18 cycle instead once we have a cleaner
picture of the whole.
That's fair.
I want to say that, unlike Tom, I'm basically in favor of preserving
OIDs in more places across updates. It seems to have little downside
and improve the understandability of the outcome. But that's separate
from whether it is a good idea to build on that infrastructure in any
particular way in the time we have left for this release.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Mon, Feb 26, 2024 at 09:51:40AM +0530, Robert Haas wrote:
I am not sure that it is a good idea to relax that for PG17 at this
stage of the development cycle, though, as we have already done a lot
in this area for pg_upgrade and it may require more tweaks during the
beta period depending on the feedback received, so I would suggest to
do more improvements for the 18 cycle instead once we have a cleaner
picture of the whole.That's fair.
I want to say that, unlike Tom, I'm basically in favor of preserving
OIDs in more places across updates. It seems to have little downside
and improve the understandability of the outcome. But that's separate
from whether it is a good idea to build on that infrastructure in any
particular way in the time we have left for this release.
Yes, the _minimal_ approach has changed in the past few years to make
pg_upgrade debugging easier. The original design was ultra-conservative
where it could be, considering how radical the core functionality was.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Hi,
It will be better to preserve them as it will be easier to
compare subscription related objects in pg_subscription and
pg_subscription_rel in the old and new clusters.
IMO it would be helpful if you could give a little bit more context on
why/when this is useful. Personally I find it somewhat difficult to
imagine a case when I really need to compare Oids of subscriptions
between old and new clusters.
If we commit to such a guarantee it will lay a certain burden on the
community in the long run and the benefits are not quite clear, to me
at least. If we are talking about giving such a guarantee only once
the value of this is arguably low.
--
Best regards,
Aleksander Alekseev