RE: pg_get_publication_tables() output duplicate relid

On Wed, Nov 17, 2021 10:47 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Tue, Nov 16, 2021 at 7:21 AM houzj(dot)fnst(at)fujitsu(dot)com
> <houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> >
> > On Mon, Nov 15, 2021 9:42 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
> wrote:
> > > > On Mon, Nov 15, 2021 at 1:48 PM houzj(dot)fnst(at)fujitsu(dot)com
> > > > <houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> > >
> > > > > create table tbl1 (a int) partition by range (a); create table
> > > > > tbl1_part1 partition of tbl1 for values from (1) to (10); create
> > > > > table tbl1_part2 partition of tbl1 for values from (10) to (20);
> > > > > create publication pub for table tbl1, tbl1_part1 with
> > > > > (publish_via_partition_root=false);
> > >
> > > In the name of consistency, I think this situation should be an
> > > error -- I mean, if we detect that the user is trying to add both
> > > the partitioned table *and* its partition, then all manner of things
> > > are possibly going to go wrong in some way, so my inclination is to avoid it
> > > altogether.
> > >
> > > Is there any reason to allow that?
> > >
> > > If we do that, then we have to be careful with later ALTER
> > > PUBLICATION
> > > too: adding a partition is not allowed if its partitioned table is
> > > there, and adding a partitioned table should behave in some sensible
> > > way if one of its partitions is there (either removing the partition
> > > at the same time, or outright rejecting the
> > > operation.)
> >
> > Thanks for the response.
> >
> > If we decide to disallow this case, we seem need to handle some other
> > cases as well, for example: We might also need additional check when
> > ATTACH a partition, because the partition's parent table could already
> > be published in the same publication as the partition.
> >
>
> What kind of additional checks you are envisioning and why?

For example:

create table tbl1 (a int) partition by range (a);
create table tbl1_part1 (like tbl1);
create table tbl1_part2 partition of tbl1 for values from (10) to (20);
create publication pub for table
tbl1, tbl1_part1 with (publish_via_partition_root=false);

--- We might need addition check here
Alter table tbl1 ATTACH partition tbl1_part1 for values from (1) to (10);

In the above cases, 'tbl1_part1' is not a partition of 'tb1' when creating a
publication. After the ATTACH, 'tbl1_part1' would become a partition of 'tbl1'
which seems the case we want to disallow(both parent and child table in
publication). So, When ATTACH, I thought we might need to check all the parent
tables to see if any parent table is in the same publication which the table to
be attached is also belongs to. Does it make sense ?

Best regards,
Hou zj