Find active long running queries and tables they lock

locks_held.sql

SELECT
  psa.datname as database,
  psa.query as current_query,
  clock_timestamp() - psa.xact_start AS transaction_age,
  array_agg(distinct c.relname) AS tables_with_locks
FROM pg_catalog.pg_stat_activity psa
JOIN pg_catalog.pg_locks l ON (psa.pid = l.pid)
JOIN pg_catalog.pg_class c ON (l.relation = c.oid)
JOIN pg_catalog.pg_namespace ns ON (c.relnamespace = ns.oid)
WHERE psa.pid != pg_backend_pid()
  AND ns.nspname != 'pg_catalog'
  AND c.relkind = 'r'
  AND psa.xact_start < clock_timestamp() - '5 seconds'::interval
GROUP BY psa.datname, psa.query, psa.xact_start;