Skip to content

Commit c297a47

Browse files
MasaoFujiiMasaoFujii
committed
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. Author: Hayato Kuroda Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/be9382f7-5072-4760-8b3f-31d6dffa8d62@oss.nttdata.com
1 parent 5687f8c commit c297a47

File tree

7 files changed

+112
-20
lines changed

7 files changed

+112
-20
lines changed

‎contrib/postgres_fdw/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@ PG_CPPFLAGS = -I$(libpq_srcdir)
1414
SHLIB_LINK_INTERNAL = $(libpq)
1515

1616
EXTENSION = postgres_fdw
17-
DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql
17+
DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql postgres_fdw--1.1--1.2.sql
1818

1919
REGRESS = postgres_fdw query_cancel
2020

‎contrib/postgres_fdw/connection.c

Lines changed: 74 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -107,10 +107,20 @@ static uint32 pgfdw_we_get_result = 0;
107107
(entry)->xact_depth, (entry)->xact_depth); \
108108
} while(0)
109109

110+
/*
111+
* Extension version number, for supporting older extension versions' objects
112+
*/
113+
enum pgfdwVersion
114+
{
115+
PGFDW_V1_1 = 0,
116+
PGFDW_V1_2,
117+
};
118+
110119
/*
111120
* SQL functions
112121
*/
113122
PG_FUNCTION_INFO_V1(postgres_fdw_get_connections);
123+
PG_FUNCTION_INFO_V1(postgres_fdw_get_connections_1_2);
114124
PG_FUNCTION_INFO_V1(postgres_fdw_disconnect);
115125
PG_FUNCTION_INFO_V1(postgres_fdw_disconnect_all);
116126

@@ -159,6 +169,8 @@ static void pgfdw_security_check(const char **keywords, const char **values,
159169
UserMapping *user, PGconn *conn);
160170
static bool UserMappingPasswordRequired(UserMapping *user);
161171
static bool disconnect_cached_connections(Oid serverid);
172+
static void postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
173+
enum pgfdwVersion api_version);
162174

163175
/*
164176
* 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,
19771989
}
19781990
}
19791991

1992+
/* Number of output arguments (columns) for various API versions */
1993+
#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 */
1996+
19801997
/*
1981-
* List active foreign server connections.
1998+
* Internal function used by postgres_fdw_get_connections variants.
1999+
*
2000+
* For API version 1.1, this function returns a set of records with
2001+
* the following values:
19822002
*
1983-
* This function takes no input parameter and returns setof record made of
1984-
* following values:
19852003
* - server_name - server name of active connection. In case the foreign server
19862004
* is dropped but still the connection is active, then the server name will
19872005
* be NULL in output.
19882006
* - valid - true/false representing whether the connection is valid or not.
1989-
* Note that the connections can get invalidated in pgfdw_inval_callback.
2007+
* Note that connections can become invalid in pgfdw_inval_callback.
2008+
*
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:
2011+
*
2012+
* - used_in_xact - true if the connection is used in the current transaction.
19902013
*
19912014
* No records are returned when there are no cached connections at all.
19922015
*/
1993-
Datum
1994-
postgres_fdw_get_connections(PG_FUNCTION_ARGS)
2016+
static void
2017+
postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
2018+
enum pgfdwVersion api_version)
19952019
{
1996-
#define POSTGRES_FDW_GET_CONNECTIONS_COLS 2
19972020
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
19982021
HASH_SEQ_STATUS scan;
19992022
ConnCacheEntry *entry;
@@ -2002,7 +2025,22 @@ postgres_fdw_get_connections(PG_FUNCTION_ARGS)
20022025

20032026
/* If cache doesn't exist, we return no records */
20042027
if (!ConnectionHash)
2005-
PG_RETURN_VOID();
2028+
return;
2029+
2030+
/* Check we have the expected number of output arguments */
2031+
switch (rsinfo->setDesc->natts)
2032+
{
2033+
case POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_1:
2034+
if (api_version != PGFDW_V1_1)
2035+
elog(ERROR, "incorrect number of output arguments");
2036+
break;
2037+
case POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2:
2038+
if (api_version != PGFDW_V1_2)
2039+
elog(ERROR, "incorrect number of output arguments");
2040+
break;
2041+
default:
2042+
elog(ERROR, "incorrect number of output arguments");
2043+
}
20062044

20072045
hash_seq_init(&scan, ConnectionHash);
20082046
while ((entry = (ConnCacheEntry *) hash_seq_search(&scan)))
@@ -2061,8 +2099,36 @@ postgres_fdw_get_connections(PG_FUNCTION_ARGS)
20612099

20622100
values[1] = BoolGetDatum(!entry->invalidated);
20632101

2102+
if (api_version >= PGFDW_V1_2)
2103+
{
2104+
/* Is this connection used in the current transaction? */
2105+
values[2] = BoolGetDatum(entry->xact_depth > 0);
2106+
}
2107+
20642108
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
20652109
}
2110+
}
2111+
2112+
/*
2113+
* List active foreign server connections.
2114+
*
2115+
* The SQL API of this function has changed multiple times, and will likely
2116+
* do so again in future. To support the case where a newer version of this
2117+
* loadable module is being used with an old SQL declaration of the function,
2118+
* we continue to support the older API versions.
2119+
*/
2120+
Datum
2121+
postgres_fdw_get_connections_1_2(PG_FUNCTION_ARGS)
2122+
{
2123+
postgres_fdw_get_connections_internal(fcinfo, PGFDW_V1_2);
2124+
2125+
PG_RETURN_VOID();
2126+
}
2127+
2128+
Datum
2129+
postgres_fdw_get_connections(PG_FUNCTION_ARGS)
2130+
{
2131+
postgres_fdw_get_connections_internal(fcinfo, PGFDW_V1_1);
20662132

20672133
PG_RETURN_VOID();
20682134
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 4 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
10468-
-------------+-------
10469-
loopback | f
10470-
| f
10467+
server_name | valid | used_in_xact
10468+
-------------+-------+--------------
10469+
loopback | f | t
10470+
| f | t
1047110471
(2 rows)
1047210472

1047310473
-- The invalid connections get closed in pgfdw_xact_callback during commit.

‎contrib/postgres_fdw/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@ install_data(
2626
'postgres_fdw.control',
2727
'postgres_fdw--1.0.sql',
2828
'postgres_fdw--1.0--1.1.sql',
29+
'postgres_fdw--1.1--1.2.sql',
2930
kwargs: contrib_data_args,
3031
)
3132

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
/* contrib/postgres_fdw/postgres_fdw--1.1--1.2.sql */
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use "ALTER EXTENSION postgres_fdw UPDATE TO '1.2'" to load this file. \quit
5+
6+
/* First we have to remove it from the extension */
7+
ALTER EXTENSION postgres_fdw DROP FUNCTION postgres_fdw_get_connections ();
8+
9+
/* Then we can drop it */
10+
DROP FUNCTION postgres_fdw_get_connections ();
11+
12+
CREATE FUNCTION postgres_fdw_get_connections (OUT server_name text,
13+
OUT valid boolean, OUT used_in_xact boolean)
14+
RETURNS SETOF record
15+
AS 'MODULE_PATHNAME', 'postgres_fdw_get_connections_1_2'
16+
LANGUAGE C STRICT PARALLEL RESTRICTED;

‎contrib/postgres_fdw/postgres_fdw.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# postgres_fdw extension
22
comment = 'foreign-data wrapper for remote PostgreSQL servers'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/postgres_fdw'
55
relocatable = true

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

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

778778
<variablelist>
779779
<varlistentry>
780-
<term><function>postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) returns setof record</function></term>
780+
<term><function>postgres_fdw_get_connections(OUT server_name text,
781+
OUT valid boolean, OUT used_in_xact boolean)
782+
returns setof record</function></term>
781783
<listitem>
782784
<para>
783785
This function returns information about all open connections postgres_fdw
784786
has established from the local session to foreign servers. If there are
785787
no open connections, no records are returned.
786788
Example usage of the function:
787789
<screen>
788-
postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
789-
server_name | valid
790-
-------------+-------
791-
loopback1 | t
792-
loopback2 | f
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
793795
</screen>
794796
The output columns are described in
795797
<xref linkend="postgres-fdw-get-connections-columns"/>.
@@ -827,6 +829,13 @@ postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
827829
the transaction. True is returned otherwise.
828830
</entry>
829831
</row>
832+
<row>
833+
<entry><structfield>used_in_xact</structfield></entry>
834+
<entry><type>boolean</type></entry>
835+
<entry>
836+
True if this connection is used in the current transaction.
837+
</entry>
838+
</row>
830839
</tbody>
831840
</tgroup>
832841
</table>

0 commit comments

Comments
 (0)