> > I thought we would add a core capability, idle_session_timeout, which > would disconnect idle sessions, and the postgres_fdw would use that. We > have already had requests for idle_session_timeout, but avoided it > because it seemed better to tell people to monitor pg_stat_activity and > terminate sessions that way, but now that postgres_fdw needs it too, > there might be enough of a requirement to add it. >
If we were to use idle_session_timeout (from patch [1]) for the remote session to go off without having to delete the corresponding entry from local connection cache and after that if we submit foreign query from local session, then below error would occur, which may not be an expected behaviour. (I took the patch from [1] and intentionally set the idle_session_timeout to a low value on remote server, issued a foreign_tbl query which caused remote session to open and after idle_session_timeout , the remote session closes and now issue the foreign_tbl query from local session) postgres=# SELECT * FROM foreign_tbl; ERROR: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. CONTEXT: remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ postgres=# Another way is that if we are thinking to use idle_session_timeout infra on the local postgres server to remove cached entries from the local connection cache, then the question arises: do we intend to use the same configuration parameter value set for idle_session_timeout for connection cache as well? Probably not, as we might use different values for different purposes of the same idle_session_timeout parameter, let's say 2000sec for idle_session_timeout and 1000sec for connection cache cleanup. [1] - https://www.postgresql.org/message-id/763A0689-F189-459E-946F-F0EC4458980B%40hotmail.com With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com On Wed, Jul 1, 2020 at 3:33 PM Rushabh Lathia <rushabh.lat...@gmail.com> wrote: > > > > On Wed, Jul 1, 2020 at 2:45 PM Bharath Rupireddy > <bharath.rupireddyforpostg...@gmail.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. > > > In my opinion, in such cases, one needs to create two server object one with > keep-connection ON and one with keep-connection off. And need to decide > to use appropriate for the particular session. > >> >> 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. >> >> With Regards, >> Bharath Rupireddy. >> EnterpriseDB: http://www.enterprisedb.com >> >> > > > -- > Rushabh Lathia
