pg_stat_activity_with_locks.sql

SELECT *,
       now() - xact_start AS duration
  FROM pg_stat_activity,
       LATERAL (SELECT array_agg(relation::regclass) AS locks
                  FROM pg_locks JOIN pg_class ON (pg_class.oid = relation)
                 WHERE relkind = 'r' AND pg_stat_activity.pid = pg_locks.pid)
            AS _
 WHERE pid != pg_backend_pid();