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

Thanks all for the ideas. There have been various points/approaches
discussed in the entire email chain so far.
I would like to summarize all of them here, so that we can agree on
one of the options and proceed further with this feature.

The issue this feature is trying to solve:
In postgres_fdw, rarely used remote connections lie ilde in the
connection cache(per backend) and so are remote sessions, for long
lasting local sessions which may unnecessarily eatup connections on
remote postgres servers.

Approach #1:
A new session level GUC (proposed name "enable_connectioncache"), when
set to true(which is by default) caches the remote connections
otherwise not. When set to false, everytime foreign query is issued a
new connection is made at the remote xact begin and dropped from the
connection cache at the remote xact end. This GUC applies to all the
foreign servers that are used in the session, it may not be possible
to have the control at the foreign server level. It may not be a good
idea to have postgres core controlling postgres_fdw property.

Approach #2:
A new postgres_fdw function, similar to dblink's dblink_disconnect(),
(possibly named postgres_fdw_disconnect_open_connections()). Seems
easy, but users have to frequently call this function to clean up the
cached entries. This may not be always possible, requires some sort of
monitoring and issuing this new disconnect function from in between
application code.

Approach #3:
A postgres_fdw foreign server level option: keep_connection(on/off).
When set to on (which is by default), caches the entries related to
that particular foreign server otherwise not. This gives control at
the foreign server level, which may not be possible with a single GUC.
It also addresses the concern that having postgres core solving
postgres_fdw problem. But, when the same foreign server is to be used
in multiple other sessions with different keep_connection
options(on/off), then a possible solution is to have two foreign
server definitions for the same server, one with keep_connection on
and another with off and use the foreign server accordingly and when
there is any change in other foreign server properties/options, need
to maintain the two versions of foreign servers.

Approach #4:
A postgres_fdw foreign server level option: connection idle time, the
amount of idle time for that server cached entry, after which the
cached entry goes away. Probably the backend, before itself going to
idle, has to be checking the cached entries and see if any of the
entries has timed out. One problem is that, if the backend 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?

I tried to summarize and put in the points in a concise manner,
forgive if I miss anything.

Thoughts?

Credits and thanks to: vignesh C, David G. Johnston, Masahiko Sawada,
Bruce Momjian, Rushabh Lathia, Ashutosh Bapat, Robert Haas.

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