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

On Fri, Jan 22, 2021 at 10:14 AM japin <japinli@hotmail.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?

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.

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