Postgres语句级AFTER UPDATE触发器如何关联OLD TABLE与NEW TABLE？

首先，你当前遇到的missing FROM-clause entry for table "old_table"错误很直接：你的触发器函数里只从new_table查询，但引用了old_table的字段却没把它加入FROM子句。不过这只是表面问题，核心的挑战是无主键/未知主键时如何关联新旧行，以及行顺序的可靠性问题，我们一步步来解决。

1. 先修复当前的语法错误 #

要同时获取新旧行的数据，你需要在查询中关联old_table和new_table。如果表有主键的话，这一步很简单，比如假设表有主键id：

CREATE OR REPLACE FUNCTION audit_update_operations() RETURNS TRIGGER AS $$
DECLARE
  user_id UUID;
BEGIN
  user_id := coalesce(current_setting('audit.AUDIT_USER', TRUE), '77777777-0000-7777-0000-777777777777')::UUID;
  INSERT INTO dml_audit_log 
  SELECT 
    now() AS changed_at, 
    user_id, 
    'U' AS operation, 
    tg_table_name::TEXT AS table_name, 
    ROW(n.*) AS data_after, 
    ROW(o.*) AS data_before
  FROM new_table n
  JOIN old_table o ON n.id = o.id; -- 用主键关联新旧表
  RETURN NULL;
END ;
$$ LANGUAGE plpgsql;

但你提到没有主键或未知主键，这就需要更复杂的处理了。

2. 无主键时的行关联困境 #

PostgreSQL的语句级触发器中，OLD TABLE和NEW TABLE分别保存了更新前后的所有受影响行，但没有内置的关联机制——因为如果表没有唯一标识（主键、唯一约束），数据库本身也无法区分哪些旧行对应哪些新行。比如，如果表中有两行完全相同的数据，执行UPDATE table SET col = val WHERE col = old_val时，数据库没法确定旧行A对应新行X还是Y。

不可靠的“替代方案”（不推荐） #

有人可能会想到用系统列比如ctid或xmin，但这些都有严重局限性：

• ctid：行的物理位置，更新后新行的ctid会改变，OLD TABLE中的ctid是旧行的位置，和NEW TABLE的ctid完全不对应。
• xmin：创建行的事务ID，旧行的xmin是插入时的事务ID，新行的xmin是当前更新的事务ID，也没法关联。
• 用所有字段关联：如果更新的字段正好是你用来关联的字段，这就完全失效了，而且如果有重复行，还是会出错。

这些方案都依赖未公开的实现细节或存在严重缺陷，绝对不能用于生产环境。

3. OLD TABLE与NEW TABLE的行顺序：官方明确无保证 #

根据PostgreSQL官方文档，OLD TABLE和NEW TABLE中的行顺序没有任何官方保证，完全由数据库的执行计划决定。依赖顺序来关联行是非常危险的，一旦执行计划变化（比如数据量变化、统计信息更新），你的审计日志就会完全错误。所以这个思路直接放弃。

4. 可行的解决方案 #

方案一：给业务表添加主键（最优） #

这是从根源解决问题的方法。没有主键的表本身就存在很多问题：数据一致性风险、更新/删除性能低下、无法可靠关联数据等。给每个业务表添加主键后，你就可以用主键稳定关联OLD TABLE和NEW TABLE，语句级触发器的性能优势也能完全发挥。

方案二：退而求其次，记录整体变化（如果能接受审计粒度降低） #

如果实在无法添加主键，你可以分别记录旧数据集和新数据集，但无法关联具体行。比如：

CREATE OR REPLACE FUNCTION audit_update_operations() RETURNS TRIGGER AS $$
DECLARE
  user_id UUID;
BEGIN
  user_id := coalesce(current_setting('audit.AUDIT_USER', TRUE), '77777777-0000-7777-0000-777777777777')::UUID;
  -- 记录所有旧行
  INSERT INTO dml_audit_log 
  SELECT now(), user_id, 'U_OLD', tg_table_name::TEXT, NULL, ROW(o.*)
  FROM old_table o;
  -- 记录所有新行
  INSERT INTO dml_audit_log 
  SELECT now(), user_id, 'U_NEW', tg_table_name::TEXT, ROW(n.*), NULL
  FROM new_table n;
  RETURN NULL;
END ;
$$ LANGUAGE plpgsql;

这种方式虽然能记录更新前后的所有数据，但没法对应到具体哪一行变成了哪一行，审计粒度比行级触发器低。

方案三：继续使用行级触发器（性能可接受的情况） #

如果业务表的更新频率不高，或者性能提升的需求不迫切，继续使用行级触发器是最稳妥的选择——它能准确记录每一行的变化，不需要依赖主键（虽然没有主键的话，行级触发器也没法区分重复行，但至少能记录每一次行级的修改）。

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