Re: Retry Cached Remote Connections for postgres_fdw in case remote backend gets killed/goes away

On Tue, Sep 29, 2020 at 10:01 PM Fujii Masao
<masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>
> > I think this is okay, because pg_terminate_backend() sends SIGTERM to
> > the backend, and upon receiving SIGTERM the signal handler die() will
> > be called and since there is no query being executed on the backend by
> > the time SIGTERM is received, it will exit immediately. Thoughts?
>
> Yeah, basically you're right. But that backend *can* still be running
> when the subsequent test query starts. I'm wondering if wait_pid()
> (please see regress.c and sql/dblink.sql) should be used to ensure
> the target backend disappeared.
>

I think wait_pid() is not a generic function, and I'm unable to use
that inside postgres_fdw.sql. I think I need to recreate that function
for postgres_fdw.sql. For dblink, it's being created as part of
paths.source. Could you help me in doing so?

And another way, if we don't want to use wait_pid() is to have a
plpgsql stored procedure, that in a loop keeps on checking for the
backed pid from pg_stat_activity, exit when pid is 0. and then proceed
to issue the next foreign table query. Thoughts?

mypid = -1;
while (mypid != 0)
SELECT pid INTO mypid FROM pg_stat_activity WHERE backend_type =
'client backend' AND application_name = 'fdw_retry_check';

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