在执行SQL的时候可能会碰到session hang的情况，这时候我们其实不知道是因为SQL本身执行很慢还是因为lock导致hang，因此一般情况下需要通过查询pg_stat_activity、pg_locks等系统表来确认。除之之外，PG还提供了通过statement timeout的超时机制来处理这种情况。

**session 1**
创建数据表，启动事务，执行查询
```
testdb=# create table t_timeout(id int);
CREATE TABLE
testdb=# 
testdb=# begin;
BEGIN
testdb=# 
testdb=# select count(*) from t_timeout;
 count 
-------
     0
(1 row)

testdb=# select * from pg_locks where pid = pg_backend_pid();
  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | ob
jid | objsubid | virtualtransaction | pid  |      mode       | granted | fastpath 
------------+----------+----------+------+-------+------------+---------------+---------+---
----+----------+--------------------+------+-----------------+---------+----------
 relation   |    16384 |    11645 |      |       |            |               |         |   
    |          | 3/94               | 1719 | AccessShareLock | t       | t
 virtualxid |          |          |      |       | 3/94       |               |         |   
    |          | 3/94               | 1719 | ExclusiveLock   | t       | t
 relation   |    16384 |   286770 |      |       |            |               |         |   
    |          | 3/94               | 1719 | AccessShareLock | t       | f
(3 rows)

testdb=# 
```

**session 2**
执行alter table命令，hang住
```
testdb=# -- session 2
testdb=# alter table t_timeout add column c1 int;
-- 挂起
```
设置50ms超时，SQL返回超时错误
```
testdb=# begin;
BEGIN
testdb=# SET statement_timeout = 50;
SET
testdb=# alter table t_timeout add column c1 int;
ERROR:  canceling statement due to statement timeout
testdb=# 
```
不过这样的设置，需要DBA对SQL的执行时长有初步的估算，比如增加列操作，正常应在10ms内返回，那设置超时50ms是没有问题，但对于vacuum full这样的操作来说，设置为50ms就很不合适了。
```
testdb=# SET statement_timeout = 50;
SET
testdb=# vacuum full;
ERROR:  canceling statement due to statement timeout
testdb=# 
```
也就是说，设置超时会存在误伤，需谨慎使用。

**参考资料**
[How to run short ALTER TABLE without long locking concurrent queries](https://www.depesz.com/2019/09/26/how-to-run-short-alter-table-without-long-locking-concurrent-queries/)

ITPub博客

PostgreSQL DBA(101) - pgAdmin(statement timeout)

最新文章