postgres_fdw: Add connection status check to postgres_fdw_get_connections().

This commit extends the postgres_fdw_get_connections() function
to check if connections are closed. This is useful for detecting closed
postgres_fdw connections that could prevent successful transaction
commits. Users can roll back transactions immediately upon detecting
closed connections, avoiding unnecessary processing of failed
transactions.

This feature is available only on systems supporting the non-standard
POLLRDHUP extension to the poll system call, including Linux.

Author: Hayato Kuroda
Reviewed-by: Shinya Kato, Zhihong Yu, Kyotaro Horiguchi, Andres Freund
Reviewed-by: Onder Kalaci, Takamichi Osumi, Vignesh C, Tom Lane, Ted Yu
Reviewed-by: Katsuragi Yuta, Peter Smith, Shubham Khanna, Fujii Masao
Discussion: https://postgr.es/m/TYAPR01MB58662809E678253B90E82CE5F5889@TYAPR01MB5866.jpnprd01.prod.outlook.com

1 files changed, 35 insertions, 7 deletions

diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index b904f7a33ec..90969f63ca7 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -777,21 +777,39 @@ OPTIONS (ADD password_required 'false');
 
   <variablelist>
    <varlistentry>
-    <term><function>postgres_fdw_get_connections(OUT server_name text,
-      OUT valid boolean, OUT used_in_xact boolean)
+    <term><function>postgres_fdw_get_connections(
+      IN check_conn boolean DEFAULT false, OUT server_name text,
+      OUT valid boolean, OUT used_in_xact boolean, OUT closed boolean)
       returns setof record</function></term>
     <listitem>
      <para>
       This function returns information about all open connections postgres_fdw
       has established from the local session to foreign servers. If there are
       no open connections, no records are returned.
+     </para>
+     <para>
+      If <literal>check_conn</literal> is set to <literal>true</literal>,
+      the function checks the status of each connection and shows
+      the result in the <literal>closed</literal> column.
+      This feature is currently available only on systems that support
+      the non-standard <symbol>POLLRDHUP</symbol> extension to
+      the <symbol>poll</symbol> system call, including Linux.
+      This is useful to check if all connections used within
+      a transaction are still open. If any connections are closed,
+      the transaction cannot be committed successfully,
+      so it is better to roll back as soon as a closed connection is detected,
+      rather than continuing to the end. Users can roll back the transaction
+      immediately if the function reports connections where both
+      <literal>used_in_xact</literal> and <literal>closed</literal> are
+      <literal>true</literal>.
+     </para>
+     <para>
       Example usage of the function:
 <screen>
-postgres=*# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
- server_name | valid | used_in_xact
--------------+-------+--------------
- loopback1   | t     | t
- loopback2   | f     | t
+ server_name | valid | used_in_xact | closed
+-------------+-------+--------------+--------
+ loopback1   | t     | t            |
+ loopback2   | f     | t            |
 </screen>
       The output columns are described in
       <xref linkend="postgres-fdw-get-connections-columns"/>.
@@ -836,6 +854,16 @@ postgres=*# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
          True if this connection is used in the current transaction.
         </entry>
        </row>
+       <row>
+        <entry><structfield>closed</structfield></entry>
+        <entry><type>boolean</type></entry>
+        <entry>
+         True if this connection is closed, false otherwise.
+         <literal>NULL</literal> is returned if <literal>check_conn</literal>
+         is set to <literal>false</literal> or if the connection status check
+         is not available on this platform.
+        </entry>
+       </row>
       </tbody>
      </tgroup>
     </table>