v3-0001-fix-double-publish.patch

From 30c911c6e6223cf4f9d2141361b8164886857b18 Mon Sep 17 00:00:00 2001
From: "houzj.fnst" <houzj.fnst@cn.fujitsu.com>
Date: Mon, 18 Oct 2021 11:46:44 +0800
Subject: [PATCH] fix double publish

if publish_via_partition_root is true, then the child table's data will be
copied twice if adding both child and parent table to the publication. The
reason is that the subscriber will fetch the table list from publisher's
pg_publication_tables view to do the table synchronization. But the view always
show both child and parent table which cause the extra synchronization
for the child table.

Fix it by making pg_publication_tables only show parent table if both parent
and child exists in the publication.

---
 src/backend/catalog/pg_publication.c      | 52 +++++++++++++++++++++++++++++++
 src/test/regress/expected/publication.out |  9 ++++++
 src/test/regress/sql/publication.sql      |  4 +++
 3 files changed, 65 insertions(+)

diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 9cd0c82..bd0db8c 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -106,6 +106,45 @@ is_publishable_class(Oid relid, Form_pg_class reltuple)
 }
 
 /*
+ * Filter out the partitions whose parent tables was also specified in
+ * the publication.
+ */
+static List *
+filter_out_partitions(List *relids)
+{
+	List	   *result = NIL;
+	ListCell   *lc;
+	ListCell   *lc2;
+
+	foreach(lc, relids)
+	{
+		bool		skip = false;
+		List	   *ancestors = NIL;
+		Oid			relid = lfirst_oid(lc);
+
+		if (get_rel_relispartition(relid))
+			ancestors = get_partition_ancestors(relid);
+
+		foreach(lc2, ancestors)
+		{
+			/*
+			 * Check if the parent table exists in the published table list.
+			 */
+			if (list_member_oid(relids, lfirst_oid(lc2)))
+			{
+				skip = true;
+				break;
+			}
+		}
+
+		if (!skip)
+			result = lappend_oid(result, relid);
+	}
+
+	return result;
+}
+
+/*
  * Another variant of this, taking a Relation.
  */
 bool
@@ -557,10 +596,23 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
 		if (publication->alltables)
 			tables = GetAllTablesPublicationRelations(publication->pubviaroot);
 		else
+		{
 			tables = GetPublicationRelations(publication->oid,
 											 publication->pubviaroot ?
 											 PUBLICATION_PART_ROOT :
 											 PUBLICATION_PART_LEAF);
+
+			/*
+			 * If the publication publishes partition changes via their
+			 * respective root partitioned tables, we must exclude partitions
+			 * in favor of including the root partitioned tables. Otherwise,
+			 * the function could return both the child and parent tables which
+			 * could cause the data of child table double-published in
+			 * subscriber side.
+			 */
+			if (publication->pubviaroot)
+				tables = filter_out_partitions(tables);
+		}
 		funcctx->user_fctx = (void *) tables;
 
 		MemoryContextSwitchTo(oldcontext);
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 82bce9b..5f5445a 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -165,6 +165,15 @@ HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
 ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted;
 -- works again, because update is no longer replicated
 UPDATE testpub_parted2 SET a = 2;
+-- add both child and parent table to the publication
+ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted, testpub_parted2;
+-- only show parent table when publish_via_partition_root is set
+select * from pg_publication_tables;
+      pubname      | schemaname |   tablename    
+-------------------+------------+----------------
+ testpub_forparted | public     | testpub_parted
+(1 row)
+
 DROP TABLE testpub_parted1, testpub_parted2;
 DROP PUBLICATION testpub_forparted, testpub_forparted1;
 -- Test cache invalidation FOR ALL TABLES publication
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index e5745d5..d3f2b2f 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -97,6 +97,10 @@ UPDATE testpub_parted2 SET a = 2;
 ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted;
 -- works again, because update is no longer replicated
 UPDATE testpub_parted2 SET a = 2;
+-- add both child and parent table to the publication
+ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted, testpub_parted2;
+-- only show parent table when publish_via_partition_root is set
+select * from pg_publication_tables;
 DROP TABLE testpub_parted1, testpub_parted2;
 DROP PUBLICATION testpub_forparted, testpub_forparted1;
 
-- 
2.7.2.windows.1