查找和殺掉postgresql堵塞的會話

查找和殺掉postgresql堵塞的會話
1.創(chuàng)建視圖，可以隨時使用：
CREATE VIEW pg_blocking AS SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity. QUERY AS blocked_statement,
blocking_activity. QUERY AS current_statement_in_blocking_process
FROM
pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks. DATABASE IS NOT DISTINCT
FROM
blocked_locks. DATABASE
AND blocking_locks.relation IS NOT DISTINCT
FROM
blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT
FROM
blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT
FROM
blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT
FROM
blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT
FROM
blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT
FROM
blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT
FROM
blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT
FROM
blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
NOT blocked_locks.GRANTED;
查詢創(chuàng)建的視圖查詢堵塞：
select  * from  pg_blocking;
2.殺掉查出的阻塞會話：
kill有兩種方式，第一種是：
SELECT pg_cancel_backend(PID);  ###取消一個正在執(zhí)行的SQL；
第二種是：
SELECT pg_terminate_backend(PID);  ###終止一個后臺服務(wù)進(jìn)程，同時釋放此后臺服務(wù)進(jìn)程的資源。

這兩個函數(shù)的區(qū)別是：pg_cancel_backend()函數(shù)實際上是給正在執(zhí)行的SQL任務(wù)配置一個取消標(biāo)志，正在執(zhí)行的任務(wù)是在合適的時候檢測到此標(biāo)志后主動退出；但如果這個任務(wù)沒有主動檢測到這個標(biāo)志，則該任務(wù)就無法正常退出，這時需要使用pg_terminate_backend()命令在終止SQL的執(zhí)行。
通常是先查詢pg_stat_activity,試圖找出長時間運(yùn)行的SQL,然后進(jìn)行終止,也可以批量終止回話：
SELECT  'SELECT pg_terminate_backend('||blocking_pid||');' from pg_blocking;