#definePOSTGRES_FDW_GET_CONNECTIONS_COLS_V1_12

values[i++]=BoolGetDatum(pgfdw_conn_check(entry->conn)!=0);

nulls[i++]= true;

values[i++]=Int32GetDatum(PQbackendPID(entry->conn));

}

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

-- List all the existing cached connections. loopback and loopback3

-- should be output as invalid connections. Also the server name and user name

-- for loopback3 should be NULL because both server and user mapping were

-- dropped.

SELECT server_name, user_name = CURRENT_USER as "user_name = CURRENT_USER", valid, used_in_xact, closed

FROM postgres_fdw_get_connections() ORDER BY 1;

server_name | user_name = CURRENT_USER | valid | used_in_xact | closed

-------------+--------------------------+-------+--------------+--------

loopback | t | f | t |

| | f | t |

-- dropped. In this test, the PIDs of remote backends can be gathered from

-- pg_stat_activity, and remote_backend_pid should match one of those PIDs.

SELECT server_name, user_name = CURRENT_USER as "user_name = CURRENT_USER",

valid, used_in_xact, closed,

remote_backend_pid = ANY(SELECT pid FROM pg_stat_activity

WHERE backend_type = 'client backend' AND pid <> pg_backend_pid())

as remote_backend_pid

FROM postgres_fdw_get_connections() ORDER BY 1;

server_name | user_name = CURRENT_USER | valid | used_in_xact | closed | remote_backend_pid

-------------+--------------------------+-------+--------------+--------+--------------------

loopback | t | f | t | | t

| | f | t | | t

(2 rows)

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

-- Since the remote server is still connected, "closed" should be FALSE,

-- or NULL if the connection status check is not available.

SELECT CASE WHEN closed IS NOT true THEN 1 ELSE 0 END

-- In this test, the remote backend handling this connection should have

-- application_name set to "fdw_conn_check", so remote_backend_pid should

-- match the PID from the pg_stat_activity entry with that application_name.

SELECT server_name,

CASE WHEN closed IS NOT true THEN false ELSE true END AS closed,

remote_backend_pid = (SELECT pid FROM pg_stat_activity

WHERE application_name = 'fdw_conn_check') AS remote_backend_pid

FROM postgres_fdw_get_connections(true);

------

-------------+--------+--------------------

(1 row)

-- After terminating the remote backend, since the connection is closed,

-- "closed" should be TRUE, or NULL if the connection status check

-- is not available.

-- is not available. Despite the termination, remote_backend_pid should

-- still show the non-zero PID of the terminated remote backend.

DO $$ BEGIN

PERFORM pg_terminate_backend(pid, 180000) FROM pg_stat_activity

WHERE application_name = 'fdw_conn_check';

END $$;

SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END

SELECT server_name,

CASE WHEN closed IS NOT false THEN true ELSE false END AS closed,

remote_backend_pid <> 0 AS remote_backend_pid

FROM postgres_fdw_get_connections(true);

------

-------------+--------+--------------------

(1 row)

-- Clean up

CREATEFUNCTIONpostgres_fdw_get_connections (

IN check_connboolean DEFAULT false, OUT server_nametext,

OUT user_nametext, OUT validboolean, OUT used_in_xactboolean,

OUT closedboolean)

OUT closedboolean, OUT remote_backend_pid int4)

RETURNS SETOF record

AS'MODULE_PATHNAME','postgres_fdw_get_connections_1_2'

LANGUAGE C STRICT PARALLEL RESTRICTED;

SELECT server_name, user_name=CURRENT_USERas"user_name = CURRENT_USER", valid, used_in_xact, closed

FROM postgres_fdw_get_connections()ORDER BY1;

SELECT server_name, user_name=CURRENT_USERas"user_name = CURRENT_USER",

valid, used_in_xact, closed,

remote_backend_pid= ANY(SELECT pidFROM pg_stat_activity

WHERE backend_type='client backend'AND pid<> pg_backend_pid())

as remote_backend_pid

FROM postgres_fdw_get_connections()ORDER BY1;

COMMIT;

SELECT CASE WHEN closed IS NOT true THEN1 ELSE0 END

SELECT server_name,

CASE WHEN closed IS NOT true THEN false ELSE true ENDAS closed,

remote_backend_pid= (SELECT pidFROM pg_stat_activity

WHERE application_name='fdw_conn_check')AS remote_backend_pid

FROM postgres_fdw_get_connections(true);

DO $$BEGIN

PERFORM pg_terminate_backend(pid,180000)FROM pg_stat_activity

WHERE application_name='fdw_conn_check';

END $$;

SELECT CASE WHEN closed IS NOT false THEN1 ELSE0 END

SELECT server_name,

CASE WHEN closed IS NOT false THEN true ELSE false ENDAS closed,

remote_backend_pid<>0AS remote_backend_pid

FROM postgres_fdw_get_connections(true);

<term><function>postgres_fdw_get_connections(

IN check_conn boolean DEFAULT false, OUT server_name text,

OUT user_name text, OUT valid boolean, OUT used_in_xact boolean,

OUT closed boolean)

OUT closed boolean, OUT remote_backend_pid int4)

returns setof record</function></term>

<listitem>

<para>

Example usage of the function:

<screen>

postgres=# SELECT * FROM postgres_fdw_get_connections(true);

server_name | user_name | valid | used_in_xact | closed

-------------+-----------+-------+--------------+--------

loopback1 | postgres | t | t | f

loopback2 | public | t | t | f

loopback3 | | f | t | f

server_name | user_name | valid | used_in_xact | closed | remote_backend_pid

-------------+-----------+-------+--------------+-----------------------------

loopback1 | postgres | t | t | f | 1353340

loopback2 | public | t | t | f | 1353120

loopback3 | | f | t | f | 1353156

</screen>

The output columns are described in

<xref linkend="postgres-fdw-get-connections-columns"/>.

is not available on this platform.

</entry>

</row>

<row>

<entry><structfield>remote_backend_pid</structfield></entry>

<entry><type>int4</type></entry>

<entry>

Process ID of the remote backend, on the foreign server,

handling the connection. If the remote backend is terminated and

the connection is closed (with <literal>closed</literal> set to

<literal>true</literal>), this still shows the process ID of

the terminated backend.

</entry>

</row>

</tbody>

</tgroup>

</table>