I have a process that appears to hang every night. I ran the following
query and results, and it looks like an autoanalyze and query are
waiting on a lock that's being exclusively held by a transaction that is
IDLE. Any ideas? Any additional queries I should run to shed light on
the issue?
SELECT pg_database.datname AS database, pg_class.relname AS table,
transaction, pid, mode, granted, usename, current_query, backend_start
FROM pg_locks, pg_class, pg_database, pg_stat_activity
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid
and pg_locks.pid = pg_stat_activity.procpid
order by pg_database.datname, pg_class.relname, granted
"dashboard";"activity_log_transition";18291984;1588;"ShareUpdateExclusiveLock";f;"postgres";"ANALYZE
public.activity_log_transition";"2007-01-17 00:06:03.281-05"
"dashboard";"activity_log_transition";18291982;2872;"AccessShareLock";f;"dashboard";"select
distinct strDescription from activity_log_transition";"2007-01-17
00:05:03.281-05"
"dashboard";"activity_log_transition";18291979;1472;"ShareLock";t;"dashboard";"<IDLE>
in transaction";"2007-01-17 00:05:00.968-05"
"dashboard";"activity_log_transition";18291979;1472;"AccessExclusiveLock";t;"dashboard";"<IDLE>
in transaction";"2007-01-17 00:05:00.968-05"
"dashboard";"activity_log_transition_pkey";18291979;1472;"AccessExclusiveLock";t;"dashboard";"<IDLE>
in transaction";"2007-01-17 00:05:00.968-05"