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

On Wed, Jul 8, 2020 at 9:26 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> If the cached connection idle time property is used (I'm thinking we
> can define it per server object) then the local backend might have to
> close the connections which are lying unused more than idle time. To
> perform this task, the local backend might have to do it before it
> goes into idle state(as suggested by you in [1]). Please correct, if
> my understanding/thinking is wrong here.
>
> If the connection clean up is to be done by the local backend, then a
> point can be - let say a local session initially issues few foreign
> queries for which connections are cached, and it keeps executing all
> local queries, without never going to idle mode(I think this scenario
> looks too much impractical to me), then we may never clean the unused
> cached connections. If this scenario is really impractical if we are
> sure that there are high chances that the local backend goes to idle
> mode, then the idea of having per-server-object idle time and letting
> the local backend clean it up before it goes to idle mode looks great
> to me.

If it just did it before going idle, then what about sessions that
haven't reached the timeout at the point when we go idle, but do reach
the timeout later? And how would the FDW get control at the right time
anyway?

> > I tend to think this is better as an FDW property rather than a core
> > facility, but I'm not 100% sure of that and I think it likely depends
> > somewhat on the answers we choose to the questions in the preceding
> > paragraph.
> >
> I completely agree on having it as a FDW property.

Right, but not everyone does. It looks to me like there have been
votes on both sides.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company