Re: Skipping schema changes in publication

On Wed, Apr 13, 2022 at 2:40 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Apr 13, 2022 at 8:45 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > On Tue, Apr 12, 2022 at 4:46 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > I understand that part but what I pointed out was that it might be
> > > better to avoid using ADD keyword in this syntax like: ALTER
> > > PUBLICATION pub1 SKIP TABLE t1,t2;
> >
> > Currently we are supporting Alter publication using the following syntax:
> > ALTER PUBLICATION pub1 ADD TABLE t1;
> > ALTER PUBLICATION pub1 SET TABLE t1;
> > ALTER PUBLICATION pub1 DROP TABLE T1;
> > ALTER PUBLICATION pub1 ADD ALL TABLES IN SCHEMA sch1;
> > ALTER PUBLICATION pub1 SET ALL TABLES IN SCHEMA sch1;
> > ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sch1;
> >
> > I have extended the new syntax in similar lines:
> > ALTER PUBLICATION pub1 ADD SKIP TABLE t1;
> > ALTER PUBLICATION pub1 SET SKIP TABLE t1;
> > ALTER PUBLICATION pub1 DROP SKIP TABLE T1;
> >
> > I did it like this to maintain consistency.
> >
>
> What is the difference between ADD and SET variants? I understand we
> need some way to remove the SKIP table setting but not sure if DROP is
> the best alternative.
>
> The other ideas could be:
> To set skip tables: ALTER PUBLICATION pub1 SKIP TABLE t1, t2...;
> To reset skip tables: ALTER PUBLICATION pub1 SKIP TABLE; /* basically
> an empty list*/
> Yet another way to reset skip tables: ALTER PUBLICATION pub1 RESET
> SKIP TABLE; /* Here we need to introduce RESET. */
>

When you were talking about SKIP TABLE then I liked the idea of:

ALTER ... SET SKIP TABLE; /* empty list to reset the table skips */
ALTER ... SET SKIP TABLE t1,t2; /* non-empty list to replace the table skips */

But when you apply that rule to SKIP ALL TABLES IN SCHEMA, then the
reset syntax looks too awkward.

ALTER ... SET SKIP ALL TABLES IN SCHEMA; /* empty list to reset the
schema skips */
ALTER ... SET SKIP ALL TABLES IN SCHEMA s1,s2; /* non-empty list to
replace the schema skips */

~~~

IMO it might be simpler to do it like:

ALTER ... DROP SKIP; /* reset/remove the skip */
ALTER ... SET SKIP TABLE t1,t2; /* non-empty list to replace table skips */
ALTER ... SET SKIP ALL TABLES IS SCHEMA s1,s2; /* non-empty list to
replace schema skips */

I don't really think that the ALTER ... SET SKIP empty list should be
supported (because reason above)
I don't really think that the ALTER ... ADD SKIP should be supported.

===

More questions - What happens if the skip table or skip schema no
longer exists exist? Does that mean error? Maybe there is a
dependency on it but OTOH it might be annoying - e.g. to disallow a
DROP TABLE when the only dependency was that the user wanted to SKIP
it...

------
Kind Regards,
Peter Smith.
Fujitsu Australia