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

On Wed, 1 Jul 2020 at 18:14, Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> >
> > I've not looked at your patch deeply but if this problem is talking
> > only about postgres_fdw I think we should improve postgres_fdw, not
> > adding a GUC to the core. It’s not that all FDW plugins use connection
> > cache and postgres_fdw’s connection cache is implemented within
> > postgres_fdw, I think we should focus on improving postgres_fdw. I
> > also think it’s not a good design that the core manages connections to
> > remote servers connected via FDW. I wonder if we can add a
> > postgres_fdw option for this purpose, say keep_connection [on|off].
> > That way, we can set it per server so that remote connections to the
> > particular server don’t remain idle.
> >
>
> If I understand it correctly, your suggestion is to add
> keep_connection option and use that while defining the server object.
> IMO having keep_connection option at the server object level may not
> serve the purpose being discussed here.
> For instance, let's say I create a foreign server in session 1 with
> keep_connection on, and I want to use that
> server object in session 2 with keep_connection off and session 3 with
> keep_connection on and so on.
> One way we can change the server's keep_connection option is to alter
> the server object, but that's not a good choice,
> as we have to alter it at the system level.

Is there use-case in practice where different backends need to have
different connection cache setting even if all of them connect the
same server? I thought since the problem that this feature is trying
to resolve is not to eat up the remote server connections capacity by
disabling connection cache, we’d like to disable connection cache to
the particular server, for example, which sets low max_connections.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services