Re: Identify missing publications from publisher while create/alter subscription.

On Mon, Jan 25, 2021 at 5:18 PM japin <japinli(at)hotmail(dot)com> wrote:
> > Do you mean when we drop publications from a subscription? If yes, do
> > we have a way to drop a publication from the subscription? See below
> > one of my earlier questions on this.
> > "I wonder, why isn't dropping a publication from a list of
> > publications that are with subscription is not allowed?"
> > At least, I see no ALTER SUBSCRIPTION ... DROP PUBLICATION mypub1 or
> > something similar?
> >
>
> Why we do not support ALTER SUBSCRIPTION...ADD/DROP PUBLICATION? When we
> have multiple publications in subscription, but I want to add/drop a single
> publication, it is conveient. The ALTER SUBSCRIPTION...SET PUBLICATION...
> should supply the completely publications.

Looks like the way to drop/add publication from the list of
publications in subscription requires users to specify all the list of
publications currently exists +/- the new publication that needs to be
added/dropped:

CREATE SUBSCRIPTION mysub1 CONNECTION 'host=localhost port=5432
dbname=postgres' PUBLICATION mypub1, mypub2, mypu3, mypub4, mypub5;
postgres=# select subpublications from pg_subscription;
subpublications
-------------------------------------
{mypub1,mypub2,mypu3,mypub4,mypub5}
(1 row)

Say, I want to drop mypub4:

ALTER SUBSCRIPTION mysub1 SET PUBLICATION mypub1, mypub2, mypu3, mypub5;
postgres=# select subpublications from pg_subscription;
subpublications
------------------------------
{mypub1,mypub2,mypu3,mypub5}

Say, I want toa dd mypub4 and mypub6:
ALTER SUBSCRIPTION mysub1 SET PUBLICATION mypub1, mypub2, mypu3,
mypub5, mypub4, mypub6;
postgres=# select subpublications from pg_subscription;
subpublications
--------------------------------------------
{mypub1,mypub2,mypu3,mypub5,mypub4,mypub6}
(1 row)

It will be good to have something like:

ALTER SUBSCRIPTION mysub1 ADD PUBLICATION mypub1, mypub3; which will
the publications to subscription if not added previously.

ALTER SUBSCRIPTION mysub1 DROP PUBLICATION mypub1, mypub3; which will
drop the publications from subscription if they exist in the
subscription's list of publications.

But I'm really not sure why the above syntax was not added earlier. We
may be missing something here.

> Sorry, this question is unrelated with this subject.

Yes, IMO it can definitely be discussed in another thread. It will be
good to get a separate opinion for this.

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