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

On Fri, Jan 22, 2021 at 12:14 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> On Fri, Jan 22, 2021 at 10:14 AM japin <japinli(at)hotmail(dot)com> wrote:
> > > 2) Can't we know whether the publications exist on the publisher with
> > > the existing (or modifying it a bit if required) query in
> > > fetch_table_list(), so that we can avoid making another connection to
> > > the publisher system from the subscriber?
> >
> > IIUC, the patch does not make another connection, it just execute a new
> > query in already connection. If we want to check publication existence
> > for ALTER SUBSCRIPTION ... SET PUBLICATION ... WITH (refresh = false)
> > we should make another connection.
>
> Actually, I meant that we can avoid submitting another SQL query to
> the publisher if we could manage to submit a single query that first
> checks if a given publication exists in pg_publication and if yes
> returns the tables associated with it from pg_publication_tables. Can
> we modify the existing query in fetch_table_list that gets only the
> table list from pg_publcation_tables to see if the given publication
> exists in the pg_publication?
>
When I was implementing this, I had given it a thought on this. To do
that we might need some function/procedure to do this. I felt this
approach is more simpler and chose this approach.
Thoughts?

> Yes you are right, if we were to check the existence of publications
> provided with ALTER SUBSCRIPTION statements, we need to do
> walrcv_connect, walrcv_exec. We could just call a common function from
> there.
>
Yes I agree this should be done in ALTER SUBSCRIPTION SET PUBLICATION
case also, currently we do if refresh is enabled, it should also be
done in ALTER SUBSCRIPTION mysub SET PUBLICATION mypub WITH (REFRESH =
FALSE) also. I will include this in my next version of the patch.

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