postgres sql查询语句堵塞排查

前言：工作期间遇到查询表的时候sql语句堵塞的情况，这边记录下如何进行排查和处理

查询sub_tasks表的时候，发现存在堵塞情况，该表sub_tasks查询的数据一直不会响应，如下图所示

通过相关语句排查堵塞情况，如下图所示，可以看到相关的堵塞卡在了delete相关的语句操作上面

SELECT 
    a.pid AS waiting_pid, 
    a.query AS waiting_query, 
    a.state AS waiting_state,
    b.pid AS blocking_pid, 
    b.query AS blocking_query,
    b.state AS blocking_state
FROM pg_stat_activity a
JOIN pg_locks l1 ON a.pid = l1.pid
JOIN pg_locks l2 ON l1.locktype = l2.locktype AND l1.database = l2.database AND l1.relation = l2.relation
JOIN pg_stat_activity b ON l2.pid = b.pid
WHERE l1.granted = false AND l2.granted = true AND a.pid <> b.pid;

通过排查可以知道是该工程中定时清理已完成任务的时候出现的问题，具体问题还未排查

确定了跟当前业务无相关影响，通过相关的sql语句将相关的堵塞任务进行关闭，如下所示

DO $$
DECLARE
    r record;
BEGIN
    FOR r IN
        SELECT b.pid AS blocking_pid
        FROM pg_stat_activity a
        JOIN pg_locks l1 ON a.pid = l1.pid
        JOIN pg_locks l2 ON l1.locktype = l2.locktype
                            AND l1.database = l2.database
                            AND l1.relation = l2.relation
        JOIN pg_stat_activity b ON l2.pid = b.pid
        WHERE l1.granted = false
          AND l2.granted = true
          AND a.pid <> b.pid
    LOOP
        -- Terminate the backend process
        PERFORM pg_terminate_backend(r.blocking_pid);
        RAISE NOTICE 'Terminated blocking process with PID: %', r.blocking_pid;
    END LOOP;
END $$;

然后重新进行查询的时候已经可以正常查询了，如下图所示