Saturday, January 28, 2012

Find Postgres tables which have been locked by over 3 minutes

SELECT
pd.datname AS DATABASE
,pc.relname AS relation
,pl.GRANTED
,pl.mode
,psa.current_query
,now() - psa.query_start AS execution_time
FROM
pg_locks pl
LEFT OUTER JOIN pg_class pc ON pc.oid = pl.relation
LEFT OUTER JOIN pg_database pd ON pd.oid = database
LEFT OUTER JOIN pg_stat_activity psa ON psa.procpid = pl.pid
INNER JOIN pg_tables pt ON pt.tablename = pc.relname
WHERE
1 = 1
AND pd.datname = 'cso'
AND now() - psa.query_start >= '00:03:00'
ORDER BY
execution_time DESC;

No comments: