Re: Added schema level support for publication.

On Wed, Sep 29, 2021 at 11:59 AM houzj(dot)fnst(at)fujitsu(dot)com
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
>
> On Wed, Sep 29, 2021 12:34 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> > On Wed, Sep 29, 2021 at 9:07 AM houzj(dot)fnst(at)fujitsu(dot)com
> > <houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> > >
> > > On Tues, Sep 28, 2021 10:46 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > > Attached v34 patch has the changes for the same.
> > >
> > > 3)
> > > + /*
> > > + * Check if setting the relation to a different schema will result in the
> > > + * publication having schema and same schema's table in the
> > publication.
> > > + */
> > > + if (stmt->objectType == OBJECT_TABLE)
> > > + {
> > > + ListCell *lc;
> > > + List *schemaPubids =
> > GetSchemaPublications(nspOid);
> > > + foreach(lc, schemaPubids)
> > > + {
> > > + Oid pubid = lfirst_oid(lc);
> > > + if (list_member_oid(GetPublicationRelations(pubid,
> > PUBLICATION_PART_ALL),
> > > + relid))
> > > + ereport(ERROR,
> > >
> > > How about we check this case like the following ?
> > >
> > > List *schemaPubids = GetSchemaPublications(nspOid);
> > > List *relPubids = GetRelationPublications(RelationGetRelid(rel));
> > > if (list_intersection(schemaPubids, relPubids))
> > > ereport(ERROR, ...
> > >
> >
> > Won't this will allow changing one of the partitions for which only partitioned
> > table is part of the target schema?
>
> I think it still disallow changing partition's schema to the published one.
> I tested with the following SQLs.
> -----
> create schema sch1;
> create schema sch2;
> create schema sch3;
>
> create table sch1.tbl1 (a int) partition by range ( a );
> create table sch2.tbl1_part1 partition of sch1.tbl1 for values from (1) to (101);
> create table sch3.tbl1_part2 partition of sch1.tbl1 for values from (101) to (200);
> create publication pub for ALL TABLES IN schema sch1, TABLE sch2.tbl1_part1;
> alter table sch2.tbl1_part1 set schema sch1;
> ---* It will report an error here *
> -----
>

Use all steps before "create publication" and then try below. These
will give an error with the patch proposed but if I change it to what
you are proposing then it won't give an error.
create publication pub for ALL TABLES IN schema sch2, Table sch1.tbl1;
alter table sch3.tbl1_part2 set schema sch2;

But now again thinking about it, I am not sure if we really want to
give error in this case. What do you think? Also, if we use
list_intersection trick, then how will we tell the publication due to
which this problem has occurred, or do you think we should leave that
as an exercise for the user?

--
With Regards,
Amit Kapila.