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

> > 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.
> >
> > Overall, though we define the server object in a single session, it
> > will be used in multiple sessions, having an
> > option at the per-server level would not be a good idea.
>
> You present this here as if it should be a Boolean (on or off) but I
> don't see why that should be the case. You can imagine trying to close
> connections if they have been idle for a certain length of time, or if
> there are more than a certain number of them, rather than (or in
> addition to) always/never. Which one is best, and why?
>
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.

>
> 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.

[1] - https://www.postgresql.org/message-id/CA%2BTgmob_ksTOgmbXhno%2Bk5XXPOK%2B-JYYLoU3MpXuutP4bH7gzA%40mail.gmail.com

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com