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

1 files changed, 29 insertions, 4 deletions

diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 54ab8edfab6..6a61d838621 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -59,6 +59,8 @@ typedef struct ConnCacheEntry
 	bool		have_error;		/* have any subxacts aborted in this xact? */
 	bool		changing_xact_state;	/* xact state change in process */
 	bool		invalidated;	/* true if reconnect is pending */
+	bool		keep_connections;	/* setting value of keep_connections
+									 * server option */
 	Oid			serverid;		/* foreign server OID used to get server name */
 	uint32		server_hashvalue;	/* hash value of foreign server OID */
 	uint32		mapping_hashvalue;	/* hash value of user mapping OID */
@@ -286,6 +288,7 @@ static void
 make_new_connection(ConnCacheEntry *entry, UserMapping *user)
 {
 	ForeignServer *server = GetForeignServer(user->serverid);
+	ListCell   *lc;
 
 	Assert(entry->conn == NULL);
 
@@ -304,6 +307,26 @@ make_new_connection(ConnCacheEntry *entry, UserMapping *user)
 							  ObjectIdGetDatum(user->umid));
 	memset(&entry->state, 0, sizeof(entry->state));
 
+	/*
+	 * Determine whether to keep the connection that we're about to make here
+	 * open even after the transaction using it ends, so that the subsequent
+	 * transactions can re-use it.
+	 *
+	 * It's enough to determine this only when making new connection because
+	 * all the connections to the foreign server whose keep_connections option
+	 * is changed will be closed and re-made later.
+	 *
+	 * By default, all the connections to any foreign servers are kept open.
+	 */
+	entry->keep_connections = true;
+	foreach(lc, server->options)
+	{
+		DefElem    *def = (DefElem *) lfirst(lc);
+
+		if (strcmp(def->defname, "keep_connections") == 0)
+			entry->keep_connections = defGetBoolean(def);
+	}
+
 	/* Now try to make the connection */
 	entry->conn = connect_pg_server(server, user);
 
@@ -970,14 +993,16 @@ pgfdw_xact_callback(XactEvent event, void *arg)
 		entry->xact_depth = 0;
 
 		/*
-		 * If the connection isn't in a good idle state or it is marked as
-		 * invalid, then discard it to recover. Next GetConnection will open a
-		 * new connection.
+		 * If the connection isn't in a good idle state, it is marked as
+		 * invalid or keep_connections option of its server is disabled, then
+		 * discard it to recover. Next GetConnection will open a new
+		 * connection.
 		 */
 		if (PQstatus(entry->conn) != CONNECTION_OK ||
 			PQtransactionStatus(entry->conn) != PQTRANS_IDLE ||
 			entry->changing_xact_state ||
-			entry->invalidated)
+			entry->invalidated ||
+			!entry->keep_connections)
 		{
 			elog(DEBUG3, "discarding connection %p", entry->conn);
 			disconnect_pg_server(entry);