From b1be3074ac719ce8073fba35d4c8b52fb4ddd0c3 Mon Sep 17 00:00:00 2001 From: Fujii Masao Date: Fri, 2 Apr 2021 19:45:42 +0900 Subject: postgres_fdw: Add option to control whether to keep connections open. This commit adds a new option keep_connections that controls whether postgres_fdw keeps the connections to the foreign server open so that the subsequent queries can re-use them. This option can only be specified for a foreign server. The default is on. If set to off, all connections to the foreign server will be discarded at the end of transaction. Closed connections will be re-established when they are necessary by future queries using a foreign table. This option is useful, for example, when users want to prevent the connections from eating up the foreign servers connections capacity. Author: Bharath Rupireddy Reviewed-by: Alexey Kondratov, Vignesh C, Fujii Masao Discussion: https://postgr.es/m/CALj2ACVvrp5=AVp2PupEm+nAC8S4buqR3fJMmaCoc7ftT0aD2A@mail.gmail.com --- contrib/postgres_fdw/sql/postgres_fdw.sql | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'contrib/postgres_fdw/sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 21a29cc0620..107d1c0e030 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2821,6 +2821,19 @@ DROP USER MAPPING FOR regress_multi_conn_user2 SERVER loopback; DROP ROLE regress_multi_conn_user1; DROP ROLE regress_multi_conn_user2; +-- =================================================================== +-- Test foreign server level option keep_connections +-- =================================================================== +-- By default, the connections associated with foreign server are cached i.e. +-- keep_connections option is on. Set it to off. +ALTER SERVER loopback OPTIONS (keep_connections 'off'); +-- connection to loopback server is closed at the end of xact +-- as keep_connections was set to off. +SELECT 1 FROM ft1 LIMIT 1; +-- No cached connections, so no records should be output. +SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1; +ALTER SERVER loopback OPTIONS (SET keep_connections 'on'); + -- =================================================================== -- batch insert -- =================================================================== -- cgit v1.2.3