PostgreSQL的事务超时解决方法

Question

据我所知，PostgreSQL 8.3不支持事务超时。我已经阅读了关于未来支持此功能的文章，并且有一些讨论。但是，由于特定原因，我需要解决此问题的解决方案。因此，我编写了一个定期运行的脚本：

1）基于锁和活动，查询以检索正在进行太长时间的事务的进程ID，并保留最旧的（trxTimeOut.sql）：

SELECT procpid
FROM
(
    SELECT DISTINCT age(now(), query_start) AS age, procpid
    FROM pg_stat_activity, pg_locks
    WHERE pg_locks.pid = pg_stat_activity.procpid
) AS foo
WHERE age > '30 seconds'
ORDER BY age DESC
LIMIT 1

2) 根据此查询，终止相应的进程 (trxTimeOut.sh):

psql -h localhost -U postgres -t -d test_database -f trxTimeOut.sql | xargs kill

尽管我已经测试过它并且似乎有效，但我想知道它是否是可接受的方法，或者我应该考虑其他方法？

- Federico Cristina

---

升级至至少8.4版本是否可行？如果是，您可以使用pg_terminate_backend()来终止打开的连接。 - Frank Heikens

谢谢你的回答。不幸的是，我不能把升级视为理所当然（这不取决于我）。无论如何，pg_terminate_backend()只是比kill命令更好地结束连接的一种方式，对吧？我的意思是，查询+kill（或终止）的解决方案相对可接受？ - Federico Cristina

Answer 1

PostgreSQL自9.6版本起提供了idle_in_transaction_session_timeout，可自动终止太长时间处于空闲状态的事务。

同时，也可以通过statement_timeout设置命令的最长执行时间，独立于事务持续时间以及其所处的状态（例如繁忙查询或等待锁）。

如果需要自动中止因等待锁而卡住的事务，请查看lock_timeout。

这些设置可以使用类似下面的SET命令在SQL级别进行设置，也可以通过ALTER DATABASE或ALTER USER为数据库或用户设置默认值，或通过postgresql.conf设置整个实例的默认值。

SET statement_timeout=10000;   -- time out after 10 seconds

- Daniel Vérité

---

谢谢，但问题有些复杂。如果一个客户端应用程序由于某种原因而挂起（即与Java Swing事件相关，但与需要太长时间的语句无关），这可能最终会阻塞其他客户端，因为第一个客户端已经授予了一些锁定（在未提交/回滚的事务中），而第二个客户端正在等待访问这些锁定。该脚本会杀死第一个事务，因为它花费的时间太长，无论问题的来源如何（从而允许第二个客户端继续）。statement_timeout 仅会中止第二个客户端的事务（因为锁定）。 - Federico Cristina

“statement_timeout”不能解决“事务空闲”状态，该状态仍然可能阻止日志重放。在某些非常具体的情况下，这个答案是不够好的。 - Charles Duffy

版本9.6x现在除了statement_timeout之外，还有一个名为idle_in_transaction_session_timeout的属性。 - gregtzar

@gregtzar：我已经重写了那个答案，它确实和PG 8.3一样过时了。感谢提醒！ - Daniel Vérité