今天在海外 PostgreSQL 库上杀进程, 杀了很久都没成功；数据库为8.3版本, 只能使用 pg_cancel_backend 来杀, 以下为详细过程：

查看进程

1
2
3
4
5
6
7
8

postgres=# select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>';  
procpid | datname | current_query | waiting  
---------+----------+------------------------------------------------------------------------------------------------------+---------  
 24110 | postgres | select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>'; | f  
 8048 | skytf | select * From tbl_conn_rec limit 1; | f  
 10787 | skytf | select ip from tbl_conn_rec limit 1; | f  
 1131 | skytf | select max(createtime) from tbl_conn_rec; | f  
(4 rows)

备注： 表 tbl_conn_rec 比较大，有20G左右，现在需要将进程，1131, 8048,10787, 都杀掉。

尝试杀会话

1
2
3
4
5
6
7
8
9
10

postgres=# select pg_cancel_backend(1131);  
pg_cancel_backend  
-------------------  
t  
(1 row)
postgres=# select pg_cancel_backend(1131);  
pg_cancel_backend  
-------------------  
t  
(1 row)

备注：由于8.3 版本没有 pg_terminate_backend, 所以只能使用 pg_cancel_backend 杀查询会话。

再次查看进程，进程未消失

postgres=# select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>';  
procpid | datname | current_query | waiting  
---------+----------+------------------------------------------------------------------------------------------------------+---------  
 24110 | postgres | select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>'; | f  
 8048 | skytf | select * From tbl_conn_rec limit 1; | f  
 10787 | skytf | select ip from tbl_conn_rec limit 1; | f  
 1131 | skytf | select max(createtime) from tbl_conn_rec; | f  
(4 rows)  

备注：进程还在，说明杀进程失败，虽然 pg_cancel_backend 返回为真，但实际上进程没有杀成功。

疑似 BUG

网上查了资料，说是BUG ，如下：

The following bug has been logged online:
Bug reference: 5459
Logged by: Mason Hale
Email address: [hidden email]
PostgreSQL version: 8.3.8
Operating system: Redhat EL 5.1-64 bit
Description: Unable to cancel query while in send()
Details:
ISSUE: unable to cancel queries using pg_cancel_backend(), that are in
send() function call, waiting on client receipt of data.
EXPECTED RESULT: expect to be able to cancel most/all queries using
pg_cancel_backend() as superuser, perhaps with some wait time, but not an
hour or more.
= SYMPTOM =
A SELECT query was running over 18 hours on our PostgreSQL 8.3.8 server.
Verified that it was not waiting on any locks via pg_stat_activity.
Attempted to cancel the query using pg_cancel_backend(), which returned ‘t’.
However more than an hour later the process was still active, using about 6%
of CPU and 5% of RAM.
Terminated the client process that was running the query (from another
server) did not cause the query process on the pgsql server to stop. In this
case the client was connecting via a ssh tunnel through an intermediate
‘gateway’ server.
Connection path was:
CLIENT –> SSH GATEWAY –> DB SERVER

备注：上面描述的和今天的问题一样，只是操作系统版本为 “ Red Hat Enterprise Linux Server release 4.2”, 今天运气不错，第一次遇到了 PostgreSQL 的 bug。大概过了几小时后，前面那三个查询会话自己运行完了。

后期计划

后期准备将这个库升为9.0。