[转]Postgresql数据库层限流脚本

-- 下面这个例子, 匹配的是： 
（查询条件 %select now% ）  AND  （活跃连接数 超过400 ）AND  （ 数据库是 mydb ）AND  （账号是 app01 ）


with tmp3 as
(select count(*) as cnt from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='mydb' and usename='app01' and state='idle' and query like '%select now%')
select 
case
 when cnt <= 400 then 
 (with tmp1 as ( select pg_terminate_backend(pid) from  (select pid from pg_stat_activity WHERE 1=2 ) as foo1)
 select count(*) from tmp1 )
 when cnt > 400 then 
 (with tmp2 as ( select pg_terminate_backend(pid) from  (select pid from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='mydb' and usename='app01' 
                and state='active' and query like '%select now%' order by backend_start 
                limit 10) as foo2)
 select count(*) from tmp2 )
end as kill_if_too_many_process
from tmp3 \watch 1