1、查询锁表的线程

SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks l1 ON l1.pid = blocked.pid
JOIN pg_locks l2 ON l2.locktype = l1.locktype 
    AND l2.DATABASE IS NOT DISTINCT FROM l1.DATABASE 
    AND l2.relation IS NOT DISTINCT FROM l1.relation 
    AND NOT l1.granted 
    AND l2.granted
JOIN pg_stat_activity blocking ON blocking.pid = l2.pid;

2、关闭单个线程

SELECT pg_terminate_backend(线程id);

3、关闭所有锁表的线程

WITH deadlock_pids AS (
    SELECT pid FROM pg_stat_activity 
    WHERE wait_event_type = 'Lock' AND state = 'active'
)
SELECT pg_terminate_backend(pid) FROM deadlock_pids;