Re: Added schema level support for publication.

On Sat, Aug 14, 2021 at 3:02 PM Peter Eisentraut <
peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
>
> On 13.08.21 04:59, Amit Kapila wrote:
> >> Even if we drop all tables added to the publication from it, 'pubkind'
> >> doesn't go back to 'empty'. Is that intentional behavior? If we do
> >> that, we can save the lookup of pg_publication_rel and
> >> pg_publication_schema in some cases, and we can switch the publication
> >> that was created as FOR SCHEMA to FOR TABLE and vice versa.
> >>
> > Do we really want to allow users to change a publication that is FOR
> > SCHEMA to FOR TABLE? I see that we don't allow to do that FOR TABLES.
> > postgres=# Alter Publication pub add table tbl1;
> > ERROR: publication "pub" is defined as FOR ALL TABLES
> > DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES
publications.
>
> 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.

Thanks for the feedback, now the same publication can handle schemas as
well as tables, and can be extended further to other objects. This is
handled in the v21 patch attached at [1]. It is supported by the following
syntax:
CREATE PUBLICATION pub FOR ALL TABLES IN SCHEMA s1,s2,s3, TABLE t1,t2,t3;

[1] -
https://www.postgresql.org/message-id/CALDaNm2iKJvSdCyh0S%2BwYgFjMNB4hu3kYjk%3DYrEkpqTJY9zW%2Bw%40mail.gmail.com

Regards,
Vignesh