PostgreSQL多表DML操作历史记录表异常排查求助

核心问题分析 #

你的多表DML日志函数log_dml_changes()存在多处逻辑错误，导致跨表记录失效，具体问题如下：

1. DELETE操作时NEW变量为空 #

触发器包含DELETE操作，但DELETE事件中NEW不存在（只有OLD有效），此时执行依赖NEW的主键查询会直接报错，无法写入记录。同时函数末尾固定返回NEW，DELETE时返回空值不符合触发器规范。

2. 主键字段获取逻辑缺失 #

代码中尝试获取record_id_value时，使用了未定义的column_name变量，这是致命语法错误——函数根本不知道要取哪个字段作为记录ID，单表测试时可能碰巧没触发报错，但跨表场景必然失效。

3. 字段变更判断逻辑漏洞 #

• INSERT操作时OLD为空，IS DISTINCT FROM判断会异常；DELETE操作时NEW为空，同样会导致判断逻辑失效。
• 动态SQL中字符串拼接的语法错误，quote_literal的使用方式错误，导致字段名拼接失败。
• 变更字段文本拼接逻辑错误，每次循环会覆盖之前的结果，而非累加。

4. 空变更判断逻辑缺陷 #

当没有字段变更时（比如无意义的UPDATE），直接返回NEW会跳过日志写入，但DELETE操作无论如何都需要记录，现有逻辑未区分操作类型。

修正后的完整代码 #

-- 重建基础表（如果已存在可跳过）
CREATE TABLE IF NOT EXISTS employee (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TABLE IF NOT EXISTS department (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS dml_history (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(255) NOT NULL,
    record_id VARCHAR(255) NOT NULL,
    action_type VARCHAR(10) NOT NULL,
    changed_columns TEXT,
    action_timestamp TIMESTAMP NOT NULL
);

CREATE OR REPLACE FUNCTION log_dml_changes()
RETURNS TRIGGER AS $$
DECLARE
    record_id_value TEXT;
    changed_columns_text TEXT := '';
    pk_column TEXT;
    col_name TEXT;
BEGIN
    -- 获取当前表的主键字段
    SELECT kcu.column_name
    INTO pk_column
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
    WHERE tc.table_name = TG_TABLE_NAME
      AND tc.constraint_type = 'PRIMARY KEY';

    -- 根据操作类型获取记录ID
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        EXECUTE 'SELECT $1.' || quote_ident(pk_column)
        INTO record_id_value
        USING NEW;
    ELSIF TG_OP = 'DELETE' THEN
        EXECUTE 'SELECT $1.' || quote_ident(pk_column)
        INTO record_id_value
        USING OLD;
    END IF;

    -- 处理字段变更记录
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        FOR col_name IN (SELECT column_name FROM information_schema.columns WHERE table_name = TG_TABLE_NAME) LOOP
            IF TG_OP = 'INSERT' THEN
                -- INSERT时所有字段都是新增值
                changed_columns_text := changed_columns_text || '"' || col_name || '": ' || COALESCE(quote_literal(NEW.*[col_name]), 'NULL') || ', ';
            ELSIF TG_OP = 'UPDATE' THEN
                -- UPDATE时只记录实际变更的字段
                IF NEW.*[col_name] IS DISTINCT FROM OLD.*[col_name] THEN
                    changed_columns_text := changed_columns_text || '"' || col_name || '": ' || COALESCE(quote_literal(NEW.*[col_name]), 'NULL') || ' (原: ' || COALESCE(quote_literal(OLD.*[col_name]), 'NULL') || '), ';
                END IF;
            END IF;
        END LOOP;
    ELSIF TG_OP = 'DELETE' THEN
        -- DELETE时记录所有原字段值
        FOR col_name IN (SELECT column_name FROM information_schema.columns WHERE table_name = TG_TABLE_NAME) LOOP
            changed_columns_text := changed_columns_text || '"' || col_name || '": ' || COALESCE(quote_literal(OLD.*[col_name]), 'NULL') || ', ';
        END LOOP;
    END IF;

    -- 移除末尾多余的逗号和空格
    IF changed_columns_text <> '' THEN
        changed_columns_text := LEFT(changed_columns_text, LENGTH(changed_columns_text) - 2);
    END IF;

    -- 写入历史记录（DELETE操作强制写入）
    IF TG_OP = 'DELETE' OR changed_columns_text <> '' THEN
        INSERT INTO dml_history (table_name, record_id, action_type, changed_columns, action_timestamp)
        VALUES (TG_TABLE_NAME, record_id_value, TG_OP, changed_columns_text, NOW());
    END IF;

    -- 根据操作类型返回正确的变量
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        RETURN NEW;
    ELSE
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 重建触发器
DROP TRIGGER IF EXISTS employee_trigger ON employee;
CREATE TRIGGER employee_trigger
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW EXECUTE FUNCTION log_dml_changes();

DROP TRIGGER IF EXISTS department_trigger ON department;
CREATE TRIGGER department_trigger
AFTER INSERT OR UPDATE OR DELETE ON department
FOR EACH ROW EXECUTE FUNCTION log_dml_changes();

-- 测试语句
INSERT INTO employee (employee_name, salary) VALUES ('John Doe', 50000.00);
INSERT INTO department (department_name) VALUES ('IT');

UPDATE employee SET salary = 55000.00 WHERE employee_name = 'John Doe';

DELETE FROM employee WHERE employee_name = 'John Doe';

SELECT * FROM dml_history;

修正说明 #

• 自动获取每张表的主键字段作为record_id，无需硬编码
• 区分INSERT/UPDATE/DELETE操作，分别使用NEW/OLD变量，避免空值错误
• 优化字段变更记录逻辑：INSERT记录所有字段，UPDATE只记录变更字段（含原值），DELETE记录所有原字段
• 修复返回值逻辑，DELETE操作返回OLD符合触发器要求
• 确保DELETE操作无论如何都会写入日志记录

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