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

On Mon, 25 Jan 2021 at 17:18, Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> On Mon, Jan 25, 2021 at 2:42 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>>
>> On Mon, Jan 25, 2021 at 1:10 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>> >
>> > On Thu, Jan 21, 2021 at 10:21 PM Bharath Rupireddy
>> > <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>> > >
>> > > On Thu, Jan 21, 2021 at 6:56 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>> > > >
>> > > > Hi,
>> > > >
>> > > > Creating/altering subscription is successful when we specify a
>> > > > publication which does not exist in the publisher. I felt we should
>> > > > throw an error in this case, that will help the user to check if there
>> > > > is any typo in the create subscription command or to create the
>> > > > publication before the subscription is created.
>> > > > If the above analysis looks correct, then please find a patch that
>> > > > checks if the specified publications are present in the publisher and
>> > > > throws an error if any of the publications is missing in the
>> > > > publisher.
>> > > > Thoughts?
>> > >
>> > > I was having similar thoughts (while working on the logical
>> > > replication bug on alter publication...drop table behaviour) on why
>> > > create subscription succeeds without checking the publication
>> > > existence. I checked in documentation, to find if there's a strong
>> > > reason for that, but I couldn't. Maybe it's because of the principle
>> > > "first let users create subscriptions, later the publications can be
>> > > created on the publisher system", similar to this behaviour
>> > > "publications can be created without any tables attached to it
>> > > initially, later they can be added".
>> > >
>> > > Others may have better thoughts.
>> > >
>> > > If we do check publication existence for CREATE SUBSCRIPTION, I think
>> > > we should also do it for ALTER SUBSCRIPTION ... SET PUBLICATION.
>> > >
>> > > I wonder, why isn't dropping a publication from a list of publications
>> > > that are with subscription is not allowed?
>> > >
>> > > Some comments so far on the patch:
>> > >
>> > > 1) I see most of the code in the new function check_publications() and
>> > > existing fetch_table_list() is the same. Can we have a generic
>> > > function, with maybe a flag to separate out the logic specific for
>> > > checking publication and fetching table list from the publisher.
>> >
>> > I have made the common code between the check_publications and
>> > fetch_table_list into a common function
>> > get_appended_publications_query. I felt the rest of the code is better
>> > off kept as it is.
>> > The Attached patch has the changes for the same and also the change to
>> > check publication exists during alter subscription set publication.
>> > Thoughts?
>> >
>>
>> So basically, the create subscription will throw an error if the
>> publication does not exist. So will you throw an error if we try to
>> drop the publication which is subscribed by some subscription? I mean
>> basically, you are creating a dependency that if you are creating a
>> subscription then there must be a publication that is not completely
>> insane but then we will have to disallow dropping the publication as
>> well. Am I missing something?
>
> Do you mean DROP PUBLICATION non_existent_publication;?
>
> Or
>
> 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.

Sorry, this question is unrelated with this subject.

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