RE: why can't a table be part of the same publication as its schema

On Thursday, September 15, 2022 10:48 AM houzj(dot)fnst(at)fujitsu(dot)com wrote:
>
> On Thursday, September 15, 2022 3:37 AM Peter Eisentraut
> <peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
>
> Hi,
>
> >
> > On 14.09.22 07:10, houzj(dot)fnst(at)fujitsu(dot)com wrote:
> > > After applying the patch, we support adding a table with column list
> > > along with the table's schema[1], and it will directly apply the
> > > column list in the logical replication after applying the patch.
> > >
> > > [1]--
> > > CREATE PUBLICATION pub FOR TABLE public.test(a), FOR ALL TABLES IN
> > > SCHEMA public;
> > > -----
> > >
> > > If from the point of view of consistency, for column list, we could
> > > report an ERROR because we currently don't allow using different
> > > column lists for a table. Maybe an ERROR like:
> > >
> > > "ERROR: cannot use column for table x when the table's schema is
> > > also in the
> > publication"
> > >
> > > But if we want to report an ERROR for column list in above case. We
> > > might need to restrict the ALTER TABLE SET SCHEMA as well because
> > > user could move a table which is published with column list to a
> > > schema that is also published in the publication, so we might need
> > > to add some similar check(which is removed in Vignesh's patch) to
> > > tablecmd.c to
> > disallow this case.
> > >
> > > Another option could be just ingore the column list if table's
> > > schema is also part of publication. But it seems slightly
> > > inconsistent with the rule that we disallow using different column list for a
> table.
> >
> > Ignoring things doesn't seem like a good idea.
> >
> > A solution might be to disallow adding any schemas to a publication if
> > column lists on a table are specified.
>
> Thanks for the suggestion. If I understand correctly, you mean we can disallow
> publishing a table with column list and any schema(a schema that the table
> might not be part of) in the same publication[1].
>
> something like--
> [1]CREATE PUBLICATION pub FOR TABLE public.test(a), ALL TABLES IN SCHEMA
> s2;
> ERROR: "cannot add schema to publication when column list is used in the
> published table"
> --
>
> Personally, it looks acceptable to me as user can anyway achieve the same
> purpose by creating serval publications and combine it and we can save the
> restriction at ALTER TABLE SET SCHEMA. Although it restricts some cases.
> I will post a top-up patch about this soon.
>
>
> About the row filter handling, maybe we don't need to restrict row filter like
> above ? Because the rule is to simply merge the row filter with 'OR' among
> publications, so it seems we could ignore the row filter in the publication when
> the table's schema is also published in the same publication(which means no
> filter).

Attach the new version patch which added suggested restriction for column list
and merged Vignesh's patch.

Some other document might need to be updated. I will update them soon.

Best regards,
Hou zj