Re: pg_get_publication_tables() output duplicate relid - Mailing list pgsql-hackers

From Amit Langote
Subject Re: pg_get_publication_tables() output duplicate relid
Date
Msg-id CA+HiwqED+GB5_GvHUoXcW=zZSSj-+VxZUqMVMRyA57vkDUbR_g@mail.gmail.com
Whole thread Raw
In response to RE: pg_get_publication_tables() output duplicate relid  ("houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>)
List pgsql-hackers
On Thu, Nov 18, 2021 at 9:33 houzj.fnst@fujitsu.com <houzj.fnst@fujitsu.com> wrote:
On Wed, Nov 17, 2021 2:18 PM Amit Langote <amitlangote09@gmail.com> wrote:
> On Wed, Nov 17, 2021 at 12:15 PM houzj.fnst@fujitsu.com
> <houzj.fnst@fujitsu.com> wrote:
> > On Wed, Nov 17, 2021 10:47 AM Amit Kapila <amit.kapila16@gmail.com>
> wrote:
> > > On Tue, Nov 16, 2021 at 7:21 AM houzj.fnst@fujitsu.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.

Maybe I didn't make it clear, I was trying to explain that it would be
complicated if we want to completely disallow specifying both child and parent
table in the publication because of the ATTACH case I gave. In other words, I
think it's fine to specify both child and parent table in the publication.

Ah okay.  I see your point.  A table that was not once a partition would be in the catalog along with the unrelated parent table and if we were to enforce the rule that a parent and a partition cannot be in the catalog at the same time, we’d need to delete that table’s catalog record on ATTACHing it as a partition.

Thanks.
--

pgsql-hackers by date:

Previous
From: "Bossart, Nathan"
Date:
Subject: Re: Improving psql's \password command
Next
From: Peter Smith
Date:
Subject: Re: row filtering for logical replication