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

On Tue, Apr 28, 2022 9:22 AM Shi, Yu/侍 雨 <shiy(dot)fnst(at)cn(dot)fujitsu(dot)com> wrote:
> Thanks for your patches.
>
> Here's a comment on the patch for REL14.
Thanks for your comments.

> + 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);
Fix it.

In addition, I try to modify the approach for the HEAD.
I enhance the API of function pg_get_publication_tables. Change the parameter
type from 'text' to 'any'. Then we can use this function to get tables from one
publication or an array of publications. Any thoughts on this approach?

Attach new patches.
The patch for HEAD:
1. Modify the approach. Enhance the API of function pg_get_publication_tables to
handle one publication or an array of publications.
The patch for REL14:
1. Improve the table sync SQL. [suggestions by Shi yu]

Regards,
Wang wei