Re: Added schema level support for publication. - Mailing list pgsql-hackers

On Sat, Jan 9, 2021 at 5:21 PM vignesh C <vignesh21@gmail.com> wrote:
> > What do we do if the user Drops the schema? Do we automatically remove
> > it from the publication?
> >
> I have not yet handled this scenario yet, I will handle this and
> adding of tests in the next patch.
>
> > I see some use of such a feature but you haven't described the use
> > case or how did you arrive at the conclusion that it would be quite
> > useful?
> >
> Currently there are a couple of options "FOR All TABLES" and "FOR
> TABLE" when a user creates a publication, 1) either to subscribe to
> the changes of all the tables or 2) subscribe to a few tables. There
> is no option for users to subscribe to relations present in the
> schemas. User has to manually identify the list of tables present in
> the schema and specify the list of tables in that schema using the
> "FOR TABLE" option. Similarly if a user wants to subscribe to n number
> of schemas, the user has to do this for the required schemas, this is
> a tedious process. This feature helps the user to take care of this
> internally using schema option.

I think this feature can be useful, in case a user has a lot of tables
to publish inside a schema. Having said that, I wonder if this feature
mandates users to create the same schema with same
permissions/authorizations manually on the subscriber, because logical
replication doesn't propagate any ddl's so are the schema or schema
changes? Or is it that the list of tables from the publisher can go
into a different schema on the subscriber?

Since the schema can have other objects such as data types, functions,
operators, I'm sure with your feature, non-table objects will be
skipped.

As Amit pointed out earlier, the behaviour when schema dropped, I
think we should also consider when schema is altered, say altered to a
different name, maybe we should change that in the publication too.

In general, what happens if we have some temporary tables or foreign
tables inside the schema, will they be allowed to send the data to
subscribers?

And, with this feature, since there can be many huge tables inside a
schema, the initial table sync phase of the replication can take a
while.

Say a user has created a publication for a schema with hundreds of
tables in it, at some point later, can he stop replicating a single or
some tables from that schema?

IMO, it's better to have the syntax - CREATE PUBLICATION
production_publication FOR ALL TABLES IN SCHEMA production - just
added IN between for all tables and schema.

Say a user has a schema with 121 tables in it, and wants to replicate
only 120 or 199 or even lesser tables out of it, so can we have some
skip option to the new syntax, something like below?
CREATE PUBLICATION production_publication FOR ALL TABLES SCHEMA
production WITH skip = marketing, accounts, sales;  --> meaning is,
replicate all the tables in the schema production except marketing,
accounts, sales tables.

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com