Extend postgres_fdw_get_connections to return remote backend pid

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

Responses

Browse pgsql-hackers by date

  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