REL14_v7-0001-Fix-data-replicated-twice-when-specifying-publish_patch

From f43ba6769179dfe1d6d72b25418f0ff7e144cb2c Mon Sep 17 00:00:00 2001
From: wangw <wangw.fnst@fujitsu.com>
Date: Thu, 28 Apr 2022 11:28:35 +0800
Subject: [PATCH v7] Fix data replicated twice when specifying
 publish_via_partition_root option.

If there are two publications that publish the parent table and the child table
separately, and both specify the option publish_via_partition_root, subscribing
to both publications from one subscription causes initial copy twice. It should
only be copied once.

To fix this, we exclude the partition table whose ancestor belongs to specified
publications when getting the table list from publisher.
---
 src/backend/commands/subscriptioncmds.c  | 35 ++++++++++++++++++------
 src/test/subscription/t/013_partition.pl | 19 +++++--------
 2 files changed, 34 insertions(+), 20 deletions(-)

diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 1719f04517..948990a36d 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -1477,7 +1477,8 @@ static List *
 fetch_table_list(WalReceiverConn *wrconn, List *publications)
 {
 	WalRcvExecResult *res;
-	StringInfoData cmd;
+	StringInfoData cmd,
+				pub_names;
 	TupleTableSlot *slot;
 	Oid			tableRow[2] = {TEXTOID, TEXTOID};
 	ListCell   *lc;
@@ -1486,10 +1487,7 @@ fetch_table_list(WalReceiverConn *wrconn, List *publications)
 
 	Assert(list_length(publications) > 0);
 
-	initStringInfo(&cmd);
-	appendStringInfoString(&cmd, "SELECT DISTINCT t.schemaname, t.tablename\n"
-						   "  FROM pg_catalog.pg_publication_tables t\n"
-						   " WHERE t.pubname IN (");
+	initStringInfo(&pub_names);
 	first = true;
 	foreach(lc, publications)
 	{
@@ -1498,14 +1496,35 @@ fetch_table_list(WalReceiverConn *wrconn, List *publications)
 		if (first)
 			first = false;
 		else
-			appendStringInfoString(&cmd, ", ");
+			appendStringInfoString(&pub_names, ", ");
 
-		appendStringInfoString(&cmd, quote_literal_cstr(pubname));
+		appendStringInfoString(&pub_names, quote_literal_cstr(pubname));
 	}
-	appendStringInfoChar(&cmd, ')');
+
+	/*
+	 * Get the list of tables from publisher, the partition table whose
+	 * ancestor is also in this list should be ignored, otherwise the initial
+	 * data in the partition table would be replicated twice.
+	 */
+	initStringInfo(&cmd);
+	appendStringInfo(&cmd, "WITH pub_tabs AS(\n"
+					 " SELECT DISTINCT N.nspname, C.oid, C.relname, C.relispartition\n"
+					 "  FROM pg_publication P,\n"
+					 "       LATERAL pg_get_publication_tables(P.pubname) GPT,\n"
+					 "       pg_class C JOIN pg_namespace N ON (N.oid = C.relnamespace)\n"
+					 "  WHERE C.oid = GPT.relid AND P.pubname IN ( %s )\n"
+					 ")\n"
+					 "SELECT DISTINCT pub_tabs.nspname, pub_tabs.relname\n"
+					 " FROM pub_tabs\n"
+					 " WHERE (pub_tabs.relispartition IS FALSE\n"
+					 "  OR NOT EXISTS (SELECT 1 FROM pg_partition_ancestors(pub_tabs.oid) as pa\n"
+					 "                  WHERE pa.relid IN (SELECT pub_tabs.oid FROM pub_tabs)\n"
+					 "                   AND pa.relid != pub_tabs.oid))\n",
+					 pub_names.data);
 
 	res = walrcv_exec(wrconn, cmd.data, 2, tableRow);
 	pfree(cmd.data);
+	pfree(pub_names.data);
 
 	if (res->status != WALRCV_OK_TUPLES)
 		ereport(ERROR,
diff --git a/src/test/subscription/t/013_partition.pl b/src/test/subscription/t/013_partition.pl
index dfe2cb6dea..e79afcc50f 100644
--- a/src/test/subscription/t/013_partition.pl
+++ b/src/test/subscription/t/013_partition.pl
@@ -6,7 +6,7 @@ use strict;
 use warnings;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 71;
+use Test::More tests => 69;
 
 # setup
 
@@ -471,12 +471,15 @@ $node_subscriber2->safe_psql('postgres',
 $node_subscriber2->safe_psql('postgres',
 	"CREATE TABLE tab3_1 (a int PRIMARY KEY, c text DEFAULT 'sub2_tab3_1', b text)"
 );
+
+# Note: We only create one table for the partitioned table (tab4) here. Because
+# we specify option "publish_via_partition_root" (see pub_all and
+# pub_lower_level above), all data should be replicated to the partitioned
+# table. So we do not need to create table for the partition table.
 $node_subscriber2->safe_psql('postgres',
 	"CREATE TABLE tab4 (a int PRIMARY KEY)"
 );
-$node_subscriber2->safe_psql('postgres',
-	"CREATE TABLE tab4_1 (a int PRIMARY KEY)"
-);
+
 # Publication that sub2 points to now publishes via root, so must update
 # subscription target relations.
 $node_subscriber2->safe_psql('postgres',
@@ -546,10 +549,6 @@ $result = $node_subscriber2->safe_psql('postgres',
 	"SELECT a FROM tab4 ORDER BY 1");
 is( $result, qq(0), 'inserts into tab4 replicated');
 
-$result = $node_subscriber2->safe_psql('postgres',
-	"SELECT a FROM tab4_1 ORDER BY 1");
-is( $result, qq(), 'inserts into tab4_1 replicated');
-
 # now switch the order of publications in the list, try again, the result
 # should be the same (no dependence on order of pulications)
 $node_subscriber2->safe_psql('postgres',
@@ -574,10 +573,6 @@ $result = $node_subscriber2->safe_psql('postgres',
 is( $result, qq(0
 1), 'inserts into tab4 replicated');
 
-$result = $node_subscriber2->safe_psql('postgres',
-	"SELECT a FROM tab4_1 ORDER BY 1");
-is( $result, qq(), 'inserts into tab4_1 replicated');
-
 # update (replicated as update)
 $node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 6 WHERE a = 5");
 $node_publisher->safe_psql('postgres', "UPDATE tab2 SET a = 6 WHERE a = 5");
-- 
2.23.0.windows.1