Обсуждение: Preserve subscription OIDs during pg_upgrade

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