On Sun, Jun 21, 2020 at 10:56 PM Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.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).
