PostgreSQL BEFORE UPDATE触发器致新增列更新失效问题求助

问题场景 #

• 表通过BEFORE UPDATE触发器自动维护updated_at列
• 直接新增列后执行UPDATE语句设置新列值，操作正常生效
• 但若先对原有列执行UPDATE触发过触发器，再新增列并执行UPDATE设置新列值，更新操作无效果，新列仍为NULL
• 临时禁用触发器可作为应急解决方案

问题根源 #

这是PostgreSQL的触发器函数执行计划缓存导致的：当触发器第一次被触发时，PostgreSQL会缓存触发器函数的执行计划，其中包含了当时的表结构信息。后续新增列后，缓存的计划并未更新，仍然基于旧表结构执行，导致针对新列的更新操作被忽略。

解决办法 #

1. 刷新触发器函数缓存（推荐）
   新增列后，执行以下语句重置触发器函数的缓存，让PostgreSQL重新生成包含新表结构的执行计划：

   ALTER FUNCTION set_updated_at() RESET ALL;
   

   替换set_updated_at()为你的触发器函数名即可。

2. 使用动态SQL编写触发器函数（长期优化）
   如果需要频繁调整表结构，可以改用动态SQL来更新updated_at，避免依赖固定的表结构缓存：

   CREATE OR REPLACE FUNCTION set_updated_at()
   RETURNS TRIGGER AS $$
   BEGIN
       EXECUTE 'SELECT CURRENT_TIMESTAMP INTO NEW.updated_at';
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   

复现示例 #

-- 创建测试表和触发器
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    updated_at TIMESTAMP
);

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_updated_at
BEFORE UPDATE ON test_table
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 插入测试数据
INSERT INTO test_table (name) VALUES ('initial');

-- 步骤1：触发触发器
UPDATE test_table SET name = 'triggered' WHERE id = 1;

-- 步骤2：新增列
ALTER TABLE test_table ADD COLUMN new_col VARCHAR(50);

-- 步骤3：更新新列（此时无效果）
UPDATE test_table SET new_col = 'test_value' WHERE id = 1;

-- 查询验证，new_col为NULL
SELECT * FROM test_table WHERE id = 1;

-- 执行刷新缓存操作
ALTER FUNCTION set_updated_at() RESET ALL;

-- 再次更新新列（此时生效）
UPDATE test_table SET new_col = 'test_value' WHERE id = 1;

-- 查询验证，new_col已更新
SELECT * FROM test_table WHERE id = 1;

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