Date: Sat, 20 Jul 2024 21:03:07 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
	Justin Pryzby <pryzby@telsasoft.com>,
	Masahiko Sawada <sawada.mshk@gmail.com>,
	Michael Paquier <michael@paquier.xyz>,
	Peter Smith <smithpb2250@gmail.com>,
	Julien Rouhaud <rjuju123@gmail.com>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade and logical replication
Message-ID: <ZpxsWx5jwlMCBZfn@nathan>
References: 
 <CAA4eK1+sO6Td4kUnV-G7K_doZm4qERSLj-rw2vMBLUNh9=JBqQ@mail.gmail.com>
 <TYCPR01MB12077B16EEDA360BA645B96F8F54C2@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <CALDaNm3h7ReG5wB9bhPpU=GpVp9Cfotx36tRErw5oKEXu+cKTA@mail.gmail.com>
 <TYCPR01MB12077C4D38046B89599322EBBF54C2@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <CALDaNm1kdAnfx8N1AUQUuxc0zXbghFKi3-4vFAym_Y3hPOm1hA@mail.gmail.com>
 <TYCPR01MB120778982E38315F541D61AD1F5512@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <CAA4eK1+B47ooT2HD1OTbSkVedK1BJLbr2K-seTd9pg9-wTgOdQ@mail.gmail.com>
 <CALDaNm0z=fLtio1h50K8WossUGXU+gy0H9y9=RYh1DDZiq2EDw@mail.gmail.com>
 <CAA4eK1LrO2qGcQot-9EgxpQRiT1O5+enHb57oN7OSNKSTn2hNQ@mail.gmail.com>
 <ZprQJv_TxccN3tkr@nathan>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="lsWyJSSM7QIdmXcR"
Content-Disposition: inline
In-Reply-To: <ZprQJv_TxccN3tkr@nathan>
Archived-At: <https://www.postgresql.org/message-id/ZpxsWx5jwlMCBZfn%40nathan>
Precedence: bulk


--lsWyJSSM7QIdmXcR
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, Jul 19, 2024 at 03:44:22PM -0500, Nathan Bossart wrote:
> I've been looking into optimizing pg_upgrade's once-in-each-database steps
> [0], and I noticed that we are opening a connection to every database in
> the cluster and running a query like
> 
> 	SELECT count(*) FROM pg_catalog.pg_subscription WHERE subdbid = %d;
> 
> Then, later on, we combine all of these values in
> count_old_cluster_subscriptions() to verify that max_replication_slots is
> set high enough.  AFAICT these per-database subscription counts aren't used
> for anything else.
> 
> This is an extremely expensive way to perform that check, and so I'm
> wondering why we don't just do
> 
> 	SELECT count(*) FROM pg_catalog.pg_subscription;
> 
> once in count_old_cluster_subscriptions().

Like so...

-- 
nathan

--lsWyJSSM7QIdmXcR
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename="v1-0001-pg_upgrade-retrieve-subscription-count-more-effic.patch"

From a90db3fa008f73f7f97cc73e4453ebf2e981cb83 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Sat, 20 Jul 2024 21:01:29 -0500
Subject: [PATCH v1 1/1] pg_upgrade: retrieve subscription count more
 efficiently

---
 src/bin/pg_upgrade/check.c      |  9 +++-----
 src/bin/pg_upgrade/info.c       | 39 ++++++++-------------------------
 src/bin/pg_upgrade/pg_upgrade.h |  3 +--
 3 files changed, 13 insertions(+), 38 deletions(-)

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 27924159d6..39d14b7b92 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -1797,17 +1797,14 @@ check_new_cluster_subscription_configuration(void)
 {
 	PGresult   *res;
 	PGconn	   *conn;
-	int			nsubs_on_old;
 	int			max_replication_slots;
 
 	/* Subscriptions and their dependencies can be migrated since PG17. */
 	if (GET_MAJOR_VERSION(old_cluster.major_version) < 1700)
 		return;
 
-	nsubs_on_old = count_old_cluster_subscriptions();
-
 	/* Quick return if there are no subscriptions to be migrated. */
-	if (nsubs_on_old == 0)
+	if (old_cluster.nsubs == 0)
 		return;
 
 	prep_status("Checking for new cluster configuration for subscriptions");
@@ -1821,10 +1818,10 @@ check_new_cluster_subscription_configuration(void)
 		pg_fatal("could not determine parameter settings on new cluster");
 
 	max_replication_slots = atoi(PQgetvalue(res, 0, 0));
-	if (nsubs_on_old > max_replication_slots)
+	if (old_cluster.nsubs > max_replication_slots)
 		pg_fatal("\"max_replication_slots\" (%d) must be greater than or equal to the number of "
 				 "subscriptions (%d) on the old cluster",
-				 max_replication_slots, nsubs_on_old);
+				 max_replication_slots, old_cluster.nsubs);
 
 	PQclear(res);
 	PQfinish(conn);
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index 95c22a7200..78398ec58b 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -28,7 +28,7 @@ static void print_db_infos(DbInfoArr *db_arr);
 static void print_rel_infos(RelInfoArr *rel_arr);
 static void print_slot_infos(LogicalSlotInfoArr *slot_arr);
 static void get_old_cluster_logical_slot_infos(DbInfo *dbinfo, bool live_check);
-static void get_db_subscription_count(DbInfo *dbinfo);
+static void get_subscription_count(ClusterInfo *cluster);
 
 
 /*
@@ -298,12 +298,12 @@ get_db_rel_and_slot_infos(ClusterInfo *cluster, bool live_check)
 		 * count for the old cluster.
 		 */
 		if (cluster == &old_cluster)
-		{
 			get_old_cluster_logical_slot_infos(pDbInfo, live_check);
-			get_db_subscription_count(pDbInfo);
-		}
 	}
 
+	if (cluster == &old_cluster)
+		get_subscription_count(cluster);
+
 	if (cluster == &old_cluster)
 		pg_log(PG_VERBOSE, "\nsource databases:");
 	else
@@ -750,14 +750,14 @@ count_old_cluster_logical_slots(void)
 /*
  * get_db_subscription_count()
  *
- * Gets the number of subscriptions in the database referred to by "dbinfo".
+ * Gets the number of subscriptions in the cluster.
  *
  * Note: This function will not do anything if the old cluster is pre-PG17.
  * This is because before that the logical slots are not upgraded, so we will
  * not be able to upgrade the logical replication clusters completely.
  */
 static void
-get_db_subscription_count(DbInfo *dbinfo)
+get_subscription_count(ClusterInfo *cluster)
 {
 	PGconn	   *conn;
 	PGresult   *res;
@@ -766,36 +766,15 @@ get_db_subscription_count(DbInfo *dbinfo)
 	if (GET_MAJOR_VERSION(old_cluster.major_version) < 1700)
 		return;
 
-	conn = connectToServer(&old_cluster, dbinfo->db_name);
+	conn = connectToServer(&old_cluster, "template1");
 	res = executeQueryOrDie(conn, "SELECT count(*) "
-							"FROM pg_catalog.pg_subscription WHERE subdbid = %u",
-							dbinfo->db_oid);
-	dbinfo->nsubs = atoi(PQgetvalue(res, 0, 0));
+							"FROM pg_catalog.pg_subscription");
+	cluster->nsubs = atoi(PQgetvalue(res, 0, 0));
 
 	PQclear(res);
 	PQfinish(conn);
 }
 
-/*
- * count_old_cluster_subscriptions()
- *
- * Returns the number of subscriptions for all databases.
- *
- * Note: this function always returns 0 if the old_cluster is PG16 and prior
- * because we gather subscriptions only for cluster versions greater than or
- * equal to PG17. See get_db_subscription_count().
- */
-int
-count_old_cluster_subscriptions(void)
-{
-	int			nsubs = 0;
-
-	for (int dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
-		nsubs += old_cluster.dbarr.dbs[dbnum].nsubs;
-
-	return nsubs;
-}
-
 static void
 free_db_and_rel_infos(DbInfoArr *db_arr)
 {
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 8afe240bdf..e6dbbe6a93 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -197,7 +197,6 @@ typedef struct
 											 * path */
 	RelInfoArr	rel_arr;		/* array of all user relinfos */
 	LogicalSlotInfoArr slot_arr;	/* array of all LogicalSlotInfo */
-	int			nsubs;			/* number of subscriptions */
 } DbInfo;
 
 /*
@@ -296,6 +295,7 @@ typedef struct
 	char		major_version_str[64];	/* string PG_VERSION of cluster */
 	uint32		bin_version;	/* version returned from pg_ctl */
 	const char *tablespace_suffix;	/* directory specification */
+	int			nsubs;			/* number of subscriptions */
 } ClusterInfo;
 
 
@@ -430,7 +430,6 @@ FileNameMap *gen_db_file_maps(DbInfo *old_db,
 							  const char *new_pgdata);
 void		get_db_rel_and_slot_infos(ClusterInfo *cluster, bool live_check);
 int			count_old_cluster_logical_slots(void);
-int			count_old_cluster_subscriptions(void);
 
 /* option.c */
 
-- 
2.39.3 (Apple Git-146)


--lsWyJSSM7QIdmXcR--