From: | Sagar Shedge <sagar(dot)shedge92(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Extend postgres_fdw_get_connections to return remote backend pid |
Date: | 2025-02-18 14:44:51 |
Message-ID: | CAPhYifF25q5xUQWXETfKwhc0YVa_6+tfG9Kw4bCvCjpCWxYs2A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
Attachment | Content-Type | Size |
---|---|---|
v01_add_checking_infrastracture.patch | application/octet-stream | 11.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Sami Imseih | 2025-02-18 14:48:43 | Re: pg_stat_statements and "IN" conditions |
Previous Message | Melanie Plageman | 2025-02-18 14:31:03 | Re: Confine vacuum skip logic to lazy_scan_skip |