locks.sql

SELECT nodename,
rarray[1] blocked_statement,
rarray[2] cur_stmt_blocking_proc,
rarray[3] count,
rarray[4] age
FROM (
SELECT nodename,
regexp_split_to_array(RESULT, '\$') rarray
FROM (
SELECT nodename,unnest(RESULT) RESULT
FROM (
SELECT nodename,
CASE
WHEN RESULT='' THEN ARRAY[]::text[]
ELSE RESULT::text[]
END RESULT
FROM run_command_on_workers($cmd$SELECT array_agg(blocked_statement || ' $ ' || cur_stmt_blocking_proc || ' $ ' || cnt::text || ' $ ' || age) FROM (
SELECT blocked_activity.query AS blocked_statement,
blocking_activity.query AS cur_stmt_blocking_proc,
count(*) cnt,
--now() - min(current_timestamp) AS how_long,
age(now(), min(blocked_activity.query_start)) AS "age"
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED
AND blocking_locks.GRANTED
GROUP BY blocked_activity.query,
blocking_activity.query
ORDER BY 4
)a$cmd$
)
) a
) b
WHERE RESULT <> ''
) a;