Re: Added schema level support for publication.

On Sun, Aug 15, 2021 at 1:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Peter Eisentraut <peter.eisentraut@enterprisedb.com> writes:
> > I think the strict separation between publication-for-tables vs.
> > publication-for-schemas is a mistake.  Why can't I have a publication
> > that publishes tables t1, t2, t3, *and* schemas s1, s2, s3.  Also note
> > that we have a pending patch to add sequences support to logical
> > replication.  So eventually, a publication will be able to contain a
> > bunch of different objects of different kinds.
>
> This seems like it's going to create a mess, because the meaning of
> "include schema S" will change over time as we add more features.
> That is, with the present patch (I suppose, haven't read it) we have
> "schema S" meaning "publish all tables in schema S".  When that other
> patch lands, presumably that same publication definition would start
> meaning "publish all tables and sequences in schema S".  And a few years
> down the road it might start meaning something else again.  That sounds
> like the sort of potentially app-breaking change that we don't like
> to make.
>
> We could avoid that bug-in-waiting if the syntax were more like
> "FOR ALL TABLES IN SCHEMA s", which could later extend to
> "FOR ALL SEQUENCES IN SCHEMA s", etc.  This is then a very clean
> intermediate step between publishing one table and "FOR ALL TABLES"
> without a schema limitation.
>
> I tend to agree that a single publication should be able to incorporate
> any of these options.
>

How about if the improved syntax from Tom Lane [1] also allowed an
"AND" keyword for combining whatever you wish?

Then the question from Peter E. [2] "Why can't I have a publication
that publishes tables t1, t2, t3, *and* schemas s1, s2, s3." would
have an intuitive solution like:

CREATE PUBLICATION pub1
FOR TABLE t1,t2,t3 AND
FOR ALL TABLES IN SCHEMA s1,s2,s3;

------
[1] https://www.postgresql.org/message-id/155565.1628954580%40sss.pgh.pa.us
[2] https://www.postgresql.org/message-id/4fb39707-dca9-1563-4482-b7a8315c36ca%40enterprisedb.com

Kind Regards,
Peter Smith.
Fujitsu Australia