PostgreSQL触发器实现自定义数据版本管理系统

触发器的能力边界得先划清楚：它只能搞定“自动快照”和“版本号递增”这类确定性动作，真要跑完整版本管理系统，还得靠应用层或额外架构来补全历史一致性、查询回溯、软删语义这些硬需求。下面把几个关键细节掰开来说。

触发器里怎么拿到操作类型（INSERT/UPDATE/DELETE）

直接上TG_OP这个内置变量，它是大小写敏感的字符串，值分别是'INSERT'、'UPDATE'、'DELETE'。千万别直接写IF TG_OP = 'update'，小写永远不匹配——别问我怎么知道的。

常见翻车现场：触发器函数看起来执行了，历史表里却啥也没有。大概率是TG_OP字符串比较时大小写写错，或者漏了单引号。

使用场景很清晰：

• INSERT时初始化version = 1和created_at
• UPDATE时读OLD.version，让NEW.version = OLD.version + 1
• DELETE时想走软删，就插入快照后RETURN NULL，阻止物理删除

行级触发器中访问新旧数据的限制

NEW和OLD只在对应操作里可用：INSERT没有OLD，DELETE没有NEW，UPDATE两者都有。试图在INSERT里读OLD.id，会直接报错record "old" is not assigned。

性能影响不能忽视：如果历史表（比如users_history）没建主键或索引，UPDATE触发器里跑INSERT INTO users_history SELECT OLD.*，数据量一上来，主表写入速度会被明显拖慢。

实操建议：

• 历史表必须建索引，至少要在(id, plt_data version)上搞唯一复合索引
• 别在触发器里做复杂计算或跨库查询，PL/pgSQL不适合高并发下的长事务
• 字段名冲突要提前规避：原表已有version，就改用plt_data version，否则NEW.version赋值会失败

为什么不能只靠触发器还原任意时间点的数据

触发器只保证“每次变更都存一份快照”，但没记录变更之间的依赖关系，也不保存事务边界。比如一个事务里更新了3行，触发器会插入3条历史记录，但你没法知道这3条属于同一个逻辑操作。

容易踩的坑：

• 并发UPDATE同一行：两个事务同时读到OLD.version = 5，都设NEW.version = 6，版本号直接冲突
• 批量UPDATE（UPDATE t SET x = x + 1 WHERE y > 100）会为每行触发一次，但从历史表里反推不出“这批更新是哪个业务动作发起的”
• 触发器不捕获DDL，表结构变了（比如删了age字段），旧历史记录里的age值还在，但新插入的历史行会因字段缺失报错

真正需要时间点恢复时，得配合pg_dump -t users_history --inserts定时导出，或者用WAL归档+PITR，而不是指望触发器日志。

触发器函数部署后怎么验证是否生效

别只看SELECT tgname FROM pg_trigger，那只能说明对象存在。必须实测DML并查历史表：

执行INSERT INTO users (name) VALUES ('alice');后，立刻查SELECT * FROM users_history WHERE id = currval('users_id_seq');，看有没有一条plt_data version = 1的记录。

关键检查点：

• 触发器绑定的是BEFORE还是AFTER？BEFORE才能修改NEW，AFTER只能读
• 触发器是FOR EACH ROW还是FOR EACH STATEMENT？历史存档必须用行级
• 函数里有没有漏掉RETURN NEW或RETURN OLD？漏了会导致主表操作被静默丢弃

最容易被忽略的是：触发器函数语言声明写成LANGUAGE plpgsql，但实际用了$$分隔符却没配对，导致函数创建成功但调用时报语法错误——这种问题只能靠SELECT pg_get_functiondef(oid)查一下函数源码来确认。