Skip to content

Commit b1be307

Browse files
MasaoFujiiMasaoFujii
committed
postgres_fdw: Add option to control whether to keep connections open.
This commit adds a new option keep_connections that controls whether postgres_fdw keeps the connections to the foreign server open so that the subsequent queries can re-use them. This option can only be specified for a foreign server. The default is on. If set to off, all connections to the foreign server will be discarded at the end of transaction. Closed connections will be re-established when they are necessary by future queries using a foreign table. This option is useful, for example, when users want to prevent the connections from eating up the foreign servers connections capacity. Author: Bharath Rupireddy Reviewed-by: Alexey Kondratov, Vignesh C, Fujii Masao Discussion: https://postgr.es/m/CALj2ACVvrp5=AVp2PupEm+nAC8S4buqR3fJMmaCoc7ftT0aD2A@mail.gmail.com
1 parent 9c5f67f commit b1be307

File tree

5 files changed

+101
-9
lines changed

5 files changed

+101
-9
lines changed

‎contrib/postgres_fdw/connection.c

Lines changed: 29 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,8 @@ typedef struct ConnCacheEntry
5959
bool have_error; /* have any subxacts aborted in this xact? */
6060
bool changing_xact_state; /* xact state change in process */
6161
bool invalidated; /* true if reconnect is pending */
62+
bool keep_connections; /* setting value of keep_connections
63+
* server option */
6264
Oid serverid; /* foreign server OID used to get server name */
6365
uint32 server_hashvalue; /* hash value of foreign server OID */
6466
uint32 mapping_hashvalue; /* hash value of user mapping OID */
@@ -286,6 +288,7 @@ static void
286288
make_new_connection(ConnCacheEntry *entry, UserMapping *user)
287289
{
288290
ForeignServer *server = GetForeignServer(user->serverid);
291+
ListCell *lc;
289292

290293
Assert(entry->conn == NULL);
291294

@@ -304,6 +307,26 @@ make_new_connection(ConnCacheEntry *entry, UserMapping *user)
304307
ObjectIdGetDatum(user->umid));
305308
memset(&entry->state, 0, sizeof(entry->state));
306309

310+
/*
311+
* Determine whether to keep the connection that we're about to make here
312+
* open even after the transaction using it ends, so that the subsequent
313+
* transactions can re-use it.
314+
*
315+
* It's enough to determine this only when making new connection because
316+
* all the connections to the foreign server whose keep_connections option
317+
* is changed will be closed and re-made later.
318+
*
319+
* By default, all the connections to any foreign servers are kept open.
320+
*/
321+
entry->keep_connections = true;
322+
foreach(lc, server->options)
323+
{
324+
DefElem *def = (DefElem *) lfirst(lc);
325+
326+
if (strcmp(def->defname, "keep_connections") == 0)
327+
entry->keep_connections = defGetBoolean(def);
328+
}
329+
307330
/* Now try to make the connection */
308331
entry->conn = connect_pg_server(server, user);
309332

@@ -970,14 +993,16 @@ pgfdw_xact_callback(XactEvent event, void *arg)
970993
entry->xact_depth = 0;
971994

972995
/*
973-
* If the connection isn't in a good idle state or it is marked as
974-
* invalid, then discard it to recover. Next GetConnection will open a
975-
* new connection.
996+
* If the connection isn't in a good idle state, it is marked as
997+
* invalid or keep_connections option of its server is disabled, then
998+
* discard it to recover. Next GetConnection will open a new
999+
* connection.
9761000
*/
9771001
if (PQstatus(entry->conn) != CONNECTION_OK ||
9781002
PQtransactionStatus(entry->conn) != PQTRANS_IDLE ||
9791003
entry->changing_xact_state ||
980-
entry->invalidated)
1004+
entry->invalidated ||
1005+
!entry->keep_connections)
9811006
{
9821007
elog(DEBUG3, "discarding connection %p", entry->conn);
9831008
disconnect_pg_server(entry);

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8913,7 +8913,7 @@ DO $d$
89138913
END;
89148914
$d$;
89158915
ERROR: invalid option "password"
8916-
HINT: Valid options in this context are: service, passfile, channel_binding, connect_timeout, dbname, host, hostaddr, port, options, application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tcp_user_timeout, sslmode, sslcompression, sslcert, sslkey, sslrootcert, sslcrl, sslcrldir, requirepeer, ssl_min_protocol_version, ssl_max_protocol_version, gssencmode, krbsrvname, gsslib, target_session_attrs, use_remote_estimate, fdw_startup_cost, fdw_tuple_cost, extensions, updatable, fetch_size, batch_size, async_capable
8916+
HINT: Valid options in this context are: service, passfile, channel_binding, connect_timeout, dbname, host, hostaddr, port, options, application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tcp_user_timeout, sslmode, sslcompression, sslcert, sslkey, sslrootcert, sslcrl, sslcrldir, requirepeer, ssl_min_protocol_version, ssl_max_protocol_version, gssencmode, krbsrvname, gsslib, target_session_attrs, use_remote_estimate, fdw_startup_cost, fdw_tuple_cost, extensions, updatable, fetch_size, batch_size, async_capable, keep_connections
89178917
CONTEXT: SQL statement "ALTER SERVER loopback_nopw OPTIONS (ADD password 'dummypw')"
89188918
PL/pgSQL function inline_code_block line 3 at EXECUTE
89198919
-- If we add a password for our user mapping instead, we should get a different
@@ -9249,6 +9249,27 @@ DROP USER MAPPING FOR regress_multi_conn_user2 SERVER loopback;
92499249
DROP ROLE regress_multi_conn_user1;
92509250
DROP ROLE regress_multi_conn_user2;
92519251
-- ===================================================================
9252+
-- Test foreign server level option keep_connections
9253+
-- ===================================================================
9254+
-- By default, the connections associated with foreign server are cached i.e.
9255+
-- keep_connections option is on. Set it to off.
9256+
ALTER SERVER loopback OPTIONS (keep_connections 'off');
9257+
-- connection to loopback server is closed at the end of xact
9258+
-- as keep_connections was set to off.
9259+
SELECT 1 FROM ft1 LIMIT 1;
9260+
?column?
9261+
----------
9262+
1
9263+
(1 row)
9264+
9265+
-- No cached connections, so no records should be output.
9266+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
9267+
server_name
9268+
-------------
9269+
(0 rows)
9270+
9271+
ALTER SERVER loopback OPTIONS (SET keep_connections 'on');
9272+
-- ===================================================================
92529273
-- batch insert
92539274
-- ===================================================================
92549275
BEGIN;

‎contrib/postgres_fdw/option.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -108,7 +108,8 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
108108
*/
109109
if (strcmp(def->defname, "use_remote_estimate") == 0 ||
110110
strcmp(def->defname, "updatable") == 0 ||
111-
strcmp(def->defname, "async_capable") == 0)
111+
strcmp(def->defname, "async_capable") == 0 ||
112+
strcmp(def->defname, "keep_connections") == 0)
112113
{
113114
/* these accept only boolean values */
114115
(void) defGetBoolean(def);
@@ -221,6 +222,7 @@ InitPgFdwOptions(void)
221222
/* async_capable is available on both server and table */
222223
{"async_capable", ForeignServerRelationId, false},
223224
{"async_capable", ForeignTableRelationId, false},
225+
{"keep_connections", ForeignServerRelationId, false},
224226
{"password_required", UserMappingRelationId, false},
225227

226228
/*

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2821,6 +2821,19 @@ DROP USER MAPPING FOR regress_multi_conn_user2 SERVER loopback;
28212821
DROP ROLE regress_multi_conn_user1;
28222822
DROP ROLE regress_multi_conn_user2;
28232823

2824+
-- ===================================================================
2825+
-- Test foreign server level option keep_connections
2826+
-- ===================================================================
2827+
-- By default, the connections associated with foreign server are cached i.e.
2828+
-- keep_connections option is on. Set it to off.
2829+
ALTER SERVER loopback OPTIONS (keep_connections 'off');
2830+
-- connection to loopback server is closed at the end of xact
2831+
-- as keep_connections was set to off.
2832+
SELECT 1 FROM ft1 LIMIT 1;
2833+
-- No cached connections, so no records should be output.
2834+
SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
2835+
ALTER SERVER loopback OPTIONS (SET keep_connections 'on');
2836+
28242837
-- ===================================================================
28252838
-- batch insert
28262839
-- ===================================================================

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

Lines changed: 34 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -518,6 +518,33 @@ OPTIONS (ADD password_required 'false');
518518
</para>
519519

520520
</sect3>
521+
522+
<sect3>
523+
<title>Connection Management Options</title>
524+
525+
<para>
526+
By default all the open connections that <filename>postgres_fdw</filename>
527+
established to the foreign servers are kept in local session for re-use.
528+
</para>
529+
530+
<variablelist>
531+
532+
<varlistentry>
533+
<term><literal>keep_connections</literal></term>
534+
<listitem>
535+
<para>
536+
This option controls whether <filename>postgres_fdw</filename> keeps
537+
the connections to the foreign server open so that the subsequent
538+
queries can re-use them. It can only be specified for a foreign server.
539+
The default is <literal>on</literal>. If set to <literal>off</literal>,
540+
all connections to this foreign server will be discarded at the end of
541+
transaction.
542+
</para>
543+
</listitem>
544+
</varlistentry>
545+
546+
</variablelist>
547+
</sect3>
521548
</sect2>
522549

523550
<sect2>
@@ -605,8 +632,10 @@ postgres=# SELECT postgres_fdw_disconnect_all();
605632
<para>
606633
<filename>postgres_fdw</filename> establishes a connection to a
607634
foreign server during the first query that uses a foreign table
608-
associated with the foreign server. This connection is kept and
609-
re-used for subsequent queries in the same session. However, if
635+
associated with the foreign server. By default this connection
636+
is kept and re-used for subsequent queries in the same session.
637+
This behavior can be controlled using
638+
<literal>keep_connections</literal> option for a foreign server. If
610639
multiple user identities (user mappings) are used to access the foreign
611640
server, a connection is established for each user mapping.
612641
</para>
@@ -622,8 +651,10 @@ postgres=# SELECT postgres_fdw_disconnect_all();
622651

623652
<para>
624653
Once a connection to a foreign server has been established,
625-
it's usually kept until the local or corresponding remote
654+
it's by default kept until the local or corresponding remote
626655
session exits. To disconnect a connection explicitly,
656+
<literal>keep_connections</literal> option for a foreign server
657+
may be disabled, or
627658
<function>postgres_fdw_disconnect</function> and
628659
<function>postgres_fdw_disconnect_all</function> functions
629660
may be used. For example, these are useful to close

0 commit comments

Comments
 (0)