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

Commit4f08ab5

Browse files
committed
postgres_fdw: Extend postgres_fdw_get_connections to return user name.
This commit adds a "user_name" output column tothe postgres_fdw_get_connections function, returning the nameof 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 commitc297a47already handled it for v18~.Author: Hayato KurodaReviewed-by: Fujii MasaoDiscussion:https://postgr.es/m/b492a935-6c7e-8c08-e485-3c1d64d7d10f@oss.nttdata.com
1 parentb14e9ce commit4f08ab5

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
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS_V1_12
2000-
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS_V1_24
2001-
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS4/* maximum of above */
2000+
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS_V1_25
2001+
#definePOSTGRES_FDW_GET_CONNECTIONS_COLS5/* 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 thetwo values from version 1.1:
2017+
* additional values along with thethree 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
Datumvalues[POSTGRES_FDW_GET_CONNECTIONS_COLS]= {0};
20582061
boolnulls[POSTGRES_FDW_GET_CONNECTIONS_COLS]= {0};
2062+
inti=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+
HeapTupletp;
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+
Oiduserid;
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
boolcheck_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
CREATEFUNCTIONpostgres_fdw_get_connections (
1313
IN check_connboolean DEFAULT false, OUT server_nametext,
14-
OUT validboolean, OUT used_in_xactboolean, OUT closedboolean)
14+
OUT user_nametext, OUT validboolean, OUT used_in_xactboolean,
15+
OUT closedboolean)
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 BY1;
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_USERas"user_name = CURRENT_USER", valid, used_in_xact, closed
3389+
FROM postgres_fdw_get_connections()ORDER BY1;
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)
[8]ページ先頭
©2009-2025 Movatter.jp