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

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

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

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center