Is there a way to figure out which statement from the block is currently running in Postgres? (Even extra extensions or tracing might be an option)
Below is the quick way to reproduce but in real scenario the block is way more complicated.
do
$$
declare
x int;
c char;
d int := 3;
begin
select pg_sleep(d), 11112 into c, x;
select pg_sleep(d), 11113 into c, x;
select pg_sleep(d), 11114 into c, x;
end
$$;
pg_stat_statements / pg_stat_activity show only the main block, not specific queries.
In fact, pg_stat_statements does have info about statements inside the block (when pg_stat_statements.track = all). However, they are normalized so we cannot see actual values. Anyway, we can monitor what is going on by checking calls.
postgres=# select queryid, query, calls from pg_stat_statements where query ~ 'pg_sleep' and not query ~ 'do';
queryid | query | calls
----------------------+------------------------+-------
-2604025637555979379 | select pg_sleep(d), $5 | 43
(1 row)
The ideal solution though is to use pgsentinel.
It shows what is running at the moment as well associated top level block.
postgres=# select ash_time, query, queryid --, top_level_query
postgres-# from pg_active_session_history
postgres-# where ash_time > timestamp '2024-10-26 13:40:00';
ash_time | query | queryid
-------------------------------+---------------------------+----------------------
2024-10-26 13:41:00.726212+01 | select pg_sleep(d), 11112 | -2604025637555979379
2024-10-26 13:41:01.730933+01 | select pg_sleep(d), 11112 | -2604025637555979379
2024-10-26 13:41:02.733903+01 | select pg_sleep(d), 11112 | -2604025637555979379
2024-10-26 13:41:03.737171+01 | select pg_sleep(d), 11113 | -2604025637555979379
2024-10-26 13:41:04.74009+01 | select pg_sleep(d), 11113 | -2604025637555979379
2024-10-26 13:41:05.742954+01 | select pg_sleep(d), 11113 | -2604025637555979379
2024-10-26 13:41:06.748392+01 | select pg_sleep(d), 11114 | -2604025637555979379
2024-10-26 13:41:07.75082+01 | select pg_sleep(d), 11114 | -2604025637555979379
2024-10-26 13:41:08.754098+01 | select pg_sleep(d), 11114 | -2604025637555979379
(9 rows)