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