RE: [PATCH] Preserve replication origin OIDs in pg_upgrade

Dear Shveta,

> No, this difference is not expected.

Yes it must be fixed.

> 2)
> Another idea could be to have a
> 'binary_upgrade_skip_subs_origin_creation' variable. We populate it
> during dumpSubscription if 'source_version >= PG17' and
> 'IsBinaryUpgrade' is true. So this will be dumped
>
> SELECT pg_catalog.binary_upgrade_set_skip_subs_origin_creation(true);
> CREATE SUBSCRIPTION ...
> SELECT pg_catalog.binary_upgrade_set_skip_subs_origin_creation(false);
>
> First call will set 'binary_upgrade_skip_subs_origin_creation' which
> will be used by Create Subscription to decide to skip origin. This is
> similar to how dumpACl decides whether to record priveleges into
> 'pg_init_privs' for objectes created by extensions. See
> binary_upgrade_set_record_init_privs() which sets
> binary_upgrade_record_init_privs which is then used by
> recordExtensionInitPriv().

To clarify, I prefer the 2nd idea. According to 29d0a77, which migrates
replication slots from PG17+, it does not change the behavior when the source is
PG16-. We should follow the manner.

In case of third idea, origin id can be also preserved for PG16-, but the
version was out-of-scope of our patch [1]; in that version pg_subscription_rel
cannot be migrated, thus the logical replication cannot work in the first place.

Also, if we want to keep the existing behavior, we should allow upgrading the
below case.

Source: PG16, two subscriptions and origins exist (id = 1, 2).
Target: an origin exists (id = 1)

But we must reject upgrading if we preserves the origin-id, right?

[1]: https://www.postgresql.org/message-id/CAFPTHDYqpuZ6o2-HuCJDYqJ7GY3%2BzV%2BXo-gT7PAgi4Bkz%2BoTxw%40mail.gmail.com

Best regards,
Hayato Kuroda
FUJITSU LIMITED