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

On Thu, Nov 4, 2021 at 7:10 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Thu, Nov 4, 2021 at 12:23 PM Greg Nancarrow <gregn4422@gmail.com>
wrote:
> >
> > On Thu, Nov 4, 2021 at 3:13 PM Amit Kapila <amit.kapila16@gmail.com>
wrote:
> > >
> > > On further thinking about this, I think we should define the behavior
> > > of replication among partitioned (on the publisher) and
> > > non-partitioned (on the subscriber) tables a bit more clearly.
> > >
> > > - If the "publish_via_partition_root" is set for a publication then we
> > > can always replicate to the table with the same name as the root table
> > > in publisher.
> > > - If the "publish_via_partition_root" is *not* set for a publication
> > > then we can always replicate to the tables with the same name as the
> > > non-root tables in publisher.
> > >
> > > Thoughts?
> > >
> >
> > I'd adjust that wording slightly, because "we can always replicate to
> > ..." sounds a bit vague, and saying that an option is set or not set
> > could be misinterpreted, as the option could be "set" to false.
> >
> > How about:
> >
> > - If "publish_via_partition_root" is true for a publication, then data
> > is replicated to the table with the same name as the root (i.e.
> > partitioned) table in the publisher.
> > - If "publish_via_partition_root" is false (the default) for a
> > publication, then data is replicated to tables with the same name as
> > the non-root (i.e. partition) tables in the publisher.
> >
>
> Sounds good to me. If we follow this then I think the patch by Hou-San
> is good to solve the first problem as described in his last email [1]?
>
> [1] -
https://www.postgresql.org/message-id/OS0PR01MB5716C756312959F293A822C794869%40OS0PR01MB5716.jpnprd01.prod.outlook.com
>

Almost.
The patch does seem to solve that first problem (double publish on
tablesync).
I used the following test (taken from [2]), and variations of it:

--- 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;


[2] -
https://postgr.es/m/CALDaNm3vxjPMMSrVDNK0f8UWP+EQ5ry14xfEukmXsVg_UcwZNA@mail.gmail.com


However, there did still seem to be a problem, if
publish_via_partition_root is then set to false; it seems that can result
in duplicate partition entries in the pg_publication_tables view, see below
(this follows on from the test scenario given above):

postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
 pub1    | sch1       | tbl1
 pub1    | sch3       | t1
(2 rows)

postgres=#  alter publication pub1 set (publish_via_partition_root=false);
ALTER PUBLICATION
postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+------------
 pub1    | sch2       | tbl1_part1
 pub1    | sch2       | tbl1_part2
 pub1    | sch2       | tbl1_part1
 pub1    | sch3       | t1
(4 rows)

So I think the patch would need to be updated to prevent that.

Regards,
Greg Nancarrow
Fujitsu Australia