v66-0006-FetchTableStates-performance-improvements.patch

From 5276031713c3dfb98d31d159d8933b0499839126 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Thu, 25 Mar 2021 17:55:58 +1100
Subject: [PATCH v66] FetchTableStates performance improvements.

Instead of calling the GetSubscriptionRelations every time to find
if the subscription has any tables, now we call a new function
HasSubscriptionRelations.

And even this is called only when we are unsure do tables exist or not.
---
 src/backend/catalog/pg_subscription.c       | 34 +++++++++++++++++++++++++++++
 src/backend/replication/logical/tablesync.c | 31 +++++++++++++++-----------
 src/include/catalog/pg_subscription_rel.h   |  1 +
 3 files changed, 53 insertions(+), 13 deletions(-)

diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index 658d9f8..0f725fb 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -450,6 +450,40 @@ RemoveSubscriptionRel(Oid subid, Oid relid)
 	table_close(rel, RowExclusiveLock);
 }
 
+/*
+ * Does the subscription have any relations?
+ *
+ * Use this function only to know true/false, and when you have no need for the
+ * the List returned by GetSubscriptionRelations.
+ */
+bool
+HasSubscriptionRelations(Oid subid)
+{
+	Relation	rel;
+	int			nkeys = 0;
+	ScanKeyData skey[2];
+	SysScanDesc scan;
+	bool		has_subrels = false;
+
+	rel = table_open(SubscriptionRelRelationId, AccessShareLock);
+
+	ScanKeyInit(&skey[nkeys++],
+				Anum_pg_subscription_rel_srsubid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(subid));
+
+	scan = systable_beginscan(rel, InvalidOid, false,
+							  NULL, nkeys, skey);
+
+	/* If even a single tuple exists then the subscription has tables. */
+	has_subrels = HeapTupleIsValid(systable_getnext(scan));
+
+	/* Cleanup */
+	systable_endscan(scan);
+	table_close(rel, AccessShareLock);
+
+	return has_subrels;
+}
 
 /*
  * Get all relations for subscription.
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index a1c9949..14c52e3 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -119,7 +119,7 @@
 
 static bool table_states_valid = false;
 static List *table_states_not_ready = NIL;
-static int FetchTableStates(bool *started_tx);
+static bool FetchTableStates(bool *started_tx);
 
 StringInfo	copybuf = NULL;
 
@@ -1144,19 +1144,18 @@ copy_table_done:
 /*
  * Common code to fetch the up-to-date sync state info into the static lists.
  *
- * Returns how many tables belong to the subscription.
+ * Returns true if subscription has 1 or more tables, else false.
  */
-static int
+static bool
 FetchTableStates(bool *started_tx)
 {
-	static int n_subrels = 0;
+	static int has_subrels = false;
 
 	*started_tx = false;
 
 	if (!table_states_valid)
 	{
 		MemoryContext oldctx;
-		List	   *subrels = NIL;
 		List	   *rstates;
 		ListCell   *lc;
 		SubscriptionRelState *rstate;
@@ -1171,10 +1170,6 @@ FetchTableStates(bool *started_tx)
 			*started_tx = true;
 		}
 
-		/* count all subscription tables. */
-		subrels = GetSubscriptionRelations(MySubscription->oid);
-		n_subrels = list_length(subrels);
-
 		/* Fetch all non-ready tables. */
 		rstates = GetSubscriptionNotReadyRelations(MySubscription->oid);
 
@@ -1188,10 +1183,20 @@ FetchTableStates(bool *started_tx)
 		}
 		MemoryContextSwitchTo(oldctx);
 
+		/*
+		 * Does the subscription have tables?
+		 *
+		 * If there were not-READY relations found then we know it does. But if
+		 * table_state_no_ready was empty we still need to check again to see
+		 * if there are 0 tables.
+		 */
+		has_subrels = (list_length(table_states_not_ready) > 0) ||
+			HasSubscriptionRelations(MySubscription->oid);
+
 		table_states_valid = true;
 	}
 
-	return n_subrels;
+	return has_subrels;
 }
 
 /*
@@ -1207,10 +1212,10 @@ AllTablesyncsReady(void)
 {
 	bool		found_busy = false;
 	bool		started_tx = false;
-	int			n_subrels = 0;
+	bool		has_subrels = false;
 
 	/* We need up-to-date sync state info for subscription tables here. */
-	n_subrels = FetchTableStates(&started_tx);
+	has_subrels = FetchTableStates(&started_tx);
 
 	found_busy = list_length(table_states_not_ready) > 0;
 
@@ -1224,7 +1229,7 @@ AllTablesyncsReady(void)
 	 * When there are no tables, then return false.
 	 * When no tablesyncs are busy, then all are READY
 	 */
-	return n_subrels > 0 && !found_busy;
+	return has_subrels && !found_busy;
 }
 
 /*
diff --git a/src/include/catalog/pg_subscription_rel.h b/src/include/catalog/pg_subscription_rel.h
index ed94f57..765e9b5 100644
--- a/src/include/catalog/pg_subscription_rel.h
+++ b/src/include/catalog/pg_subscription_rel.h
@@ -88,6 +88,7 @@ extern void UpdateSubscriptionRelState(Oid subid, Oid relid, char state,
 extern char GetSubscriptionRelState(Oid subid, Oid relid, XLogRecPtr *sublsn);
 extern void RemoveSubscriptionRel(Oid subid, Oid relid);
 
+extern bool HasSubscriptionRelations(Oid subid);
 extern List *GetSubscriptionRelations(Oid subid);
 extern List *GetSubscriptionNotReadyRelations(Oid subid);
 
-- 
1.8.3.1