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

On 2020/09/30 21:02, Bharath Rupireddy wrote:
> 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?

+1 for this approach! We can use plpgsql or DO command.

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

Or we can just wait for the number of processes with
appname='fdw_retry_check' to be zero rather than checking the pid.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION