PostgreSQL PARSE waiting

digoal

2010-08-28日
某系统发送异常，经查有大量waiting.
skyurs=# select procpid,query_start,waiting,current_query from pg_stat_activity;
procpid |          query_start          | waiting |                                                                      current_qu
ery                                                                       
---------+-------------------------------+---------+--------------------------------------------------------------------------------
--------------------------------------------------------------------------
   22048 | 2010-08-27 14:30:24.673425+08 | f       | create table urs_test_user_list as select user_id,count(user_id) from urs_user_
relation a where relation=1 group by user_id having(count(user_id)>50) \r+
         |                               |         | and (select count(*) from urs_user_relation where relation=1 and buddy_id=a.use
r_id limit 50)=50\r                                                      +
         |                               |         | limit 400000\r                                                                 
                                                                         +
         |                               |         |
    2634 | 2010-08-27 19:27:06.622968+08 | t       | select buddy_id, group_id, relation, appinfo,  extinfo, create_time from skyurs
.urs_user_relation where user_id=$1 and relation=1
   12932 | 2010-08-27 20:05:50.278318+08 | t       | select buddy_id, group_id, relation, appinfo,  extinfo, create_time from skyurs
.urs_user_relation where user_id=$1 and relation=1

建表语句:create table urs_test_user_list as select user_id,count(user_id) from urs_user_
relation a where relation=1 group by user_id having(count(user_id)>50) \r+
         |                               |         | and (select count(*) from urs_user_relation where relation=1 and buddy_id=a.use
r_id limit 50)=50\r                                                      +
         |                               |         | limit 400000\
把表 urs_user_relation给锁了，导致相关的SELECT都处于等待锁状态.
skyurs=# select pg_terminate_backend(2204;
pg_terminate_backend
----------------------
t
(1 row)
干掉之后，锁消失。
结束后查看数据库日志：
出现大量:
2010-08-28 10:56:53.735 CST,"skyurs","skyurs",17107,"192.168.171.39:40721",4c77adcc.42d3,3,"ARSE",2010-08-27 20:21:32 CST,74/157,0,
LOG,00000,"duration: 52520677.542 ms  parse <unnamed>: select buddy_id, group_id, relation, appinfo,  extinfo, create_time from skyu
rs.urs_user_relation where user_id=$1 and relation=1",,,,,,,,,""

总结：
1. 生产环境跑长SQL必须引起注意.这种SQL必须人在现场看牢。以及时响应。
2. 这个建表语句可以改一下，先把表建好，再用insert into select语法写入，这样的话锁没这么大。
当然，PostgreSQL应该降低这类情形下的锁级别才对。
3. 可以考虑配置超时机制，超时的SQL返回错误，以免锁过长时间才发现
4. 监控超时SQL，及时反馈给DBA处理。