Re: Skipping schema changes in publication

On Thu, Apr 14, 2022, at 10:47 AM, Peter Eisentraut wrote:
> On 12.04.22 08:23, vignesh C wrote:
> > I have also included the implementation for skipping a few tables from
> > all tables publication, the 0002 patch has the implementation for the
> > same.
> > This feature is helpful for use cases where the user wants to
> > subscribe to all the changes except for the changes present in a few
> > tables.
> > Ex:
> > CREATE PUBLICATION pub1 FOR ALL TABLES SKIP TABLE t1,t2;
> > OR
> > ALTER PUBLICATION pub1 ADD SKIP TABLE t1,t2;
>
> We have already allocated the "skip" terminology for skipping
> transactions, which is a dynamic run-time action. We are also using the
> term "skip" elsewhere to skip locked rows, which is similarly a run-time
> action. I think it would be confusing to use the term SKIP for DDL
> construction.
I didn't like the SKIP choice too. We already have EXCEPT for IMPORT FOREIGN
SCHEMA and if I were to suggest a keyword, it would be EXCEPT.

> I would also think about this in broader terms. For example, sometimes
> people want features like "all columns except these" in certain places.
> The syntax for those things should be similar.
The questions are:
What kind of issues does it solve?
Do we have a workaround for it?

> That said, I'm not sure this feature is worth the trouble. If this is
> useful, what about "whole database except these schemas"? What about
> "create this database from this template except these schemas". This
> could get out of hand. I think we should encourage users to group their
> object the way they want and not offer these complicated negative
> selection mechanisms.
I have the same impression too. We already provide a way to:

* include individual tables;
* include all tables;
* include all tables in a certain schema.

Doesn't it cover the majority of the use cases? We don't need to cover all
possible cases in one DDL command. IMO the current grammar for CREATE
PUBLICATION is already complicated after the ALL TABLES IN SCHEMA. You are
proposing to add "ALL TABLES SKIP ALL TABLES" that sounds repetitive but it is
not; doesn't seem well-thought-out. I'm also concerned about possible gotchas
for this proposal. The first command above suggests that it skips all tables in a
certain schema. What happen if I decide to include a particular table of the
skipped schema (second command)?

ALTER PUBLICATION pub1 ADD SKIP ALL TABLES IN SCHEMA s1,s2;
ALTER PUBLICATION pub1 ADD TABLE s1.foo;

Having said that I'm not wedded to this proposal. Unless someone provides
compelling use cases for this additional syntax, I think we should leave the
publication syntax as is.

--
Euler Taveira
EDB https://www.enterprisedb.com/