Skip to content

Commit 857df3c

Browse files
MasaoFujiiMasaoFujii
committed
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 parent c297a47 commit 857df3c

File tree

5 files changed

+199
-19
lines changed

5 files changed

+199
-19
lines changed

‎contrib/postgres_fdw/connection.c

Lines changed: 78 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,10 @@
1212
*/
1313
#include "postgres.h"
1414

15+
#if HAVE_POLL_H
16+
#include <poll.h>
17+
#endif
18+
1519
#include "access/htup_details.h"
1620
#include "access/xact.h"
1721
#include "catalog/pg_user_mapping.h"
@@ -171,6 +175,8 @@ static bool UserMappingPasswordRequired(UserMapping *user);
171175
static bool disconnect_cached_connections(Oid serverid);
172176
static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
173177
enum pgfdwVersion api_version);
178+
static int pgfdw_conn_check(PGconn *conn);
179+
static bool pgfdw_conn_checkable(void);
174180

175181
/*
176182
* Get a PGconn which can be used to execute queries on the remote PostgreSQL
@@ -1991,25 +1997,27 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List *cancel_requested,
19911997

19921998
/* Number of output arguments (columns) for various API versions */
19931999
#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_1 2
1994-
#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2 3
1995-
#define POSTGRES_FDW_GET_CONNECTIONS_COLS 3 /* maximum of above */
2000+
#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2 4
2001+
#define POSTGRES_FDW_GET_CONNECTIONS_COLS 4 /* maximum of above */
19962002

19972003
/*
19982004
* Internal function used by postgres_fdw_get_connections variants.
19992005
*
2000-
* For API version 1.1, this function returns a set of records with
2001-
* the following values:
2006+
* For API version 1.1, this function takes no input parameter and
2007+
* returns a set of records with the following values:
20022008
*
20032009
* - server_name - server name of active connection. In case the foreign server
20042010
* is dropped but still the connection is active, then the server name will
20052011
* be NULL in output.
20062012
* - valid - true/false representing whether the connection is valid or not.
20072013
* Note that connections can become invalid in pgfdw_inval_callback.
20082014
*
2009-
* For API version 1.2 and later, this function returns the following
2010-
* additional value along with the two values from version 1.1:
2015+
* For API version 1.2 and later, this function takes an input parameter
2016+
* to check a connection status and returns the following
2017+
* additional values along with the two values from version 1.1:
20112018
*
20122019
* - used_in_xact - true if the connection is used in the current transaction.
2020+
* - closed: true if the connection is closed.
20132021
*
20142022
* No records are returned when there are no cached connections at all.
20152023
*/
@@ -2101,8 +2109,19 @@ postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
21012109

21022110
if (api_version >= PGFDW_V1_2)
21032111
{
2112+
bool check_conn = PG_GETARG_BOOL(0);
2113+
21042114
/* Is this connection used in the current transaction? */
21052115
values[2] = BoolGetDatum(entry->xact_depth > 0);
2116+
2117+
/*
2118+
* If a connection status check is requested and supported, return
2119+
* whether the connection is closed. Otherwise, return NULL.
2120+
*/
2121+
if (check_conn && pgfdw_conn_checkable())
2122+
values[3] = BoolGetDatum(pgfdw_conn_check(entry->conn) != 0);
2123+
else
2124+
nulls[3] = true;
21062125
}
21072126

21082127
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
@@ -2258,3 +2277,56 @@ disconnect_cached_connections(Oid serverid)
22582277

22592278
return result;
22602279
}
2280+
2281+
/*
2282+
* Check if the remote server closed the connection.
2283+
*
2284+
* Returns 1 if the connection is closed, -1 if an error occurred,
2285+
* and 0 if it's not closed or if the connection check is unavailable
2286+
* on this platform.
2287+
*/
2288+
static int
2289+
pgfdw_conn_check(PGconn *conn)
2290+
{
2291+
int sock = PQsocket(conn);
2292+
2293+
if (PQstatus(conn) != CONNECTION_OK || sock == -1)
2294+
return -1;
2295+
2296+
#if (defined(HAVE_POLL) && defined(POLLRDHUP))
2297+
{
2298+
struct pollfd input_fd;
2299+
int result;
2300+
2301+
input_fd.fd = sock;
2302+
input_fd.events = POLLRDHUP;
2303+
input_fd.revents = 0;
2304+
2305+
do
2306+
result = poll(&input_fd, 1, 0);
2307+
while (result < 0 && errno == EINTR);
2308+
2309+
if (result < 0)
2310+
return -1;
2311+
2312+
return (input_fd.revents & POLLRDHUP) ? 1 : 0;
2313+
}
2314+
#else
2315+
return 0;
2316+
#endif
2317+
}
2318+
2319+
/*
2320+
* Check if connection status checking is available on this platform.
2321+
*
2322+
* Returns true if available, false otherwise.
2323+
*/
2324+
static bool
2325+
pgfdw_conn_checkable(void)
2326+
{
2327+
#if (defined(HAVE_POLL) && defined(POLLRDHUP))
2328+
return true;
2329+
#else
2330+
return false;
2331+
#endif
2332+
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 50 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -10464,10 +10464,10 @@ drop cascades to foreign table ft7
1046410464
-- should be output as invalid connections. Also the server name for
1046510465
-- loopback3 should be NULL because the server was dropped.
1046610466
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
10467-
server_name | valid | used_in_xact
10468-
-------------+-------+--------------
10469-
loopback | f | t
10470-
| f | t
10467+
server_name | valid | used_in_xact | closed
10468+
-------------+-------+--------------+--------
10469+
loopback | f | t |
10470+
| f | t |
1047110471
(2 rows)
1047210472

1047310473
-- The invalid connections get closed in pgfdw_xact_callback during commit.
@@ -12286,3 +12286,49 @@ ANALYZE analyze_table;
1228612286
-- cleanup
1228712287
DROP FOREIGN TABLE analyze_ftable;
1228812288
DROP TABLE analyze_table;
12289+
-- ===================================================================
12290+
-- test for postgres_fdw_get_connections function with check_conn = true
12291+
-- ===================================================================
12292+
-- Disable debug_discard_caches in order to manage remote connections
12293+
SET debug_discard_caches TO '0';
12294+
-- The text of the error might vary across platforms, so only show SQLSTATE.
12295+
\set VERBOSITY sqlstate
12296+
SELECT 1 FROM postgres_fdw_disconnect_all();
12297+
?column?
12298+
----------
12299+
1
12300+
(1 row)
12301+
12302+
ALTER SERVER loopback OPTIONS (SET application_name 'fdw_conn_check');
12303+
SELECT 1 FROM ft1 LIMIT 1;
12304+
?column?
12305+
----------
12306+
1
12307+
(1 row)
12308+
12309+
-- Since the remote server is still connected, "closed" should be FALSE,
12310+
-- or NULL if the connection status check is not available.
12311+
SELECT CASE WHEN closed IS NOT true THEN 1 ELSE 0 END
12312+
FROM postgres_fdw_get_connections(true);
12313+
case
12314+
------
12315+
1
12316+
(1 row)
12317+
12318+
-- After terminating the remote backend, since the connection is closed,
12319+
-- "closed" should be TRUE, or NULL if the connection status check
12320+
-- is not available.
12321+
DO $$ BEGIN
12322+
PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
12323+
WHERE application_name = 'fdw_conn_check';
12324+
END $$;
12325+
SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END
12326+
FROM postgres_fdw_get_connections(true);
12327+
case
12328+
------
12329+
1
12330+
(1 row)
12331+
12332+
-- Clean up
12333+
\set VERBOSITY default
12334+
RESET debug_discard_caches;

‎contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,8 +9,9 @@ ALTER EXTENSION postgres_fdw DROP FUNCTION postgres_fdw_get_connections ();
99
/* Then we can drop it */
1010
DROP FUNCTION postgres_fdw_get_connections ();
1111

12-
CREATE FUNCTION postgres_fdw_get_connections (OUT server_name text,
13-
OUT valid boolean, OUT used_in_xact boolean)
12+
CREATE FUNCTION postgres_fdw_get_connections (
13+
IN check_conn boolean DEFAULT false, OUT server_name text,
14+
OUT valid boolean, OUT used_in_xact boolean, OUT closed boolean)
1415
RETURNS SETOF record
1516
AS 'MODULE_PATHNAME', 'postgres_fdw_get_connections_1_2'
1617
LANGUAGE C STRICT PARALLEL RESTRICTED;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4235,3 +4235,36 @@ ANALYZE analyze_table;
42354235
-- cleanup
42364236
DROP FOREIGN TABLE analyze_ftable;
42374237
DROP TABLE analyze_table;
4238+
4239+
-- ===================================================================
4240+
-- test for postgres_fdw_get_connections function with check_conn = true
4241+
-- ===================================================================
4242+
4243+
-- Disable debug_discard_caches in order to manage remote connections
4244+
SET debug_discard_caches TO '0';
4245+
4246+
-- The text of the error might vary across platforms, so only show SQLSTATE.
4247+
\set VERBOSITY sqlstate
4248+
4249+
SELECT 1 FROM postgres_fdw_disconnect_all();
4250+
ALTER SERVER loopback OPTIONS (SET application_name 'fdw_conn_check');
4251+
SELECT 1 FROM ft1 LIMIT 1;
4252+
4253+
-- Since the remote server is still connected, "closed" should be FALSE,
4254+
-- or NULL if the connection status check is not available.
4255+
SELECT CASE WHEN closed IS NOT true THEN 1 ELSE 0 END
4256+
FROM postgres_fdw_get_connections(true);
4257+
4258+
-- After terminating the remote backend, since the connection is closed,
4259+
-- "closed" should be TRUE, or NULL if the connection status check
4260+
-- is not available.
4261+
DO $$ BEGIN
4262+
PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity
4263+
WHERE application_name = 'fdw_conn_check';
4264+
END $$;
4265+
SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END
4266+
FROM postgres_fdw_get_connections(true);
4267+
4268+
-- Clean up
4269+
\set VERBOSITY default
4270+
RESET debug_discard_caches;

‎doc/src/sgml/postgres-fdw.sgml

Lines changed: 35 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -777,21 +777,39 @@ OPTIONS (ADD password_required 'false');
777777

778778
<variablelist>
779779
<varlistentry>
780-
<term><function>postgres_fdw_get_connections(OUT server_name text,
781-
OUT valid boolean, OUT used_in_xact boolean)
780+
<term><function>postgres_fdw_get_connections(
781+
IN check_conn boolean DEFAULT false, OUT server_name text,
782+
OUT valid boolean, OUT used_in_xact boolean, OUT closed boolean)
782783
returns setof record</function></term>
783784
<listitem>
784785
<para>
785786
This function returns information about all open connections postgres_fdw
786787
has established from the local session to foreign servers. If there are
787788
no open connections, no records are returned.
789+
</para>
790+
<para>
791+
If <literal>check_conn</literal> is set to <literal>true</literal>,
792+
the function checks the status of each connection and shows
793+
the result in the <literal>closed</literal> column.
794+
This feature is currently available only on systems that support
795+
the non-standard <symbol>POLLRDHUP</symbol> extension to
796+
the <symbol>poll</symbol> system call, including Linux.
797+
This is useful to check if all connections used within
798+
a transaction are still open. If any connections are closed,
799+
the transaction cannot be committed successfully,
800+
so it is better to roll back as soon as a closed connection is detected,
801+
rather than continuing to the end. Users can roll back the transaction
802+
immediately if the function reports connections where both
803+
<literal>used_in_xact</literal> and <literal>closed</literal> are
804+
<literal>true</literal>.
805+
</para>
806+
<para>
788807
Example usage of the function:
789808
<screen>
790-
postgres=*# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
791-
server_name | valid | used_in_xact
792-
-------------+-------+--------------
793-
loopback1 | t | t
794-
loopback2 | f | t
809+
server_name | valid | used_in_xact | closed
810+
-------------+-------+--------------+--------
811+
loopback1 | t | t |
812+
loopback2 | f | t |
795813
</screen>
796814
The output columns are described in
797815
<xref linkend="postgres-fdw-get-connections-columns"/>.
@@ -836,6 +854,16 @@ postgres=*# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
836854
True if this connection is used in the current transaction.
837855
</entry>
838856
</row>
857+
<row>
858+
<entry><structfield>closed</structfield></entry>
859+
<entry><type>boolean</type></entry>
860+
<entry>
861+
True if this connection is closed, false otherwise.
862+
<literal>NULL</literal> is returned if <literal>check_conn</literal>
863+
is set to <literal>false</literal> or if the connection status check
864+
is not available on this platform.
865+
</entry>
866+
</row>
839867
</tbody>
840868
</tgroup>
841869
</table>

0 commit comments

Comments
 (0)