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

On Sun, Jun 21, 2020 at 10:56 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> When a query on foreign table is executed from a local session using
> postgres_fdw, as expected the local postgres backend opens a
> connection which causes a remote session/backend to be opened on the
> remote postgres server for query execution.
>
> [...]

> I propose to have a new session level GUC called
> "enable_connectioncache"(name can be changed if it doesn't correctly
> mean the purpose) with the default value being true which means that
> all the remote connections are cached. If set to false, the
> connections are not cached and so are remote sessions closed by the local
> backend/session at
> the end of each remote transaction.
>
> [...]

> Thoughts?
>
> Test Case:
> without patch:
> 1. Run the query on foreign table
> 2. Look for the backend/session opened on the remote postgres server, it
> exists till the local session remains active.
>
> with patch:
> 1. SET enable_connectioncache TO false;
> 2. Run the query on the foreign table
> 3. Look for the backend/session opened on the remote postgres server, it
> should not exist.
>

If this is just going to apply to postgres_fdw why not just have that
module provide a function "disconnect_open_sessions()" or the like that
does this upon user command? I suppose there would be some potential value
to having this be set per-user but that wouldn't preclude the usefulness of
a function. And by having a function the usefulness of the GUC seems
reduced. On a related note is there any entanglement here with the
supplied dblink and/or dblink_fdw [1] modules as they do provide connect
and disconnect functions and also leverages postgres_fdw (or dblink_fdw if
specified, which brings us back to the question of whether this option
should be respected by that FDW).

Otherwise, I would imagine that having multiple queries execute before
wanting to drop the connection would be desirable so at minimum a test case
that does something like:

SELECT count(*) FROM remote.tbl1;
-- connection still open
SET enable_connectioncache TO false;
SELECT count(*) FROM remote.tbl2;
-- now it was closed

Or maybe even better, have the close action happen on a transaction
boundary.

And if it doesn't just apply to postgres_fdw (or at least doesn't have to)
then the description text should be less specific.

David J.

[1] The only place I see "dblink_fdw" in the documentation is in the dblink
module's dblink_connect page. I would probably modify that page to say:
"It is recommended to use the foreign-data wrapper dblink_fdw (installed by
this module) when defining the foreign server." (adding the parenthetical).