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).