RE: Data is copied twice when specifying both child and parent table in publication - Mailing list pgsql-hackers

On Sun, Apr 24, 2022 2:16 PM Wang, Wei/王 威 <wangw.fnst@fujitsu.com> wrote:
> 
> Attach the new patches.[suggestions by Amit-San]
> The patch for HEAD:
> 1. Add a new function to get tables info by a publications array.
> The patch for REL14:
> 1. Use an alias to make the statement understandable. BTW, I adjusted the
> alignment.
> 2. Improve the test cast about the column list and row filter to cover this bug.
> 

Thanks for your patches.

Here's a comment on the patch for REL14.

+    appendStringInfo(&cmd, "SELECT DISTINCT ns.nspname, c.relname\n"
+                     " FROM pg_catalog.pg_publication_tables t\n"
+                     "      JOIN pg_catalog.pg_namespace ns\n"
+                     "         ON ns.nspname = t.schemaname\n"
+                     "      JOIN pg_catalog.pg_class c\n"
+                     "         ON c.relname = t.tablename AND c.relnamespace = ns.oid\n"
+                     " WHERE t.pubname IN (%s)\n"
+                     " AND (c.relispartition IS FALSE\n"
+                     "      OR NOT EXISTS\n"
+                     "        ( SELECT 1 FROM pg_partition_ancestors(c.oid) as relid\n"
+                     "          WHERE relid IN\n"
+                     "            (SELECT DISTINCT (schemaname || '.' || tablename)::regclass::oid\n"
+                     "             FROM pg_catalog.pg_publication_tables t\n"
+                     "             WHERE t.pubname IN (%s))\n"
+                     "          AND relid != c.oid))\n",
+                     pub_names.data, pub_names.data);

I think we can use an alias like 'pa' for pg_partition_ancestors, and modify the SQL as follows. 

+    appendStringInfo(&cmd, "SELECT DISTINCT ns.nspname, c.relname\n"
+                     " FROM pg_catalog.pg_publication_tables t\n"
+                     "      JOIN pg_catalog.pg_namespace ns\n"
+                     "         ON ns.nspname = t.schemaname\n"
+                     "      JOIN pg_catalog.pg_class c\n"
+                     "         ON c.relname = t.tablename AND c.relnamespace = ns.oid\n"
+                     " WHERE t.pubname IN (%s)\n"
+                     " AND (c.relispartition IS FALSE\n"
+                     "      OR NOT EXISTS\n"
+                     "        ( SELECT 1 FROM pg_partition_ancestors(c.oid) pa\n"
+                     "          WHERE pa.relid IN\n"
+                     "            (SELECT DISTINCT (t.schemaname || '.' || t.tablename)::regclass::oid\n"
+                     "             FROM pg_catalog.pg_publication_tables t\n"
+                     "             WHERE t.pubname IN (%s))\n"
+                     "          AND pa.relid != c.oid))\n",
+                     pub_names.data, pub_names.data);

Regards,
Shi yu