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

On 2020/10/01 21:14, Bharath Rupireddy wrote:
> On Wed, Sep 30, 2020 at 11:32 PM Fujii Masao
> <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>>
>>> 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.
>>
>
> Used plpgsql procedure as we have to use the procedure 2 times.
>
>>
>>>
>>> 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.
>>
>
> Done.
>
> Attaching v7 patch with above changes. Please review it.

Thanks for updating the patch!

+-- committed the txn. The entry of the terminated backend from pg_stat_activity
+-- would be removed only after the txn commit.

pg_stat_clear_snapshot() can be used to reset the entry.

+ EXIT WHEN proccnt = 0;
+ END LOOP;

Isn't it better to sleep here, to avoid th busy loop?

So what I thought was something like

CREATE OR REPLACE PROCEDURE wait_for_backend_termination()
LANGUAGE plpgsql
AS $$
BEGIN
LOOP
PERFORM * FROM pg_stat_activity WHERE application_name = 'fdw_retry_check';
EXIT WHEN NOT FOUND;
PERFORM pg_sleep(1), pg_stat_clear_snapshot();
END LOOP;
END;
$$;

Regards,

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