On Fri, 10 Nov 2023 at 19:26, vignesh C <vignes...@gmail.com> wrote: > > On Thu, 9 Nov 2023 at 12:23, Michael Paquier <mich...@paquier.xyz> wrote: > > > > > Note: actually, this would be OK if we are able to keep the OIDs of > > the subscribers consistent across upgrades? I'm OK to not do nothing > > about that in this patch, to keep it simpler. Just asking in passing. > > I will analyze more on this and post the analysis in the subsequent mail.
I analyzed further and felt that retaining subscription oid would be cleaner as subscription/subscription_rel/replication_origin/replication_origin_status all of these will be using the same oid as earlier and also probably help in supporting upgrade of subscription in more scenarios later. Here is a patch to handle the same. Regards, Vignesh
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index edc82c11be..1c7bb4b7cd 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -75,6 +75,9 @@ /* check if the 'val' has 'bits' set */ #define IsSet(val, bits) (((val) & (bits)) == (bits)) +/* Potentially set by pg_upgrade_support functions */ +Oid binary_upgrade_next_pg_subscription_oid = InvalidOid; + /* * Structure to hold a bitmap representing the user-provided CREATE/ALTER * SUBSCRIPTION command options and the parsed/default values of each of them. @@ -679,8 +682,23 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt, memset(values, 0, sizeof(values)); memset(nulls, false, sizeof(nulls)); - subid = GetNewOidWithIndex(rel, SubscriptionObjectIndexId, - Anum_pg_subscription_oid); + /* Use binary-upgrade override for pg_subscription.oid? */ + if (IsBinaryUpgrade) + { + if (!OidIsValid(binary_upgrade_next_pg_subscription_oid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("pg_subscription OID value not set when in binary upgrade mode"))); + + subid = binary_upgrade_next_pg_subscription_oid; + binary_upgrade_next_pg_subscription_oid = InvalidOid; + } + else + { + subid = GetNewOidWithIndex(rel, SubscriptionObjectIndexId, + Anum_pg_subscription_oid); + } + values[Anum_pg_subscription_oid - 1] = ObjectIdGetDatum(subid); values[Anum_pg_subscription_subdbid - 1] = ObjectIdGetDatum(MyDatabaseId); values[Anum_pg_subscription_subskiplsn - 1] = LSNGetDatum(InvalidXLogRecPtr); diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c index 53cfa72b6f..34c328ea0d 100644 --- a/src/backend/utils/adt/pg_upgrade_support.c +++ b/src/backend/utils/adt/pg_upgrade_support.c @@ -59,6 +59,17 @@ binary_upgrade_set_next_pg_type_oid(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } +Datum +binary_upgrade_set_next_pg_subscription_oid(PG_FUNCTION_ARGS) +{ + Oid subid = PG_GETARG_OID(0); + + CHECK_IS_BINARY_UPGRADE; + binary_upgrade_next_pg_subscription_oid = subid; + + PG_RETURN_VOID(); +} + Datum binary_upgrade_set_next_array_pg_type_oid(PG_FUNCTION_ARGS) { diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 4528b7cc39..bc309be2a8 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4954,6 +4954,14 @@ dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo) appendPQExpBuffer(delq, "DROP SUBSCRIPTION %s;\n", qsubname); + if (dopt->binary_upgrade) + { + appendPQExpBufferStr(query, "\n-- For binary upgrade, must preserve pg_subscription.oid\n"); + appendPQExpBuffer(query, + "SELECT pg_catalog.binary_upgrade_set_next_pg_subscription_oid('%u'::pg_catalog.oid);\n\n", + subinfo->dobj.catId.oid); + } + appendPQExpBuffer(query, "CREATE SUBSCRIPTION %s CONNECTION ", qsubname); appendStringLiteralAH(query, subinfo->subconninfo, fout); diff --git a/src/include/catalog/binary_upgrade.h b/src/include/catalog/binary_upgrade.h index 82a9125ba9..456e777b8d 100644 --- a/src/include/catalog/binary_upgrade.h +++ b/src/include/catalog/binary_upgrade.h @@ -19,6 +19,7 @@ extern PGDLLIMPORT Oid binary_upgrade_next_pg_tablespace_oid; extern PGDLLIMPORT Oid binary_upgrade_next_pg_type_oid; +extern PGDLLIMPORT Oid binary_upgrade_next_pg_subscription_oid; extern PGDLLIMPORT Oid binary_upgrade_next_array_pg_type_oid; extern PGDLLIMPORT Oid binary_upgrade_next_mrng_pg_type_oid; extern PGDLLIMPORT Oid binary_upgrade_next_mrng_array_pg_type_oid; diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 45c681db5e..43cf39acae 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11328,6 +11328,10 @@ proname => 'binary_upgrade_set_next_pg_type_oid', provolatile => 'v', proparallel => 'r', prorettype => 'void', proargtypes => 'oid', prosrc => 'binary_upgrade_set_next_pg_type_oid' }, +{ oid => '8406', descr => 'for use by pg_upgrade', + proname => 'binary_upgrade_set_next_pg_subscription_oid', provolatile => 'v', + proparallel => 'r', prorettype => 'void', proargtypes => 'oid', + prosrc => 'binary_upgrade_set_next_pg_subscription_oid' }, { oid => '3584', descr => 'for use by pg_upgrade', proname => 'binary_upgrade_set_next_array_pg_type_oid', provolatile => 'v', proparallel => 'r', prorettype => 'void', proargtypes => 'oid',