postgres_fdw: Add functions to discard cached connections.

This commit introduces two new functions postgres_fdw_disconnect()
and postgres_fdw_disconnect_all(). The former function discards
the cached connections to the specified foreign server. The latter discards
all the cached connections. If the connection is used in the current
transaction, it's not closed and a warning message is emitted.

For example, these functions are useful when users want to explicitly
close the foreign server connections that are no longer necessary and
then to prevent them from eating up the foreign servers connections
capacity.

Author: Bharath Rupireddy, tweaked a bit by Fujii Masao
Reviewed-by: Alexey Kondratov, Zhijie Hou, Zhihong Yu, Fujii Masao
Discussion: https://postgr.es/m/CALj2ACVvrp5=AVp2PupEm+nAC8S4buqR3fJMmaCoc7ftT0aD2A@mail.gmail.com

1 files changed, 66 insertions, 1 deletions

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index fb4c22ac69f..8d6abd4c548 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -512,7 +512,7 @@ OPTIONS (ADD password_required 'false');
       the end of that transaction. <literal>true</literal> is returned
       otherwise. If there are no open connections, no record is returned.
       Example usage of the function:
-    <screen>
+<screen>
 postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
  server_name | valid 
 -------------+-------
@@ -522,6 +522,51 @@ postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><function>postgres_fdw_disconnect(server_name text) returns boolean</function></term>
+    <listitem>
+     <para>
+      This function discards the open connections that are established by
+      <filename>postgres_fdw</filename> from the local session to
+      the foreign server with the given name.  Note that there can be
+      multiple connections to the given server using different user mappings.
+      If the connections are used in the current local transaction,
+      they are not disconnected and warning messages are reported.
+      This function returns <literal>true</literal> if it disconnects
+      at least one connection, otherwise <literal>false</literal>.
+      If no foreign server with the given name is found, an error is reported.
+      Example usage of the function:
+<screen>
+postgres=# SELECT postgres_fdw_disconnect('loopback1');
+ postgres_fdw_disconnect 
+-------------------------
+ t
+</screen>
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><function>postgres_fdw_disconnect_all() returns boolean</function></term>
+    <listitem>
+     <para>
+      This function discards all the open connections that are established by
+      <filename>postgres_fdw</filename> from the local session to
+      the foreign servers.  If the connections are used in the current local
+      transaction, they are not disconnected and warning messages are reported.
+      This function returns <literal>true</literal> if it disconnects
+      at least one connection, otherwise <literal>false</literal>.
+      Example usage of the function:
+<screen>
+postgres=# SELECT postgres_fdw_disconnect_all();
+ postgres_fdw_disconnect_all 
+-----------------------------
+ t
+</screen>
+     </para>
+    </listitem>
+   </varlistentry>
    </variablelist>
 
 </sect2>
@@ -537,6 +582,26 @@ postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
    multiple user identities (user mappings) are used to access the foreign
    server, a connection is established for each user mapping.
   </para>
+
+  <para>
+   When changing the definition of or removing a foreign server or
+   a user mapping, the corresponding connections are closed.
+   But note that if the connections are used in the current local transaction
+   at that moment, they are kept until the end of the transaction.
+   Closed connections will be established again when they are necessary
+   by subsequent queries using a foreign table.
+  </para>
+
+  <para>
+   Once a connection to a foreign server has been established,
+   it's usually kept until the local or the corresponding remote
+   session exits.  To disconnect a connection explicitly,
+   <function>postgres_fdw_disconnect</function> and
+   <function>postgres_fdw_disconnect_all</function> functions
+   need to be used.  For example, these are useful when closing
+   the connections that are no longer necessary and then preventing them
+   from consuming the foreign server connections capacity too much.
+  </para>
  </sect2>
 
  <sect2>