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

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?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com