De-duplicate the result of pg_publication_tables view.

Amit Kapila · Amit Kapila · commit a61bff2bf479 · 2021-12-08T11:15:25.000+05:30
We show duplicate values for child tables in publications that have both child and parent tables and are published with publish_via_partition_root as false which is not what the user would expect. We decided not to backpatch this as there is no user complaint about this and it doesn't seem to be a critical issue. Author: Hou Zhijie Reviewed-by: Bharath Rupireddy, Amit Langote, Amit Kapila Discussion: https://postgr.es/m/OS0PR01MB5716E97F00732B52DC2BBC2594989@OS0PR01MB5716.jpnprd01.prod.outlook.com
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
@@ -487,6 +487,10 @@ GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
 	systable_endscan(scan);
 	table_close(pubrelsrel, AccessShareLock);
 
+	/* Now sort and de-duplicate the result list */
+	list_sort(result, list_oid_cmp);
+	list_deduplicate_oid(result);
+
 	return result;
 }
 
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
@@ -832,6 +832,14 @@ SELECT * FROM pg_publication_tables;
  pub     | sch2       | tbl1_part1
 (1 row)
 
+-- Table publication that includes both the parent table and the child table
+ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
+SELECT * FROM pg_publication_tables;
+ pubname | schemaname | tablename  
+---------+------------+------------
+ pub     | sch2       | tbl1_part1
+(1 row)
+
 DROP PUBLICATION pub;
 DROP TABLE sch2.tbl1_part1;
 DROP TABLE sch1.tbl1;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
@@ -469,6 +469,10 @@ DROP PUBLICATION pub;
 CREATE PUBLICATION pub FOR TABLE sch2.tbl1_part1 WITH (PUBLISH_VIA_PARTITION_ROOT=0);
 SELECT * FROM pg_publication_tables;
 
+-- Table publication that includes both the parent table and the child table
+ALTER PUBLICATION pub ADD TABLE sch1.tbl1;
+SELECT * FROM pg_publication_tables;
+
 DROP PUBLICATION pub;
 DROP TABLE sch2.tbl1_part1;
 DROP TABLE sch1.tbl1;

Original file line number	Diff line number	Diff line change
`@@ -487,6 +487,10 @@ GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)`
`487`	`487`	`systable_endscan(scan);`
`488`	`488`	`table_close(pubrelsrel, AccessShareLock);`
`489`	`489`
	`490`	`+ /* Now sort and de-duplicate the result list */`
	`491`	`+ list_sort(result, list_oid_cmp);`
	`492`	`+ list_deduplicate_oid(result);`
	`493`	`+`
`490`	`494`	`return result;`
`491`	`495`	`}`
`492`	`496`