From b1be3074ac719ce8073fba35d4c8b52fb4ddd0c3 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
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/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

