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

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).

Best regards,
Hou zj