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

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.

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

Best regards,
Hou zj