Thank you for the hints.
> Why only those modes? I'd search for locks with granted=false, then see
> all the other locks held by the process that's holding the conflicting
> lock with granted=true (i.e. the one you're waiting on).
Something like this?
SELECT granted, pid, virtualxid, transactionid, virtualtransaction, count(1) AS locks, current_query
FROM pg_locks AS l LEFT JOIN pg_stat_activity AS a ON pid = procpid
GROUP BY 1, 2, 3, 4, 5, 7
ORDER BY 1, 6 DESC;
And two more queries to do extended analysis of its results after restarting PG:
SELECT pg_stat_activity.datname, pg_class.relname, pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename, pg_stat_activity.current_query, pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start)AS "age", pg_stat_activity.procpid
FROM pg_stat_activity, pg_locks LEFT OUTER JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_locks.pid = pg_stat_activity.procpid
ORDER BY query_start;
SELECT * FROM pg_locks;
Are there another things I should do when the problem rise up again?
--
Regards,
Sergey Konoplev