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