Re: Support ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION ... syntax

On Wed, Jan 27, 2021 at 2:57 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> On Wed, Jan 27, 2021 at 2:30 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Tue, Jan 26, 2021 at 9:18 AM japin <japinli(at)hotmail(dot)com> wrote:
> > >
> > >
> > > When I read the discussion in [1], I found that update subscription's publications
> > > is complicated.
> > >
> > > For example, I have 5 publications in subscription.
> > >
> > > CREATE SUBSCRIPTION mysub1 CONNECTION 'host=localhost port=5432 dbname=postgres'
> > > PUBLICATION mypub1, mypub2, mypub3, mypub4, mypub5;
> > >
> > > If I want to drop "mypub4", we should use the following command:
> > >
> > > ALTER SUBSCRIPTION mysub1 SET PUBLICATION mypub1, mypub2, mypub3, mypub5;
> > >
> > > Also, if I want to add "mypub7" and "mypub8", it will use:
> > >
> > > ALTER SUBSCRIPTION mysub1 SET PUBLICATION mypub1, mypub2, mypub3, mypub5, mypub7, mypub8;
> > >
> > > Attached implement ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION ... syntax, for the above
> > > two cases, we can use the following:
> > >
> > > ALTER SUBSCRIPTION mysub1 DROP PUBLICATION mypub4;
> > >
> > > ALTER SUBSCRIPTION mysub1 DROP PUBLICATION mypub7, mypub8;
> > >
> > > I think it's more convenient. Any thoughts?
> > >
> >
> > While the new proposed syntax does seem to provide some ease for users
> > but it has nothing which we can't do with current syntax. Also, in the
> > current syntax, there is an additional provision for refreshing the
> > existing publications as well. So, if the user has to change the
> > existing subscription such that it has to (a) add new publication(s),
> > (b) remove some publication(s), (c) refresh existing publication(s)
> > then all can be done in one command whereas with your new proposed
> > syntax user has to write three separate commands.
>
> IIUC the initial patch proposed here, it does allow ALTER SUBSCRIPTION
> mysub1 ADD/DROP PUBLICATION mypub4 WITH (refresh = true);. Isn't this
> option enough to achieve what we can with ALTER SUBSCRIPTION mysub1
> SET PUBLICATION mypub1, mypub2 WITH (refresh = true);? Am I missing
> something here?
>

I feel the SET syntax would allow refreshing existing publications as
well whereas, in Add, it will be only for new Publications.

--
With Regards,
Amit Kapila.