Astute readers may have noticed that the pg_locks and pg_stat_activity views both share the pid column. Since the pg_locks view only details information about the locks themselves, we can't tell when the lock might have been granted, or any other pertinent troubleshooting details. There is a very handy query that uses both of these views.
Users of PostgreSQL 9.5 and older can use this query:
SELECT l.pid, l.mode, l.granted, a.waiting, l.relation::REGCLASS::TEXT AS locked_object, a.datname, a.client_addr, a.usename, a.query_start, now() - a.query_start AS duration, substring(a.query, 1, 20) AS query_part FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE l.relation IS NOT NULL AND now() - a.query_start > INTERVAL '10 ...
