Dear Hackers, I want to propose to extend existing postgres_fdw_get_connections functionality to return remote server backend pid. Using postgres_fdw extension, backend can establish connections to remote postgres servers. Recently we added functionality to get connection status which can help users to detect closed connections immediately. But currently there is no way to get a remote backend PID for these connections. Postgres backend (Coordinator) serving global session can establish connections with multiple backends (worker) from the same server or different servers. This information is useful for monitoring and reporting purposes. Using coordinator backend PID and worker backend PID information, users can build a global session/transaction view, detect distributed deadlock, and build distributed cumulative statistics.
=======
Example
=======
```
test=# select * from postgres_fdw_get_connections('true');
server_name | user_name | valid | used_in_xact | closed | remote_backend_pid
------------------+----------------+-------+------------------+----------+----------------------------
loopback | ubuntu | t | f |
f | 1126051
loopback_1 | ubuntu | t | f | f
| 1126103
(2 rows)
test=# select pg_terminate_backend(pid, 180000) FROM pg_stat_activity
WHERE application_name = 'fdw_conn_check';
pg_terminate_backend
----------------------
t
(1 row)
test=# select * from postgres_fdw_get_connections('true');
server_name | user_name | valid | used_in_xact | closed | remote_backend_pid
------------------+----------------+-------+------------------+----------+----------------------------
loopback | ubuntu | t | f | t
|
loopback_1 | ubuntu | t | f | f
| 1126103
(2 rows)
```
--
Sagar Dilip Shedge,
Pune.
With Regards.
v01_add_checking_infrastracture.patch
Description: Binary data
