Re: pg_get_publication_tables() output duplicate relid

On Mon, Dec 6, 2021 at 1:59 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Fri, Dec 3, 2021 at 6:04 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > On Fri, Dec 3, 2021 at 12:37 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > On Thu, Dec 2, 2021 at 7:18 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > > > Okay, I did write a PoC patch this morning after sending out my
> > > > earlier email. I polished it a bit, which is attached.
> > >
> > > I see multiple problems with this patch and idea.
> >
> > Thanks for looking at it. Yeah, I have not looked very closely at ALL
> > TABLES [IN SCHEMA], though only because I suspected that those cases
> > deal with partitioning in such a way that the partition duplication
> > issue doesn't arise. That is, only the FOR TABLE list_of_tables and
> > ADD TABLE syntax allow for the duplication issue to occur.
> >
> > > (a) I think you
> > > forgot to deal with "All Tables In Schema" Publication which will be
> > > quite tricky to deal with during attach operation. How will you remove
> > > a particular relation from such a publication if there is a need to do
> > > so?
> >
> > Hmm, my understanding of how FOR ALL TABLES... features work is that
> > one cannot remove a particular relation from such publications?
> >
> > create schema sch;
> > create table sch.p (a int primary key) partition by list (a);
> > create table sch.p1 partition of sch.p for values in (1);
> > create table sch.p2 partition of sch.p for values in (2);
> > create table p (a int primary key) partition by list (a);
> > create table p1 partition of p for values in (1);
> > create table p2 partition of p for values in (2);
> > create publication puball for all tables;
> > create publication pubsch for all tables in schema sch;
> >
> > alter publication puball drop table p;
> > ERROR: publication "puball" is defined as FOR ALL TABLES
> > DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
> >
> > alter publication pubsch drop table sch.p;
> > ERROR: relation "p" is not part of the publication
> >
> > What am I missing?
>
> Currently, in your patch, you are trying to remove a particular
> relation/partition during attach but how will you do that if such a
> relation is part of All Tables In Schema publication?

So IIUC the scenario of concern is when a table to be attached as a
partition is in a schema that's present in pg_publication_namespace.
The only way to stop it from being published is to move it to another
schema that is not published using that publication.

I think I misunderstood how the IN SCHEMA feature works.
Specifically, I didn't know that one can add a partitioned table to
the same publication (or any table other than those in a particular
schema for that matter). Then the attached partition would still be
present in the publication by way of being part of the schema that is
present in the publication, along with the partitioned table that is
added separately.

Yes, my proposal in its current form can't prevent that kind of duplication.

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