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

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?

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