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

On Tuesday, September 13, 2022 12:40 PM Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote:
>
> At Mon, 12 Sep 2022 04:26:48 +0000, "houzj(dot)fnst(at)fujitsu(dot)com"
> <houzj(dot)fnst(at)fujitsu(dot)com> wrote in
> > On Monday, September 12, 2022 1:08 AM Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> > > > > On Sep 10, 2022, at 4:17 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > > >
> > > >>> I don't understand why we
> > > >>> used this ALL TABLES IN SCHEMA language.
> > > >>
> > > >> The conversation, as I recall, was that "ADD SCHEMA foo" would
> > > >> only mean
> > > all tables in foo, until publication of other object types became
> > > supported, at which point "ADD SCHEMA foo" would suddenly mean more than it did before.
> > > People might find that surprising, so the "ALL TABLES IN" was
> > > intended to future-proof against surprising behavioral changes.
> > > >
> > > > If I encountered this syntax in a vacuum, that's not what I would
> > > > think. I would think that ADD ALL TABLES IN SCHEMA meant add all
> > > > the tables in the schema to the publication one by one as
> > > > individual objects
> > >
> > > Yes, it appears the syntax was chosen to avoid one kind of
> > > confusion, but created another kind. Per the docs on this feature:
> > >
> > > FOR ALL TABLES IN SCHEMA
> > > Marks the publication as one that replicates changes for all
> > > tables in the specified list of schemas, including tables created in the future.
> > >
> > > Like you, I wouldn't expect that definition, given the behavior of
> > > GRANT with respect to the same grammatical construction.
> >
> > I'm a bit unsure if it should be compared to GRANT. Because even if we
> > chose "ALTER PUBLICATION p1 { ADD | DROP } SCHEMA name", it's also not
> > consistent with the meaning of GRANT ON SCHEMA, as GRANT ON SCHEMA
> > doesn't grant rights on the tables within schema if I understand correctly.
> >
> > I feel we'd better compare the syntax with the existing publication command:
> > FOR ALL TABLES. If you create a publication FOR ALL TABLES, it means
> > publishing all the tables in the database *including* tables created
> > in the future. I think both the syntax and meaning of ALL TABLES IN
> > SCHEMA are consistent with the existing FOR ALL TABLES.
>
> IMHO, I feel closer to Robert. "ALL TABLES IN SCHEMA" sounds like the
> concrete tables at the time of invocation. While I agree that it is not directly
> comparable to GRANT, but if I see "ALTER PUBLICATION p1 ADD SCHEMA s1", I
> automatically translate that into "all tables in the schema s1 at the time of using
> this publication". At least, it would cause less confusion when it were "ALT PUB
> p1 DROP SCEMA s1" aginst "DROP ALL TABLES IN SCHEMA s1".
>
> However..
>
> > And the behavior is clearly documented, so personally I think it's fine.
> > https://www.postgresql.org/docs/devel/sql-createpublication.html
> > --
> > FOR ALL TABLES
> > Marks the publication as one that replicates changes for all tables in the
> database, including tables created in the future.
> > FOR ALL TABLES IN SCHEMA
> > Marks the publication as one that replicates changes for all tables in the
> specified list of schemas, including tables created in the future.
> > --
> >
> > Besides, as mentioned(and suggested by Tom[1]), we might support
> > publishing SEQUENCE(or others) in the future. It would give more
> > flexibility to user if we have another FOR ALL SEQUENCES(or other objects) IN
> SCHEMA.
> >
> > [1]
> >
> https://www.postgresql.org/message-id/155565.1628954580%40sss.pgh.pa.u
> > s
>
> Fair point. Should be stupid, but how about the following?
>
> CREATE PUBLICATION p1 FOR TABLES * IN SCHEMA s1;
> DROP PUBLICATION p1 FOR TABLES * IN SCHEMA s1;
> ATLER PUBLICATION p1 ADD TABLES * IN SCHEMA s1; ALTER PUBLICATION
> p1 DROP TABLES * IN SCHEMA s1;
>
> This is an analog of synchronous_standby_names. But I'm not sure a bare
> asterisc can appear there.. We could use ANY instead?
>
> CREATE PUBLICATION p1 FOR TABLES ANY IN SCHEMA s1; ...

Thanks for the suggestions. But personally, I am not sure if this is better the
current syntax as it seems syntactically inconsistent with the existing "FOR
ALL TABLES". Also, the behavior to include future tables is consistent with FOR
ALL TABLES.

Best regards,
Hou zj