Re: pg_get_publication_tables() output duplicate relid

On Wed, Nov 17, 2021 at 12:15 PM houzj(dot)fnst(at)fujitsu(dot)com
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> 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 wrote:
> > > 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 ?

I don't think creating or attaching a partition of a table that is
present in a publish_via_partition_root=false actually adds the
partition to pg_publication_rel, the base catalog. A partition's
membership in the publication is implicit, unless of course you add it
to the publication explicitly, like all the examples we have been
discussing. I guess we're only arguing about the problems with the
pg_publication_tables view, which does expand the partitioned table to
show the partitions that are not otherwise not present in the base
catalog.

--
Amit Langote
EDB: http://www.enterprisedb.com