On Tue, Jul 14, 2020 at 10:28 PM Bruce Momjian <br...@momjian.us> wrote: > > On Tue, Jul 14, 2020 at 03:38:49PM +0530, Bharath Rupireddy wrote: > > 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? > > Imagine implementing idle_in_session_timeout (which is useful on its > own), and then, when you connect to a foreign postgres_fdw server, you > set idle_in_session_timeout on the foreign side, and it just > disconnects/exits after an idle timeout. There is nothing the sending > side has to do. >
Assuming we use idle_in_session_timeout on remote backends, the remote sessions will be closed after timeout, but the locally cached connection cache entries still exist and become stale. The subsequent queries that may use the cached connections will fail, of course these subsequent queries can retry the connections only at the beginning of a remote txn but not in the middle of a remote txn, as being discussed in [1]. For instance, in a long running local txn, let say we used a remote connection at the beginning of the local txn(note that it will open a remote session and it's entry is cached in local connection cache), only we use the cached connection later at some point in the local txn, by then let say the idle_in_session_timeout has happened on the remote backend and the remote session would have been closed. The long running local txn will fail instead of succeeding. Isn't it a problem here? Please correct me, If I miss anything. IMHO, we are not fully solving the problem with idle_in_session_timeout on remote backends though we are addressing the main problem partly by letting the remote sessions close by themselves. [1] - https://www.postgresql.org/message-id/flat/CALj2ACUAi23vf1WiHNar_LksM9EDOWXcbHCo-fD4Mbr1d%3D78YQ%40mail.gmail.com With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
