查找和杀掉postgresql堵塞的会话

查找和杀掉postgresql堵塞的会话
1.创建视图，可以随时使用：
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;
查询创建的视图查询堵塞：
select * from pg_blocking;
2.杀掉查出的阻塞会话：
kill有两种方式，第一种是：
SELECT pg_cancel_backend(PID); ###取消一个正在执行的SQL；
第二种是：
SELECT pg_terminate_backend(PID); ###终止一个后台服务进程，同时释放此后台服务进程的资源。

这两个函数的区别是：pg_cancel_backend()函数实际上是给正在执行的SQL任务配置一个取消标志，正在执行的任务是在合适的时候检测到此标志后主动退出；但如果这个任务没有主动检测到这个标志，则该任务就无法正常退出，这时需要使用pg_terminate_backend()命令在终止SQL的执行。
通常是先查询pg_stat_activity,试图找出长时间运行的SQL,然后进行终止,也可以批量终止回话：
SELECT 'SELECT pg_terminate_backend('||blocking_pid||');' from pg_blocking;