Horton

Re: Skipping schema changes in publication

Peter Smith <smithpb2250@gmail.com>

View thread
From: Peter Smith <smithpb2250@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: vignesh C <vignesh21@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-04-13T07:35:39Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix miscellaneous issues in EXCEPT publication clause.

  2. Change syntax of EXCEPT TABLE clause in publication commands.

  3. Add support for EXCEPT TABLE in ALTER PUBLICATION.

  4. Allow table exclusions in publications via EXCEPT TABLE.

  5. Add wait_for_subscription_sync for TAP tests.

On Wed, Apr 13, 2022 at 2:40 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Wed, Apr 13, 2022 at 8:45 AM vignesh C <vignesh21@gmail.com> wrote:
> >
> > On Tue, Apr 12, 2022 at 4:46 PM Amit Kapila <amit.kapila16@gmail.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