Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit857df3c

Browse files
committed
postgres_fdw: Add connection status check to postgres_fdw_get_connections().
This commit extends the postgres_fdw_get_connections() functionto check if connections are closed. This is useful for detecting closedpostgres_fdw connections that could prevent successful transactioncommits. Users can roll back transactions immediately upon detectingclosed connections, avoiding unnecessary processing of failedtransactions.This feature is available only on systems supporting the non-standardPOLLRDHUP extension to the poll system call, including Linux.Author: Hayato KurodaReviewed-by: Shinya Kato, Zhihong Yu, Kyotaro Horiguchi, Andres FreundReviewed-by: Onder Kalaci, Takamichi Osumi, Vignesh C, Tom Lane, Ted YuReviewed-by: Katsuragi Yuta, Peter Smith, Shubham Khanna, Fujii MasaoDiscussion:https://postgr.es/m/TYAPR01MB58662809E678253B90E82CE5F5889@TYAPR01MB5866.jpnprd01.prod.outlook.com
1 parentc297a47 commit857df3c

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+
#ifHAVE_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
staticbooldisconnect_cached_connections(Oidserverid);
172176
staticvoidpostgres_fdw_get_connections_internal(FunctionCallInfofcinfo,
173177
enumpgfdwVersionapi_version);
178+
staticintpgfdw_conn_check(PGconn*conn);
179+
staticboolpgfdw_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
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS_V1_12
1994-
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS_V1_23
1995-
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS3/* maximum of above */
2000+
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS_V1_24
2001+
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS4/* maximum of above */
19962002

19972003
/*
19982004
* Internal function used by postgres_fdw_get_connections variants.
19992005
*
2000-
* For API version 1.1, this functionreturns a set of records with
2001-
* the following values:
2006+
* For API version 1.1, this functiontakes no input parameter and
2007+
*returns a set of records withthe 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+
boolcheck_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
returnresult;
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+
staticint
2289+
pgfdw_conn_check(PGconn*conn)
2290+
{
2291+
intsock=PQsocket(conn);
2292+
2293+
if (PQstatus(conn)!=CONNECTION_OK||sock==-1)
2294+
return-1;
2295+
2296+
#if (defined(HAVE_POLL)&& defined(POLLRDHUP))
2297+
{
2298+
structpollfdinput_fd;
2299+
intresult;
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+
return0;
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+
staticbool
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
DROPFUNCTION postgres_fdw_get_connections ();
1111

12-
CREATEFUNCTIONpostgres_fdw_get_connections (OUT server_nametext,
13-
OUT validboolean, OUT used_in_xactboolean)
12+
CREATEFUNCTIONpostgres_fdw_get_connections (
13+
IN check_connboolean DEFAULT false, OUT server_nametext,
14+
OUT validboolean, OUT used_in_xactboolean, OUT closedboolean)
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
DROPTABLE 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+
SELECT1FROM postgres_fdw_disconnect_all();
4250+
ALTER SERVER loopback OPTIONS (SET application_name'fdw_conn_check');
4251+
SELECT1FROM ft1LIMIT1;
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 THEN1 ELSE0 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 THEN1 ELSE0 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)
[8]ページ先頭
©2009-2025 Movatter.jp