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.fujii@oss.nttdata.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