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

>
> 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.lathia@gmail.com> wrote:
>
>
>
> On Wed, Jul 1, 2020 at 2:45 PM Bharath Rupireddy <bharath.rupireddyforpostgres@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