Re: Added schema level support for publication.

On Thu, Oct 21, 2021 at 3:29 PM Greg Nancarrow <gregn4422(at)gmail(dot)com> wrote:
>
> On Thu, Oct 21, 2021 at 3:25 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > Attached v44 patch as the fixes for the same.
> >
>
> In the v44-0001 patch, I have some doubts about the condition guarding
> the following code in pg_get_publication_tables():
>
> + if (schemarelids)
> + {
> + /*
> + * 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 data of the child table
> + * to be double-published on the subscriber side.
> + *
> + * XXX As of now, we do this when a publication has associated
> + * schema or for all tables publication. See
> + * GetAllTablesPublicationRelations().
> + */
> + tables = list_concat_unique_oid(relids, schemarelids);
> + if (publication->pubviaroot)
> + tables = filter_partitions(tables);
> + }
>
> Shouldn't a partition be filtered out only if publication->pubviaroot
> and the partition belongs to a schema (i.e. ALL TABLES IN SCHEMA)
> included in the publication?
> The current code seems to filter out partitions of partitioned tables
> included in the publication if ANY schemas are included as part of the
> publication (e.g. which could be a schema not including any
> partitioned tables or partitions).

I could reproduce the issue by using the following test:
--- Setup
create schema sch1;
create schema sch2;
create table sch1.tbl1 (a int) partition by range (a);
create table sch2.tbl1_part1 partition of sch1.tbl1 for values from (1) to (10);
create table sch2.tbl1_part2 partition of sch1.tbl1 for values from
(10) to (20);
create schema sch3;
create table sch3.t1(c1 int);

--- Publication
create publication pub1 for all tables in schema sch3, table
sch1.tbl1, table sch2.tbl1_part1 with ( publish_via_partition_root
=on);
insert into sch1.tbl1 values(1);
insert into sch1.tbl1 values(11);
insert into sch3.t1 values(1);

---- Subscription
CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres host=localhost
port=5432' PUBLICATION pub1;

The patch posted at [1] has the fix for the same.
[1] - https://www.postgresql.org/message-id/CALDaNm1onqBEr0WE_e7%3DCNw3bURfrGRmbMjX31d-nx3FGLS10A%40mail.gmail.com

Regards,
Vignesh