postgresql alter table/truncate table hang并阻塞其他事务执行的处理办法

用户抱怨当使用alter table add column…或者truncate table …时，异常缓慢，甚至直接hang在那里，session居高不下，用户便也无法完成一些操作， 猜测可能的远因是因为在高并发的环境下，被异动的表上，之前已经发生了一些其他查询或操作，然后 alter table 或者truncate table 必须等待获得lock，才能继续

我们模拟alter table 锁的情况

=# begin;
BEGIN
 
=# alter table test add column whatever2 int4;
ALTER TABLE
 
=# select * from pg_locks where pid = pg_backend_pid();
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath 
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 relation      |   165725 |    12143 |      |       |            |               |         |       |          | 3/2594             | 32470 | AccessShareLock     | t       | t
 virtualxid    |          |          |      |       | 3/2594     |               |         |       |          | 3/2594             | 32470 | ExclusiveLock       | t       | t
 transactionid |          |          |      |       |            |          1422 |         |       |          | 3/2594             | 32470 | ExclusiveLock       | t       | f
 relation      |   165725 |   166142 |      |       |            |               |         |       |          | 3/2594             | 32470 | AccessExclusiveLock | t       | f
(4 rows)
 
=# rollback;
ROLLBACK

注意，这里的 AccessExclusiveLock——这是独占锁，直到锁消失前，其他session将不能在这个table上执行任何操作，在这个例子中是——直到与 alter table 的事务完成

接下来，我们开启一个事务(session 1)，并执行test表上的select 操作，不关闭事务

(session 1) =# begin;
BEGIN 
(session 1) =# select count(*) from test;
 count 
-------
     0
(1 row)

开启另一个事务(session 2),然后执行alter table 操作

(session 2) =# alter table test add column whatever2 int4;

session 2卡住，因为他等候从session1获得lock,但是它已经获得test table的AccessExclusiveLock,因此它也阻塞随后其他的针对test table的操作，包含select操作

(session 3) =# depesz=# select * from test limit 1;

session 3也卡住了

怎么办？ 解套的方法将是设计一个loop
1.建立一个sql脚本文件，供后续loop调用

=$ printf "SET statement_timeout = 50;\nALTER TABLE test add column whatever2 INT4;\n" > alter.sql

set statement_timeout =50 将确保sql语句在50 millisecond内完成alter table操作,否怎中止执行

接下来，设计一个loop,调用alter.sql文件，

while true
do
    date
    psql -qX -v ON_ERROR_STOP=1 -f alter.sql && break
    sleep 1
done

loop开始后即开始执行alter.sql文件，首先set statement_timeout=50,随后执行alter table指令，如果50毫秒内没有成功获得lock去执行alter table,即停止执行，然后sleep 1秒后再次loop,知道成功alter table
显然，添加该列现在可能需要很长时间——但在任何时间点，其他查询都不会被锁定超过 50 毫秒