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

On Thu, Sep 15, 2022 at 8:18 AM houzj(dot)fnst(at)fujitsu(dot)com
<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:
> > > 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.
>

Yeah, I agree that it restricts more cases for how different
combinations can be specified for a publication but OTOH it helps to
uplift restriction in ALTER TABLE ... SET SCHEMA which seems like a
good trade-off.

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

Yeah, this is what we are doing internally when combining multiple
publications but let me explain with an example the case of a single
publication so that if anybody has any objections to it, we can
discuss the same.

Case-1: When row filter is specified *without* ALL TABLES IN SCHEMA clause
postgres=# create table t1(c1 int, c2 int, c3 int);
CREATE TABLE
postgres=# create publication pub1 for table t1 where (c1 > 10);
CREATE PUBLICATION
postgres=# select pubname, schemaname, tablename, rowfilter from
pg_publication_tables;
pubname | schemaname | tablename | rowfilter
---------+------------+-----------+-----------
pub1 | public | t1 | (c1 > 10)
(1 row)

Case-2: When row filter is specified *with* ALL TABLES IN SCHEMA clause
postgres=# create schema s1;
CREATE SCHEMA
postgres=# create table s1.t2(c1 int, c2 int, c3 int);
CREATE TABLE
postgres=# create publication pub2 for table s1.t2 where (c1 > 10),
all tables in schema s1;
CREATE PUBLICATION
postgres=# select pubname, schemaname, tablename, rowfilter from
pg_publication_tables;
pubname | schemaname | tablename | rowfilter
---------+------------+-----------+-----------
pub1 | public | t1 | (c1 > 10)
pub2 | s1 | t2 |
(2 rows)

So, for case-2, the rowfilter is not considered. Note, case-2 was not
possible before the patch which is discussed here and after the patch,
the behavior will be the same as we have it before when we combine
publications.

--
With Regards,
Amit Kapila.