MIME-Version: 1.0
References: 
 <CAFPTHDa5aP8ixTZBygGAe48E1N=DvuPzcXikw7bNKixLMN3pVg@mail.gmail.com>
 <OS9PR01MB12149E188221BED862E4BE9E0F5342@OS9PR01MB12149.jpnprd01.prod.outlook.com>
 <CALDaNm2-uwpbJ8fnrssp+hORvOutsqRoZAsa05xVVzXe5Bt3bw@mail.gmail.com>
 <CAFPTHDaNLRqPAda1RUDVfSDH7eLTaONv0UEmc9H5sdMW1Li2Bg@mail.gmail.com>
 <CAN4CZFOb3urMsLPsEyVrYR7-7yA+BC5kDgQQd0nAQ8xj2zyRcA@mail.gmail.com>
 <CAFPTHDYqpuZ6o2-HuCJDYqJ7GY3+zV+Xo-gT7PAgi4Bkz+oTxw@mail.gmail.com>
 <CAN4CZFN4oLdxLwUXHUPV-5mFmK+4dcnppP00fV3i4qmMYCAkGA@mail.gmail.com>
 <CAFPTHDbBiTfjYkQwYTNA2a4LRp8Sp7_zB59fmV0R7977ztxgmg@mail.gmail.com>
 <CAFPTHDaftSwzGTGbFEw8rwDBsub0XqcDm1wxQcquj-Y3PC2qrg@mail.gmail.com>
 <CANhcyEVercsNbgC5CTkMNzh_w_Jv1uK7RQg9vEvAeNQhoBHCKQ@mail.gmail.com>
 <CAJpy0uAXynzWr4w_9GkJC3i=LL9WsRCZYVWAV0PgKmgs8riWzg@mail.gmail.com>
 <CAFPTHDavPyxsWjK0cRO3yOaP4u8FGYrOJuXJB-4wzneAY3H3Ug@mail.gmail.com>
 <CANhcyEVFSutXXdt_0XMCD37NE7Yd7ROdDVEE06D8YVyPNEBFHg@mail.gmail.com>
 <CAJpy0uD-yDM_ViZm5O+ok3HHMGMnqtdW=oOsuU7SHm9QpNx01w@mail.gmail.com>
 <CAA4eK1KzGSANd=g1S8xpcyiwh3M-c+Uaa4+447v-+N8chX7K3g@mail.gmail.com>
 <CAFPTHDYgyDE4+8tM4-d3Gcyg1E4VuQJhmNh9Bwis_W9NKxw0tA@mail.gmail.com>
In-Reply-To: 
 <CAFPTHDYgyDE4+8tM4-d3Gcyg1E4VuQJhmNh9Bwis_W9NKxw0tA@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Wed, 8 Jul 2026 15:32:35 +0530
Message-ID: 
 <CAA4eK1L4FONedTukrNKjeWCc5KAig8uBvx_pxAuYJgKCgrsOpw@mail.gmail.com>
Subject: Re: [PATCH] Preserve replication origin OIDs in pg_upgrade
To: Ajin Cherian <itsajin@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>,
 Shlok Kyal <shlok.kyal.oss@gmail.com>,
	Dilip Kumar <dilipbalaut@gmail.com>,
 Zsolt Parragi <zsolt.parragi@percona.com>,
	vignesh C <vignesh21@gmail.com>,
 "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1L4FONedTukrNKjeWCc5KAig8uBvx_pxAuYJgKCgrsOpw%40mail.gmail.com>
Precedence: bulk

On Tue, Jun 16, 2026 at 2:50=E2=80=AFPM Ajin Cherian <itsajin@gmail.com> wr=
ote:
>
> On Mon, Jun 15, 2026 at 9:31=E2=80=AFPM Amit Kapila <amit.kapila16@gmail.=
com> wrote:
>
>>
>> Separately, I don't think we actually need to preserve the
>> subscription OID to meet the goal here. pg_commit_ts stores the
>> numeric roident, not the origin name or the sub OID, and conflict
>> detection compares roidents. So the invariant we must keep is "each
>> subscription owns the same roident after upgrade", which doesn't
>> depend on the name being stable.
>>
>>
>
> Replication origins are global objects and are therefore dumped by pg_dum=
pall, while subscriptions are database-specific and are dumped by pg_dump. =
In pg_upgrade, pg_dumpall runs before pg_dump.
>
> The complication if subscription OID is not preserved is that subscriptio=
n-associated origins derive their name from the subscription's OID. If the =
subscription OID is not preserved across upgrades, the origin name on the n=
ew cluster will differ from the old one, making it impossible to restore th=
e origin independently. For these origins, creation must happen after Creat=
eSubscription has established the new OID and not upfront in pg_dumpall.
>
> Non-subscription origins have no such dependency and continue to be creat=
ed separately as before. The subscription code also cannot be left untouche=
d: even once the subscription is created, rather than origin being created =
inside CreateSubscription, its associated origin must still be explicitly c=
reated via binary_upgrade_create_replication_origin to ensure the roident m=
atches the original node, which means CreateSubscription needs to be modifi=
ed anyways. So, there is an advantage of reducing code if subscription OID =
is preserved and all origins keep the same name as the old node.
>

I think here the key is not reducing the code foot print in
CreateSubscription rather the non-subscription origins anyway needs to
be created separately by pg_dumpall as they don't have any binding
with subscription. So, if we don't preserve sub OIDs, we will never be
able to match the names of origins created with pg_dumpall and then
later used by subscriptions. I feel this reasoning should be captured
in the commit message.

--=20
With Regards,
Amit Kapila.