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

On Mon, 25 Jan 2021 at 21:55, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> 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.
>

I started a new thread [1] for this, please have a look.

[1] - https://www.postgresql.org/message-id/MEYP282MB166939D0D6C480B7FBE7EFFBB6BC0@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.