BEFORE INSERT/UPDATE触发器失效排查：部分行未触发IP版本判断

数据老陈

2026-6-17

问题描述

我们公司数据库中有一张customerReview表，简化表结构如下：

CREATE TABLE customerReview(
    customerReviewId SERIAL PRIMARY KEY,
    dateTimeCreated  TIMESTAMP WITHOUT TIME ZONE,
    dateTimeUpdated  TIMESTAMP WITHOUT TIME ZONE,
    businessID       INTEGER,
    reviewText       TEXT,
    reviewerIP       TEXT,
    isIPv6           BOOLEAN
)

我未创建该表，但添加了isIPv6列及对应的触发器函数：

CREATE OR REPLACE FUNCTION review_ip_version()
    RETURNS TRIGGER AS
$BDY$
    IF NEW.reviewerip ~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN
        NEW.isIPv6 := FALSE;
    ELSIF NEW.reviewerip ~ '^[0-9a-f]{0,4}(:[0-9a-f]{0,4}){2,7}$' 
        AND NEW.reviewerip !~ '::.*::|:::|[0-9a-f]{5,}|(:[0-9a-f]*){7}:' THEN 
        NEW.isIPv6 := TRUE
    END IF;

    RETURN NEW;
END;
$BDY$
LANGUAGE 'plpgsql';

该函数逻辑为：reviewerIP匹配有效IPv4时，isIPv6设为FALSE；匹配有效IPv6时设为TRUE；其余情况（含reviewerIP为NULL）设为NULL，测试阶段所有场景均验证准确。

调用该函数的触发器为BEFORE INSERT OR UPDATE类型。添加触发器前，我在同一事务中用相同正则更新了所有现有行的isIPv6值，确保初始数据正确，次日测试也一切正常。

触发器正常运行约一个月后出现异常：7条reviewerIP为NULL的行中，6条isIPv6为FALSE，1条为TRUE（正确值应为NULL）。依赖isIPv6正确性的外部流程因此中断，且触发器仍对绝大多数行有效。

我推测存在操作绕过了触发器，但公司未保留日志表，dateTimeUpdated由Web流程维护不可靠，线索有限。想确认是否存在特殊场景会让UPDATE操作绕过该BEFORE INSERT OR UPDATE触发器？

可能的触发器绕过场景

超级权限直接修改数据：拥有超级用户权限（如postgres角色）的人员，可通过系统目录或底层函数直接修改行数据，跳过常规INSERT/UPDATE操作，这种情况下触发器不会触发。这类操作权限极高，一般只有运维人员能执行。
触发器被临时禁用：有人执行了ALTER TABLE customerReview DISABLE TRIGGER review_ip_version;（或禁用该表所有触发器），之后执行了错误的UPDATE/INSERT操作，随后又重新启用了触发器。这种操作需要表的ALTER权限，大概率是内部人员误操作。
使用COPY命令时跳过触发器：使用COPY导入数据时，若加上WITH TRIGGERS OFF选项，会直接绕过所有触发器写入数据。如果导入的数据集里reviewerIP为NULL但isIPv6被错误设置为FALSE/TRUE，就会出现此类异常。
触发器函数被临时修改：有人修改过review_ip_version函数的逻辑，比如临时将isIPv6默认设为FALSE或TRUE，之后又改回原逻辑。在函数被修改的时间段内，所有写入的行都会生成错误的isIPv6值，包括reviewerIP为NULL的情况。
TRUNCATE后批量导入错误数据：TRUNCATE命令会绕过所有触发器，若有人先清空表，再导入包含错误isIPv6值的数据集（如备份文件中的错误数据），也会出现异常。不过仅部分行异常的话，这条可能性稍低。

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