RE: Data is copied twice when specifying both child and parent table in publication

On Sun, Apr 24, 2022 2:16 PM Wang, Wei/王 威 <wangw(dot)fnst(at)fujitsu(dot)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