Search code examples
postgresqlpiddblink

PostgreSQL: Get PID of a query via dblink before it finishes

I want to make monitoring for my queries which I am sending via dblink dblink_send_query, and thus I need it's PID so I can search for it in pg tables. But so far I was only able to get PID after dblink finishes via returning value pg_backend_pid() or doing it quite clumsy by getting it from pg_stat_activity by searching with query string, which doesn't account for multiple same queries or is really not ideal for queries that are text long.

So how can I get PID for this before it finishes?

select dblink_send_query('cn1', 'select pg_sleep(60)');
select dblink_send_query('cn2', 'select pg_sleep(60)');
select dblink_send_query('cn3', 'select pg_sleep(60)');

PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

Solution

  • The backend process ID will stay the same for the whole life of the dblink connection.

    So when you open the connection, run SELECT pg_backend_pid() first and save the result in a data structure where it is associated with the connection. Then you can just query that data structure to get the backend process ID for that connection.