Re: [PATCH] postgres_fdw connection caching - cause remote sessions linger till the local session exit

Hi,

On 2020-11-06 18:56, Anastasia Lubennikova wrote:
> Status update for a commitfest entry.
> 
> This thread was inactive for a while and from the latest messages, I
> see that the patch needs some further work.
> So I move it to "Waiting on Author".
> 
> The new status of this patch is: Waiting on Author

I had a look on the initial patch and discussed options [1] to proceed 
with this issue. I agree with Bruce about idle_session_timeout, it would 
be a nice to have in-core feature on its own. However, this should be a 
cluster-wide option and it will start dropping all idle connection not 
only foreign ones. So it may be not an option for some cases, when the 
same foreign server is used for another load as well.

Regarding the initial issue I prefer point #3, i.e. foreign server 
option. It has a couple of benefits IMO: 1) it may be set separately on 
per foreign server basis, 2) it will live only in the postgres_fdw 
contrib without any need to touch core. I would only supplement this 
postgres_fdw foreign server option with a GUC, e.g. 
postgres_fdw.keep_connections, so one could easily define such behavior 
for all foreign servers at once or override server-level option by 
setting this GUC on per session basis.

Attached is a small POC patch, which implements this contrib-level 
postgres_fdw.keep_connections GUC. What do you think?

[1] 
https://www.postgresql.org/message-id/CALj2ACUFNydy0uo0JL9A1isHQ9pFe1Fgqa_HVanfG6F8g21nSQ%40mail.gmail.com


Regards
-- 
Alexey Kondratov

Postgres Professional https://www.postgrespro.com
Russian Postgres Company