Skip to content

Commit 4f08ab5

Browse files
MasaoFujiiMasaoFujii
committed
postgres_fdw: Extend postgres_fdw_get_connections to return user name.
This commit adds a "user_name" output column to the postgres_fdw_get_connections function, returning the name of the local user mapped to the foreign server for each connection. If a public mapping is used, it returns "public." This helps identify postgres_fdw connections more easily, such as determining which connections are invalid, closed, or used within the current transaction. No extension version bump is needed, as commit c297a47 already handled it for v18~. Author: Hayato Kuroda Reviewed-by: Fujii Masao Discussion: https://postgr.es/m/b492a935-6c7e-8c08-e485-3c1d64d7d10f@oss.nttdata.com
1 parent b14e9ce commit 4f08ab5

File tree

5 files changed

+81
-26
lines changed

5 files changed

+81
-26
lines changed

‎contrib/postgres_fdw/connection.c

Lines changed: 47 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1997,8 +1997,8 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List *cancel_requested,
19971997

19981998
/* Number of output arguments (columns) for various API versions */
19991999
#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_1 2
2000-
#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2 4
2001-
#define POSTGRES_FDW_GET_CONNECTIONS_COLS 4 /* maximum of above */
2000+
#define POSTGRES_FDW_GET_CONNECTIONS_COLS_V1_2 5
2001+
#define POSTGRES_FDW_GET_CONNECTIONS_COLS 5 /* maximum of above */
20022002

20032003
/*
20042004
* Internal function used by postgres_fdw_get_connections variants.
@@ -2014,10 +2014,13 @@ pgfdw_finish_abort_cleanup(List *pending_entries, List *cancel_requested,
20142014
*
20152015
* For API version 1.2 and later, this function takes an input parameter
20162016
* to check a connection status and returns the following
2017-
* additional values along with the two values from version 1.1:
2017+
* additional values along with the three values from version 1.1:
20182018
*
2019+
* - user_name - the local user name of the active connection. In case the
2020+
* user mapping is dropped but the connection is still active, then the
2021+
* user name will be NULL in the output.
20192022
* - used_in_xact - true if the connection is used in the current transaction.
2020-
* - closed: true if the connection is closed.
2023+
* - closed - true if the connection is closed.
20212024
*
20222025
* No records are returned when there are no cached connections at all.
20232026
*/
@@ -2056,6 +2059,7 @@ postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
20562059
ForeignServer *server;
20572060
Datum values[POSTGRES_FDW_GET_CONNECTIONS_COLS] = {0};
20582061
bool nulls[POSTGRES_FDW_GET_CONNECTIONS_COLS] = {0};
2062+
int i = 0;
20592063

20602064
/* We only look for open remote connections */
20612065
if (!entry->conn)
@@ -2100,28 +2104,61 @@ postgres_fdw_get_connections_internal(FunctionCallInfo fcinfo,
21002104
Assert(entry->conn && entry->xact_depth > 0 && entry->invalidated);
21012105

21022106
/* Show null, if no server name was found */
2103-
nulls[0] = true;
2107+
nulls[i++] = true;
21042108
}
21052109
else
2106-
values[0] = CStringGetTextDatum(server->servername);
2110+
values[i++] = CStringGetTextDatum(server->servername);
21072111

2108-
values[1] = BoolGetDatum(!entry->invalidated);
2112+
if (api_version >= PGFDW_V1_2)
2113+
{
2114+
HeapTuple tp;
2115+
2116+
/* Use the system cache to obtain the user mapping */
2117+
tp = SearchSysCache1(USERMAPPINGOID, ObjectIdGetDatum(entry->key));
2118+
2119+
/*
2120+
* Just like in the foreign server case, user mappings can also be
2121+
* dropped in the current explicit transaction. Therefore, the
2122+
* similar check as in the server case is required.
2123+
*/
2124+
if (!HeapTupleIsValid(tp))
2125+
{
2126+
/*
2127+
* If we reach here, this entry must have been invalidated in
2128+
* pgfdw_inval_callback, same as in the server case.
2129+
*/
2130+
Assert(entry->conn && entry->xact_depth > 0 &&
2131+
entry->invalidated);
2132+
2133+
nulls[i++] = true;
2134+
}
2135+
else
2136+
{
2137+
Oid userid;
2138+
2139+
userid = ((Form_pg_user_mapping) GETSTRUCT(tp))->umuser;
2140+
values[i++] = CStringGetTextDatum(MappingUserName(userid));
2141+
ReleaseSysCache(tp);
2142+
}
2143+
}
2144+
2145+
values[i++] = BoolGetDatum(!entry->invalidated);
21092146

21102147
if (api_version >= PGFDW_V1_2)
21112148
{
21122149
bool check_conn = PG_GETARG_BOOL(0);
21132150

21142151
/* Is this connection used in the current transaction? */
2115-
values[2] = BoolGetDatum(entry->xact_depth > 0);
2152+
values[i++] = BoolGetDatum(entry->xact_depth > 0);
21162153

21172154
/*
21182155
* If a connection status check is requested and supported, return
21192156
* whether the connection is closed. Otherwise, return NULL.
21202157
*/
21212158
if (check_conn && pgfdw_conn_checkable())
2122-
values[3] = BoolGetDatum(pgfdw_conn_check(entry->conn) != 0);
2159+
values[i++] = BoolGetDatum(pgfdw_conn_check(entry->conn) != 0);
21232160
else
2124-
nulls[3] = true;
2161+
nulls[i++] = true;
21252162
}
21262163

21272164
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -10472,13 +10472,15 @@ NOTICE: drop cascades to 2 other objects
1047210472
DETAIL: drop cascades to user mapping for public on server loopback3
1047310473
drop cascades to foreign table ft7
1047410474
-- List all the existing cached connections. loopback and loopback3
10475-
-- should be output as invalid connections. Also the server name for
10476-
-- loopback3 should be NULL because the server was dropped.
10477-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
10478-
server_name | valid | used_in_xact | closed
10479-
-------------+-------+--------------+--------
10480-
loopback | f | t |
10481-
| f | t |
10475+
-- should be output as invalid connections. Also the server name and user name
10476+
-- for loopback3 should be NULL because both server and user mapping were
10477+
-- dropped.
10478+
SELECT server_name, user_name = CURRENT_USER as "user_name = CURRENT_USER", valid, used_in_xact, closed
10479+
FROM postgres_fdw_get_connections() ORDER BY 1;
10480+
server_name | user_name = CURRENT_USER | valid | used_in_xact | closed
10481+
-------------+--------------------------+-------+--------------+--------
10482+
loopback | t | f | t |
10483+
| | f | t |
1048210484
(2 rows)
1048310485

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

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,8 @@ DROP FUNCTION postgres_fdw_get_connections ();
1111

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

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3382,9 +3382,11 @@ SELECT server_name FROM postgres_fdw_get_connections() ORDER BY 1;
33823382
ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'off');
33833383
DROP SERVER loopback3 CASCADE;
33843384
-- List all the existing cached connections. loopback and loopback3
3385-
-- should be output as invalid connections. Also the server name for
3386-
-- loopback3 should be NULL because the server was dropped.
3387-
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
3385+
-- should be output as invalid connections. Also the server name and user name
3386+
-- for loopback3 should be NULL because both server and user mapping were
3387+
-- dropped.
3388+
SELECT server_name, user_name = CURRENT_USER as "user_name = CURRENT_USER", valid, used_in_xact, closed
3389+
FROM postgres_fdw_get_connections() ORDER BY 1;
33883390
-- The invalid connections get closed in pgfdw_xact_callback during commit.
33893391
COMMIT;
33903392
-- All cached connections were closed while committing above xact, so no

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

Lines changed: 18 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -779,7 +779,8 @@ OPTIONS (ADD password_required 'false');
779779
<varlistentry>
780780
<term><function>postgres_fdw_get_connections(
781781
IN check_conn boolean DEFAULT false, OUT server_name text,
782-
OUT valid boolean, OUT used_in_xact boolean, OUT closed boolean)
782+
OUT user_name text, OUT valid boolean, OUT used_in_xact boolean,
783+
OUT closed boolean)
783784
returns setof record</function></term>
784785
<listitem>
785786
<para>
@@ -806,10 +807,12 @@ OPTIONS (ADD password_required 'false');
806807
<para>
807808
Example usage of the function:
808809
<screen>
809-
server_name | valid | used_in_xact | closed
810-
-------------+-------+--------------+--------
811-
loopback1 | t | t |
812-
loopback2 | f | t |
810+
postgres=# SELECT * FROM postgres_fdw_get_connections(true);
811+
server_name | user_name | valid | used_in_xact | closed
812+
-------------+-----------+-------+--------------+--------
813+
loopback1 | postgres | t | t | f
814+
loopback2 | public | t | t | f
815+
loopback3 | | f | t | f
813816
</screen>
814817
The output columns are described in
815818
<xref linkend="postgres-fdw-get-connections-columns"/>.
@@ -836,6 +839,16 @@ OPTIONS (ADD password_required 'false');
836839
invalid), this will be <literal>NULL</literal>.
837840
</entry>
838841
</row>
842+
<row>
843+
<entry><structfield>user_name</structfield></entry>
844+
<entry><type>text</type></entry>
845+
<entry>
846+
Name of the local user mapped to the foreign server of this
847+
connection, or <literal>public</literal> if a public mapping is used.
848+
If the user mapping is dropped but the connection remains open
849+
(i.e., marked as invalid), this will be <literal>NULL</literal>.
850+
</entry>
851+
</row>
839852
<row>
840853
<entry><structfield>valid</structfield></entry>
841854
<entry><type>boolean</type></entry>

0 commit comments

Comments
 (0)