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

On Tue, Jul 14, 2020 at 10:28 PM Bruce Momjian <bruce(at)momjian(dot)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