PostgreSQL触发器实现列不可变：INSERT默认值拦截问题求解

问题分析 #

你的触发器函数在UPDATE时逻辑正确，但INSERT时的判断逻辑存在错误：INSERT操作中OLD行不存在（值为NULL），因此_old_value始终为NULL，原代码中ELSIF TG_OP = 'INSERT' AND _old_value IS NOT NULL THEN的条件永远不会触发。而你遇到的报错，大概率是代码中误将判断条件写为_new_value IS NOT NULL，导致默认值填充的列被误判为用户显式设置的值。

此外，核心矛盾在于：你需要区分用户显式设置列值和数据库自动填充默认值的场景，但PostgreSQL触发器无法直接获取用户是否在INSERT语句中指定了某列。

解决方案 #

方案一：使用PostgreSQL权限控制（推荐） #

通过数据库权限系统直接禁止普通用户在INSERT时指定受保护列，这是最可靠且简洁的方法：

-- 禁止普通用户在INSERT时指定受保护列
REVOKE INSERT (id, user_id, created_at, last_modified_at, last_modified_by) ON protected_table FROM PUBLIC;

-- 保留postgres用户的权限（如需管理员能显式设置这些列）
GRANT INSERT (id, user_id, created_at, last_modified_at, last_modified_by) ON protected_table TO postgres;

效果：

• 普通用户在INSERT语句中显式指定受保护列时，会直接触发权限错误；
• 不指定受保护列时，数据库自动用默认值填充，不会触发触发器报错；
• 原触发器的UPDATE逻辑保持不变，继续阻止修改受保护列。

方案二：修改触发器函数（适用于稳定默认值场景） #

如果必须通过触发器实现，可修改函数逻辑，通过对比列的默认值来判断是否为用户显式设置的值。注意：此方法仅适用于稳定/不可变的默认值（如DEFAULT now()、DEFAULT 0），对于gen_random_uuid()这类每次生成不同值的volatile默认值不适用。

修改后的函数：

CREATE OR REPLACE FUNCTION guard_columns()
  RETURNS TRIGGER
  AS $$
DECLARE
  _column TEXT;
  _old_value TEXT;
  _new_value TEXT;
  _default_expr TEXT;
  _default_value TEXT;
BEGIN
  IF CURRENT_USER != 'postgres' THEN
    FOR i IN 0..TG_NARGS - 1 LOOP
      _column := TG_ARGV[i];
      
      EXECUTE FORMAT('SELECT ($1).%I::TEXT', _column)
      USING OLD INTO _old_value;
      EXECUTE FORMAT('SELECT ($1).%I::TEXT', _column)
      USING NEW INTO _new_value;

      -- UPDATE时阻止修改
      IF TG_OP = 'UPDATE' AND _old_value IS DISTINCT FROM _new_value THEN
        RAISE invalid_parameter_value
        USING message = FORMAT('Attempt to modify immutable column: %I', _column);
      -- INSERT时判断是否为用户显式设置
      ELSIF TG_OP = 'INSERT' THEN
        -- 获取列的默认表达式
        SELECT pg_get_expr(adbin, adrelid) INTO _default_expr
        FROM pg_attrdef
        WHERE adrelid = TG_RELID AND adnum = (SELECT attnum FROM pg_attribute WHERE attrelid = TG_RELID AND attname = _column);
        
        IF _default_expr IS NOT NULL THEN
          -- 执行默认表达式获取当前默认值
          EXECUTE FORMAT('SELECT %s::TEXT', _default_expr) INTO _default_value;
          -- 若NEW值与默认值不同，说明用户显式设置了值
          IF _new_value IS DISTINCT FROM _default_value THEN
            RAISE invalid_parameter_value
            USING message = FORMAT('Attempt to set value for immutable column: %I', _column);
          END IF;
        ELSE
          -- 无默认值的列，禁止用户设置任何值
          IF _new_value IS NOT NULL THEN
            RAISE invalid_parameter_value
            USING message = FORMAT('Attempt to set value for immutable column: %I', _column);
          END IF;
        END IF;
      END IF;
    END LOOP;
  END IF;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

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