Re: Identify missing publications from publisher while create/alter subscription. - Mailing list pgsql-hackers

On Mon, Jan 25, 2021 at 5:18 PM japin <japinli@hotmail.com> wrote:
>
>
> On Mon, 25 Jan 2021 at 17:18, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
> > On Mon, Jan 25, 2021 at 2:42 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >>
> >> On Mon, Jan 25, 2021 at 1:10 PM vignesh C <vignesh21@gmail.com> wrote:
> >> >
> >> > On Thu, Jan 21, 2021 at 10:21 PM Bharath Rupireddy
> >> > <bharath.rupireddyforpostgres@gmail.com> wrote:
> >> > >
> >> > > On Thu, Jan 21, 2021 at 6:56 PM vignesh C <vignesh21@gmail.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.

Please start a new thread for this, let's discuss this separately.

Regards,
Vignesh
EnterpriseDB: http://www.enterprisedb.com