On 2024/07/24 20:40, Hayato Kuroda (Fujitsu) wrote:
Attached patches contain above fixes and comment improvements per request from
GPT-4o.
Thanks for updating the patches!
I’ve created a new base patch and split the v42-0001 patch into two parts
to implement the feature and improvements step by step. After pushing these
patches, I’ll focus on the v42-0002 patch next.
I’ve attached the three patches.
v43-0001:
This new patch enhances documentation for postgres_fdw_get_connections()
output columns. The output columns were documented in text format,
which is manageable for the current two columns. However, upcoming patches
will add new columns, making text descriptions less readable.
This patch updates the documentation to use a table format,
making it easier for users to understand each output column.
v43-0002:
This patch adds the "used_in_xact" column to postgres_fdw_get_connections().
It separates this change from the original v42-0001 patch for clarity.
v43-0003
This patch adds the "closed" column to postgres_fdw_get_connections().
I’ve also made several code improvements, for example adding a typedef for
pgfdwVersion to simplify its usage, and updated typedefs.list.
+enum pgfdwVersion
+{
+ PGFDW_V1_0 = 0,
+ PGFDW_V1_2,
+} pgfdwVersion;
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATIONFrom 53d07523a8e6c44043cdae8782282e5018f14953 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fu...@postgresql.org>
Date: Thu, 25 Jul 2024 20:35:12 +0900
Subject: [PATCH v43 1/3] doc: Enhance documentation for
postgres_fdw_get_connections() output columns.
The documentation previously described the output columns of
postgres_fdw_get_connections() in text format, which was manageable
for the original two columns. However, upcoming patches will add
new columns, making text descriptions less readable.
This commit updates the documentation to use a table format,
making it easier for users to understand each output column.
---
doc/src/sgml/postgres-fdw.sgml | 53 +++++++++++++++++++++++++++-------
1 file changed, 42 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 1a600382e2..c7e0a7d2a6 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -780,17 +780,9 @@ OPTIONS (ADD password_required 'false');
<term><function>postgres_fdw_get_connections(OUT server_name text, OUT
valid boolean) returns setof record</function></term>
<listitem>
<para>
- This function returns the foreign server names of all the open
- connections that <filename>postgres_fdw</filename> established from
- the local session to the foreign servers. It also returns whether
- each connection is valid or not. <literal>false</literal> is returned
- if the foreign server connection is used in the current local
- transaction but its foreign server or user mapping is changed or
- dropped (Note that server name of an invalid connection will be
- <literal>NULL</literal> if the server is dropped),
- and then such invalid connection will be closed at
- the end of that transaction. <literal>true</literal> is returned
- otherwise. If there are no open connections, no record is returned.
+ This function returns information about all open connections that
+ postgres_fdw has established from the local session to foreign servers.
+ If there are no open connections, no records are returned.
Example usage of the function:
<screen>
postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
@@ -799,7 +791,46 @@ postgres=# SELECT * FROM postgres_fdw_get_connections()
ORDER BY 1;
loopback1 | t
loopback2 | f
</screen>
+ The output columns are described in
+ <xref linkend="postgres-fdw-get-connections-columns"/>.
</para>
+
+ <table id="postgres-fdw-get-connections-columns">
+ <title><function>postgres_fdw_get_connections</function> Output
Columns</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>server_name</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>
+ The foreign server name of this connection. If the server is
+ dropped but the connection remains open (i.e., marked as
+ invalid), this will be <literal>NULL</literal>.
+ </entry>
+ </row>
+ <row>
+ <entry><structfield>valid</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry>
+ False if this connection is invalid, meaning it is used in
+ the current transaction but its foreign server or
+ user mapping has been changed or dropped.
+ The invalid connection will be closed at the end of
+ the transaction. True is returned otherwise.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
</listitem>
</varlistentry>
--
2.45.2
From b7e65e27a28d3fb4a2da75947a8093bfff0e7d9a Mon Sep 17 00:00:00 2001
From: Fujii Masao <fu...@postgresql.org>
Date: Thu, 25 Jul 2024 20:42:00 +0900
Subject: [PATCH v43 2/3] postgres_fdw: Add "used_in_xact" column to
postgres_fdw_get_connections().
This commit extends the postgres_fdw_get_connections() function to
include a new used_in_xact column, indicating whether each connection
is used in the current transaction.
This addition is particularly useful for the upcoming feature that
will check if connections are closed. By using those information,
users can verify if postgres_fdw connections used in a transaction
remain open. If any connection is closed, the transaction cannot
be committed successfully. In this case users can roll back it
immediately without waiting for transaction end.
The SQL API for postgres_fdw_get_connections() is updated by
this commit and may change in the future. To handle compatibility
with older SQL declarations, an API versioning system is introduced,
allowing the function to behave differently based on the API version.
---
contrib/postgres_fdw/Makefile | 2 +-
contrib/postgres_fdw/connection.c | 84 +++++++++++++++++--
.../postgres_fdw/expected/postgres_fdw.out | 8 +-
contrib/postgres_fdw/meson.build | 1 +
.../postgres_fdw/postgres_fdw--1.1--1.2.sql | 16 ++++
contrib/postgres_fdw/postgres_fdw.control | 2 +-
doc/src/sgml/postgres-fdw.sgml | 21 +++--
src/tools/pgindent/typedefs.list | 1 +
8 files changed, 114 insertions(+), 21 deletions(-)
create mode 100644 contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index b9fa699305..88fdce40d6 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -14,7 +14,7 @@ PG_CPPFLAGS = -I$(libpq_srcdir)
SHLIB_LINK_INTERNAL = $(libpq)
EXTENSION = postgres_fdw
-DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql
+DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql
postgres_fdw--1.1--1.2.sql
REGRESS = postgres_fdw query_cancel
diff --git a/contrib/postgres_fdw/connection.c
b/contrib/postgres_fdw/connection.c
index 33e8054f64..2e587de023 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -107,10 +107,20 @@ static uint32 pgfdw_we_get_result = 0;
(entry)->xact_depth,
(entry)->xact_depth); \
} while(0)
+/*
+ * Extension version number, for supporting older extension versions' objects
+ */
+typedef enum pgfdwVersion
+{
+ PGFDW_V1_1 = 0,
+ PGFDW_V1_2,
+} pgfdwVersion;
+
/*
* SQL functions
*/
PG_FUNCTION_INFO_V1(postgres_fdw_get_connections);
+PG_FUNCTION_INFO_V1(postgres_fdw_get_connections_1_2);
PG_FUNCTION_INFO_V1(postgres_fdw_disconnect);
PG_FUNCTION_INFO_V1(postgres_fdw_disconnect_all);
@@ -159,6 +169,8 @@ static void pgfdw_security_check(const char **keywords,
const char **values,
UserMapping
*user, PGconn *conn);
static bool UserMappingPasswordRequired(UserMapping *user);
static bool disconnect_cached_connections(Oid serverid);
+static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
+
pgfdwVersion api_version);
/*
* Get a PGconn which can be used to execute queries on the remote PostgreSQL
@@ -1977,23 +1989,34 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List
*cancel_requested,
}
}
+/* Number of output arguments (columns) for various API versions */
+#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_1 2
+#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2 3
+#define POSTGRES_FDW_GET_CONNECTIONS_COLS 3 /* maximum of above */
+
/*
- * List active foreign server connections.
+ * Internal function used by postgres_fdw_get_connections variants.
+ *
+ * For API version 1.1, this function returns a set of records with
+ * the following values:
*
- * This function takes no input parameter and returns setof record made of
- * following values:
* - server_name - server name of active connection. In case the foreign server
* is dropped but still the connection is active, then the server name will
* be NULL in output.
* - valid - true/false representing whether the connection is valid or not.
- * Note that the connections can get invalidated in pgfdw_inval_callback.
+ * Note that connections can become invalid in pgfdw_inval_callback.
+ *
+ * For API version 1.2 and later, this function returns the following
+ * additional value along with the two values from version 1.1:
+ *
+ * - used_in_xact - true if the connection is used in the current transaction.
*
* No records are returned when there are no cached connections at all.
*/
-Datum
-postgres_fdw_get_connections(PG_FUNCTION_ARGS)
+static void
+postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
+
pgfdwVersion api_version)
{
-#define POSTGRES_FDW_GET_CONNECTIONS_COLS 2
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
HASH_SEQ_STATUS scan;
ConnCacheEntry *entry;
@@ -2002,7 +2025,22 @@ postgres_fdw_get_connections(PG_FUNCTION_ARGS)
/* If cache doesn't exist, we return no records */
if (!ConnectionHash)
- PG_RETURN_VOID();
+ return;
+
+ /* Check we have the expected number of output arguments */
+ switch (rsinfo->setDesc->natts)
+ {
+ case POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_1:
+ if (api_version != PGFDW_V1_1)
+ elog(ERROR, "incorrect number of output
arguments");
+ break;
+ case POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2:
+ if (api_version != PGFDW_V1_2)
+ elog(ERROR, "incorrect number of output
arguments");
+ break;
+ default:
+ elog(ERROR, "incorrect number of output arguments");
+ }
hash_seq_init(&scan, ConnectionHash);
while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
@@ -2061,10 +2099,38 @@ postgres_fdw_get_connections(PG_FUNCTION_ARGS)
values[1] = BoolGetDatum(!entry->invalidated);
+ if (api_version >= PGFDW_V1_2)
+ {
+ /* Is this connection used in the current transaction?
*/
+ values[2] = BoolGetDatum(entry->xact_depth > 0);
+ }
+
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
values, nulls);
}
+}
+
+/*
+ * List active foreign server connections.
+ *
+ * The SQL API of this function has changed multiple times, and will likely
+ * do so again in future. To support the case where a newer version of this
+ * loadable module is being used with an old SQL declaration of the function,
+ * we continue to support the older API versions.
+ */
+Datum
+postgres_fdw_get_connections_1_2(PG_FUNCTION_ARGS)
+{
+ postgres_fdw_get_connections_internal(fcinfo, PGFDW_V1_2);
+
+ return (Datum) 0;
+}
+
+Datum
+postgres_fdw_get_connections(PG_FUNCTION_ARGS)
+{
+ postgres_fdw_get_connections_internal(fcinfo, PGFDW_V1_1);
- PG_RETURN_VOID();
+ return (Datum) 0;
}
/*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 39b2b317e8..82fdc0e26f 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10464,10 +10464,10 @@ drop cascades to foreign table ft7
-- should be output as invalid connections. Also the server name for
-- loopback3 should be NULL because the server was dropped.
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
- server_name | valid
--------------+-------
- loopback | f
- | f
+ server_name | valid | used_in_xact
+-------------+-------+--------------
+ loopback | f | t
+ | f | t
(2 rows)
-- The invalid connections get closed in pgfdw_xact_callback during commit.
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index f0803ee077..3014086ba6 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -26,6 +26,7 @@ install_data(
'postgres_fdw.control',
'postgres_fdw--1.0.sql',
'postgres_fdw--1.0--1.1.sql',
+ 'postgres_fdw--1.1--1.2.sql',
kwargs: contrib_data_args,
)
diff --git a/contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
b/contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
new file mode 100644
index 0000000000..0c65bf2e14
--- /dev/null
+++ b/contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
@@ -0,0 +1,16 @@
+/* contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION postgres_fdw UPDATE TO '1.2'" to load this file.
\quit
+
+/* First we have to remove it from the extension */
+ALTER EXTENSION postgres_fdw DROP FUNCTION postgres_fdw_get_connections ();
+
+/* Then we can drop it */
+DROP FUNCTION postgres_fdw_get_connections ();
+
+CREATE FUNCTION postgres_fdw_get_connections (OUT server_name text,
+ OUT valid boolean, OUT used_in_xact boolean)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'postgres_fdw_get_connections_1_2'
+LANGUAGE C STRICT PARALLEL RESTRICTED;
diff --git a/contrib/postgres_fdw/postgres_fdw.control
b/contrib/postgres_fdw/postgres_fdw.control
index d489382064..a4b800be4f 100644
--- a/contrib/postgres_fdw/postgres_fdw.control
+++ b/contrib/postgres_fdw/postgres_fdw.control
@@ -1,5 +1,5 @@
# postgres_fdw extension
comment = 'foreign-data wrapper for remote PostgreSQL servers'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/postgres_fdw'
relocatable = true
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index c7e0a7d2a6..819dca3e00 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -777,7 +777,9 @@ OPTIONS (ADD password_required 'false');
<variablelist>
<varlistentry>
- <term><function>postgres_fdw_get_connections(OUT server_name text, OUT
valid boolean) returns setof record</function></term>
+ <term><function>postgres_fdw_get_connections(OUT server_name text,
+ OUT valid boolean, OUT used_in_xact boolean)
+ returns setof record</function></term>
<listitem>
<para>
This function returns information about all open connections that
@@ -785,11 +787,11 @@ OPTIONS (ADD password_required 'false');
If there are no open connections, no records are returned.
Example usage of the function:
<screen>
-postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
- server_name | valid
--------------+-------
- loopback1 | t
- loopback2 | f
+postgres=*# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
+ server_name | valid | used_in_xact
+-------------+-------+--------------
+ loopback1 | t | t
+ loopback2 | f | t
</screen>
The output columns are described in
<xref linkend="postgres-fdw-get-connections-columns"/>.
@@ -827,6 +829,13 @@ postgres=# SELECT * FROM postgres_fdw_get_connections()
ORDER BY 1;
the transaction. True is returned otherwise.
</entry>
</row>
+ <row>
+ <entry><structfield>used_in_xact</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry>
+ True if this connection is used in the current transaction.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..508c74a22b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3731,6 +3731,7 @@ pg_wc_probefunc
pg_wchar
pg_wchar_tbl
pgp_armor_headers_state
+pgfdwVersion
pgsocket
pgsql_thing_t
pgssEntry
--
2.45.2
From fe98ccbf0a60e6c95272d3d837df7bc9cb7cf1f2 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fu...@postgresql.org>
Date: Fri, 26 Jul 2024 03:05:53 +0900
Subject: [PATCH v43 3/3] 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.
---
contrib/postgres_fdw/connection.c | 84 +++++++++++++++++--
.../postgres_fdw/expected/postgres_fdw.out | 54 +++++++++++-
.../postgres_fdw/postgres_fdw--1.1--1.2.sql | 5 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 33 ++++++++
doc/src/sgml/postgres-fdw.sgml | 41 +++++++--
5 files changed, 199 insertions(+), 18 deletions(-)
diff --git a/contrib/postgres_fdw/connection.c
b/contrib/postgres_fdw/connection.c
index 2e587de023..7ce29ea768 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -12,6 +12,10 @@
*/
#include "postgres.h"
+#if HAVE_POLL_H
+#include <poll.h>
+#endif
+
#include "access/htup_details.h"
#include "access/xact.h"
#include "catalog/pg_user_mapping.h"
@@ -171,6 +175,8 @@ static bool UserMappingPasswordRequired(UserMapping *user);
static bool disconnect_cached_connections(Oid serverid);
static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
pgfdwVersion api_version);
+static int pgfdw_conn_check(PGconn *conn);
+static bool pgfdw_conn_checkable(void);
/*
* Get a PGconn which can be used to execute queries on the remote PostgreSQL
@@ -1991,14 +1997,14 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List
*cancel_requested,
/* Number of output arguments (columns) for various API versions */
#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_1 2
-#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2 3
-#define POSTGRES_FDW_GET_CONNECTIONS_COLS 3 /* maximum of above */
+#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2 4
+#define POSTGRES_FDW_GET_CONNECTIONS_COLS 4 /* maximum of above */
/*
* Internal function used by postgres_fdw_get_connections variants.
*
- * For API version 1.1, this function returns a set of records with
- * the following values:
+ * For API version 1.1, this function takes no input parameter and
+ * returns a set of records with the following values:
*
* - server_name - server name of active connection. In case the foreign server
* is dropped but still the connection is active, then the server name will
@@ -2006,10 +2012,12 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List
*cancel_requested,
* - valid - true/false representing whether the connection is valid or not.
* Note that connections can become invalid in pgfdw_inval_callback.
*
- * For API version 1.2 and later, this function returns the following
- * additional value along with the two values from version 1.1:
+ * For API version 1.2 and later, this function takes an input parameter
+ * to check a connection status and returns the following
+ * additional values along with the two values from version 1.1:
*
* - used_in_xact - true if the connection is used in the current transaction.
+ * - closed: true if the connection is closed.
*
* No records are returned when there are no cached connections at all.
*/
@@ -2101,8 +2109,19 @@ postgres_fdw_get_connections_internal(FunctionCallInfo
fcinfo,
if (api_version >= PGFDW_V1_2)
{
+ bool check_conn = PG_GETARG_BOOL(0);
+
/* Is this connection used in the current transaction?
*/
values[2] = BoolGetDatum(entry->xact_depth > 0);
+
+ /*
+ * If a connection status check is requested and
supported, return
+ * whether the connection is closed. Otherwise, return
NULL.
+ */
+ if (check_conn && pgfdw_conn_checkable())
+ values[3] =
BoolGetDatum(pgfdw_conn_check(entry->conn) != 0);
+ else
+ nulls[3] = true;
}
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
values, nulls);
@@ -2258,3 +2277,56 @@ disconnect_cached_connections(Oid serverid)
return result;
}
+
+/*
+ * Check if the remote server closed the connection.
+ *
+ * Returns 1 if the connection is closed, -1 if an error occurred,
+ * and 0 if it's not closed or if the connection check is unavailable
+ * on this platform.
+ */
+static int
+pgfdw_conn_check(PGconn *conn)
+{
+ int sock = PQsocket(conn);
+
+ if (PQstatus(conn) != CONNECTION_OK || sock == -1)
+ return -1;
+
+#if (defined(HAVE_POLL) && defined(POLLRDHUP))
+ {
+ struct pollfd input_fd;
+ int result;
+
+ input_fd.fd = sock;
+ input_fd.events = POLLRDHUP;
+ input_fd.revents = 0;
+
+ do
+ result = poll(&input_fd, 1, 0);
+ while (result < 0 && errno == EINTR);
+
+ if (result < 0)
+ return -1;
+
+ return (input_fd.revents & POLLRDHUP) ? 1 : 0;
+ }
+#else
+ return 0;
+#endif
+}
+
+/*
+ * Check if connection status checking is available on this platform.
+ *
+ * Returns true if available, false otherwise.
+ */
+static bool
+pgfdw_conn_checkable(void)
+{
+#if (defined(HAVE_POLL) && defined(POLLRDHUP))
+ return true;
+#else
+ return false;
+#endif
+}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 82fdc0e26f..212434711e 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10464,10 +10464,10 @@ drop cascades to foreign table ft7
-- should be output as invalid connections. Also the server name for
-- loopback3 should be NULL because the server was dropped.
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
- server_name | valid | used_in_xact
--------------+-------+--------------
- loopback | f | t
- | f | t
+ server_name | valid | used_in_xact | closed
+-------------+-------+--------------+--------
+ loopback | f | t |
+ | f | t |
(2 rows)
-- The invalid connections get closed in pgfdw_xact_callback during commit.
@@ -12286,3 +12286,49 @@ ANALYZE analyze_table;
-- cleanup
DROP FOREIGN TABLE analyze_ftable;
DROP TABLE analyze_table;
+-- ===================================================================
+-- test for postgres_fdw_get_connections function with check_conn = true
+-- ===================================================================
+-- Disable debug_discard_caches in order to manage remote connections
+SET debug_discard_caches TO '0';
+-- The text of the error might vary across platforms, so only show SQLSTATE.
+\set VERBOSITY sqlstate
+SELECT 1 FROM postgres_fdw_disconnect_all();
+ ?column?
+----------
+ 1
+(1 row)
+
+ALTER SERVER loopback OPTIONS (SET application_name 'fdw_conn_check');
+SELECT 1 FROM ft1 LIMIT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+-- Since the remote server is still connected, "closed" should be FALSE,
+-- or NULL if the connection status check is not available.
+SELECT CASE WHEN closed IS NOT true THEN 1 ELSE 0 END
+ FROM postgres_fdw_get_connections(true);
+ case
+------
+ 1
+(1 row)
+
+-- After terminating the remote backend, since the connection is closed,
+-- "closed" should be TRUE, or NULL if the connection status check
+-- is not available.
+DO $$ BEGIN
+PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'fdw_conn_check';
+END $$;
+SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END
+ FROM postgres_fdw_get_connections(true);
+ case
+------
+ 1
+(1 row)
+
+-- Clean up
+\set VERBOSITY default
+RESET debug_discard_caches;
diff --git a/contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
b/contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
index 0c65bf2e14..0d406c6028 100644
--- a/contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
+++ b/contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql
@@ -9,8 +9,9 @@ ALTER EXTENSION postgres_fdw DROP FUNCTION
postgres_fdw_get_connections ();
/* Then we can drop it */
DROP FUNCTION postgres_fdw_get_connections ();
-CREATE FUNCTION postgres_fdw_get_connections (OUT server_name text,
- OUT valid boolean, OUT used_in_xact boolean)
+CREATE 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
AS 'MODULE_PATHNAME', 'postgres_fdw_get_connections_1_2'
LANGUAGE C STRICT PARALLEL RESTRICTED;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8be9f99c19..371e131933 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4235,3 +4235,36 @@ ANALYZE analyze_table;
-- cleanup
DROP FOREIGN TABLE analyze_ftable;
DROP TABLE analyze_table;
+
+-- ===================================================================
+-- test for postgres_fdw_get_connections function with check_conn = true
+-- ===================================================================
+
+-- Disable debug_discard_caches in order to manage remote connections
+SET debug_discard_caches TO '0';
+
+-- The text of the error might vary across platforms, so only show SQLSTATE.
+\set VERBOSITY sqlstate
+
+SELECT 1 FROM postgres_fdw_disconnect_all();
+ALTER SERVER loopback OPTIONS (SET application_name 'fdw_conn_check');
+SELECT 1 FROM ft1 LIMIT 1;
+
+-- Since the remote server is still connected, "closed" should be FALSE,
+-- or NULL if the connection status check is not available.
+SELECT CASE WHEN closed IS NOT true THEN 1 ELSE 0 END
+ FROM postgres_fdw_get_connections(true);
+
+-- After terminating the remote backend, since the connection is closed,
+-- "closed" should be TRUE, or NULL if the connection status check
+-- is not available.
+DO $$ BEGIN
+PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
+ WHERE application_name = 'fdw_conn_check';
+END $$;
+SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END
+ FROM postgres_fdw_get_connections(true);
+
+-- Clean up
+\set VERBOSITY default
+RESET debug_discard_caches;
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 819dca3e00..9f3151f647 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -777,21 +777,40 @@ 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 that
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 +855,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>
--
2.45.2
