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
>From a90db3fa008f73f7f97cc73e4453ebf2e981cb83 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nat...@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)
