PostgreSQL在事务诊断和阅读中闲置pg_locks

我们看到：数据库中的“闲置事务”查询长时间出现.为了看到它们,我会运行这样的东西：

SELECT query_start,procpid,client_addr,current_query FROM pg_stat_activity
WHERE query_start < NOW() - interval '5 minutes';

大多数结果当然只是使用pgbouncer保持开放使用的空闲连接,但有时候会有这些旧的“事务中的IDLE”查询.我明白这意味着有一个查询事务正在等待某事,或者有一个BEGIN但尚未达到COMMIT或ROLLBACK的事情.

我的下一步是尝试使用pg_locks来确定进程正在等待什么：

select pg_class.relname,pg_locks.transactionid,pg_locks.mode,pg_locks.granted as "g",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
and pg_stat_activity.procpid = <AN IDLE TRANSACTION PROCESS>
order by query_start;

很多次,结果我看起来像这样：

relname | transactionid |      mode       | g |     current_query     |         query_start          |       age       |  client_addr   | procpid 
---------+---------------+-----------------+---+-----------------------+------------------------------+-----------------+----------------+---------
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | ExclusiveLock   | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
         |               | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 |    1991
(10 rows)

我不知道怎么读这个(我猜这是源于不是真正理解pg_locks).没有姓名,所以呢说是等待什么呢？我认为,如果被授予“真实”,它就有锁.由于所有这些结果都被授予,因为pg_locks显示了我所拥有的锁,而不是它正在等待什么？

现在我通过重新启动Apache来修复这个问题,这似乎阻碍了事务的松动,但显然这不是一个真正的解决方案.我正在寻找Postgres给我一个地方去看看,特别是因为Django应该自动管理它的连接和交易.