Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment