PostgreSQL捕获死锁异常时如何获取完整PG_EXCEPTION_DETAIL？

问题描述 #

发生死锁时，希望将包含死锁双方查询语句的错误详情记录到自定义表中，且不抛出异常。无异常处理时，服务器日志能显示包含双方查询的完整死锁详情；但通过GET STACKED DIAGNOSTICS获取PG_EXCEPTION_DETAIL时，仅能拿到锁等待相关信息，无法获取对应的查询语句。需要找到捕获异常时获取完整死锁详情的方法。

死锁复现代码 #

CREATE TABLE tab_deleteme (
    id INT PRIMARY KEY,
    value TEXT
);

INSERT INTO tab_deleteme (id, value)
VALUES (1, 'A'), (2, 'B') ;
COMMIT;

-- 事务一，可尝试添加/移除异常块
DO $$
DECLARE
   v_error_detail TEXT;
BEGIN
    -- 锁定id=1的行
    UPDATE tab_deleteme SET value = 'Lock1' WHERE id = 1;
    PERFORM pg_sleep(2);  -- 等待另一个事务启动

    -- 尝试锁定id=2的行
    UPDATE tab_deleteme SET value = 'Lock1-2' WHERE id = 2;
EXCEPTION
    WHEN others THEN
        GET STACKED DIAGNOSTICS
           v_error_detail = PG_EXCEPTION_DETAIL;

 RAISE NOTICE '%',v_error_detail;

END;
$$;

-- 事务二，需在事务一启动后立即执行，可尝试添加/移除异常块
DO $$
DECLARE
   v_error_detail TEXT;
BEGIN
    -- 锁定id=2的行
    UPDATE tab_deleteme SET value = 'Lock2' WHERE id = 2;
    PERFORM pg_sleep(2);  -- 等待另一个事务启动

    -- 尝试锁定id=1的行
    UPDATE tab_deleteme SET value = 'Lock2-1' WHERE id = 1;
EXCEPTION
    WHEN others THEN
        GET STACKED DIAGNOSTICS
           v_error_detail = PG_EXCEPTION_DETAIL;

 RAISE NOTICE '%',v_error_detail;

END;
$$;

解决方案 #

PostgreSQL抛出死锁异常时，PG_EXCEPTION_DETAIL仅包含当前进程的锁等待信息，完整死锁详情（含双方查询）默认仅写入服务器日志。可通过以下两种方式获取完整信息：

方案一：查询系统进程表获取对方查询语句 #

死锁发生瞬间，另一个参与死锁的进程通常仍处于活跃状态，可以通过pg_stat_activity查询其执行的语句，结合当前异常信息拼接完整详情。

步骤：

1. 创建死锁日志表：

CREATE TABLE deadlock_logs (
    log_id SERIAL PRIMARY KEY,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    full_error_detail TEXT
);

2. 修改事务代码，在异常块中收集双方信息：

-- 事务一修改后代码
DO $$
DECLARE
    v_error_detail TEXT;
    v_other_queries TEXT;
BEGIN
    UPDATE tab_deleteme SET value = 'Lock1' WHERE id = 1;
    PERFORM pg_sleep(2);
    UPDATE tab_deleteme SET value = 'Lock1-2' WHERE id = 2;
EXCEPTION
    WHEN deadlock_detected THEN
        -- 获取当前进程的死锁详情
        GET STACKED DIAGNOSTICS v_error_detail = PG_EXCEPTION_DETAIL;
        
        -- 查询其他参与死锁的进程语句（需根据业务调整过滤条件）
        SELECT string_agg(query, E'\n----------------\n') INTO v_other_queries
        FROM pg_stat_activity
        WHERE pid != pg_backend_pid()
          AND query IS NOT NULL
          AND query NOT LIKE '%pg_sleep%'
          AND query LIKE '%tab_deleteme%';
        
        -- 拼接完整死锁信息
        v_error_detail := E'当前进程死锁详情:\n' || v_error_detail || 
                          E'\n\n其他参与死锁进程的查询:\n' || COALESCE(v_other_queries, '未捕获到');
        
        -- 写入日志表，不抛出异常
        INSERT INTO deadlock_logs (full_error_detail) VALUES (v_error_detail);
END;
$$;

-- 事务二修改后代码
DO $$
DECLARE
    v_error_detail TEXT;
    v_other_queries TEXT;
BEGIN
    UPDATE tab_deleteme SET value = 'Lock2' WHERE id = 2;
    PERFORM pg_sleep(2);
    UPDATE tab_deleteme SET value = 'Lock2-1' WHERE id = 1;
EXCEPTION
    WHEN deadlock_detected THEN
        GET STACKED DIAGNOSTICS v_error_detail = PG_EXCEPTION_DETAIL;
        
        SELECT string_agg(query, E'\n----------------\n') INTO v_other_queries
        FROM pg_stat_activity
        WHERE pid != pg_backend_pid()
          AND query IS NOT NULL
          AND query NOT LIKE '%pg_sleep%'
          AND query LIKE '%tab_deleteme%';
        
        v_error_detail := E'当前进程死锁详情:\n' || v_error_detail || 
                          E'\n\n其他参与死锁进程的查询:\n' || COALESCE(v_other_queries, '未捕获到');
        
        INSERT INTO deadlock_logs (full_error_detail) VALUES (v_error_detail);
END;
$$;

方案二：读取服务器日志文件获取完整详情 #

通过配置PostgreSQL日志参数，将完整日志写入文件，在异常块中读取最新日志条目提取死锁信息。

步骤：

1. 修改postgresql.conf配置日志参数：

log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_messages = warning
log_error_verbosity = verbose

修改后重启PostgreSQL服务。

2. 创建函数读取最新死锁日志：

CREATE OR REPLACE FUNCTION get_latest_deadlock_log() RETURNS TEXT AS $$
DECLARE
    latest_log_file TEXT;
    log_content TEXT;
    deadlock_start INT;
    deadlock_end INT;
BEGIN
    -- 获取最新的日志文件名
    SELECT filename INTO latest_log_file
    FROM pg_ls_dir('pg_log')
    WHERE filename LIKE 'postgresql-%'
    ORDER BY filename DESC
    LIMIT 1;
    
    IF latest_log_file IS NULL THEN
        RETURN '未找到日志文件';
    END IF;
    
    -- 读取日志文件内容
    log_content := pg_read_file('pg_log/' || latest_log_file);
    
    -- 提取死锁相关内容（死锁日志以"ERROR:  deadlock detected"开头，下一个ERROR或日志结尾结束）
    deadlock_start := strpos(log_content, 'ERROR:  deadlock detected');
    IF deadlock_start = 0 THEN
        RETURN '未找到死锁日志';
    END IF;
    
    deadlock_end := strpos(substr(log_content, deadlock_start), 'ERROR: ');
    IF deadlock_end > 0 THEN
        deadlock_end := deadlock_start + deadlock_end - 1;
    ELSE
        deadlock_end := length(log_content) + 1;
    END IF;
    
    RETURN substr(log_content, deadlock_start, deadlock_end - deadlock_start);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

3. 在异常块中调用函数并写入日志表：

DO $$
DECLARE
    v_full_deadlock_detail TEXT;
BEGIN
    -- 业务逻辑同前
EXCEPTION
    WHEN deadlock_detected THEN
        v_full_deadlock_detail := get_latest_deadlock_log();
        INSERT INTO deadlock_logs (full_error_detail) VALUES (v_full_deadlock_detail);
END;
$$;

注意事项 #

• 方案一的进程查询过滤条件需根据实际业务调整，避免匹配无关进程的查询语句；若死锁后对方进程已结束，可能无法捕获到查询。
• 方案二需要确保执行函数的用户拥有读取日志目录的权限，且日志文件的命名规则需与配置一致。
• 两种方案均需确保事务异常处理仅捕获deadlock_detected异常，避免误处理其他错误。

内容的提问来源于stack exchange，提问作者Peter