Postgres 在 18 版中为常见的数据修改语句带来了一项看似小巧但非常实用的改进:允许在 INSERT、UPDATE、DELETE 和 MERGE 的 RETURNING 子句中同时访问修改前的行(OLD)与修改后的行(NEW)。这一增强改善了许多用例的可读性与可靠性,尤其是审计、webhook 通知、upsert(插入或更新)判定与数据变更捕获等场景。理解它的语义与边界对于正确采用新特性非常重要。 在较早的 Postgres 版本中,RETURNING 子句只能直接返回当前版本的行值,而若要获取修改前的值,往往需要借助触发器在独立表中记录,或依赖内部实现细节(例如利用 xmax = 0 判断新插入行)。xmax 的方法虽然在实践中可用,但并非公开契约,未来实现变动可能导致不兼容。Postgres 18 的改进直接把 OLD 与 NEW 在 RETURNING 中暴露出来,语义明确:对于 INSERT,OLD 为 NULL,NEW 包含最终插入的值(包括默认值与触发器修改);对于 DELETE,NEW 为 NULL,OLD 为被删除的值;对于 UPDATE,OLD 与 NEW 都存在,分别表示修改前与修改后各列的完整状态;对于 MERGE,根据匹配与动作类型相应地暴露 OLD 或 NEW。
使用示例可以直观体现该特性的价值。假设有一张表 fruit,执行一条 UPDATE 并返回修改前后的行: UPDATE fruit SET quantity = 300 WHERE item = 'Apples' RETURNING OLD.*, NEW.*; 这会返回旧行与新行的并列字段,便于后续处理或直接传给外部系统。另一个常见场景是 upsert 时判断一条返回的行究竟是新插入的还是被更新的。以前需要依赖 xmax,写法晦涩且不稳健;现在可以简单地使用 OLD IS NULL 来判断: INSERT INTO webhook (id, data) VALUES (@id, @data) ON CONFLICT (id) DO UPDATE SET id = webhook.id RETURNING webhook.*, (OLD IS NULL)::boolean AS is_new; 在这个语句中,若返回行的 OLD 为 NULL,就表示发生了插入操作,is_new 为 true;否则表示为更新操作,is_new 为 false。这个检查明确、语义清晰,也不会依赖内部实现的锁行为。 除了 upsert 判定之外,访问 OLD 与 NEW 在 RETURNING 中还有许多实际用途。
审计是最直接的用例之一:可以在一条 DML 语句结束后,直接将旧值与新值写入审计表或变更日志,从而减少触发器或额外查询的复杂性。举例来说,可以通过 WITH 语句组合把更新与审计写成一个原子操作,既保证事务一致性,又减少客户端与数据库之间的往返。对于需要实时推送变更的 webhook 或消息队列集成,RETURNING 中的 OLD/NEW 能够把完整变更内容一起返回,便于服务器端直接发送通知或入队。 需要注意的是,RETURNING 中的 OLD 与 NEW 所表示的是在同一语句内部最终的行状态,触发器对 NEW 的修改通常会反映在 RETURNING 的结果中。也就是说,BEFORE 行级触发器对 NEW 的改变会出现在 RETURNING 的 NEW 值中;AFTER 触发器如果修改了数据(虽然较少见且受限于可变性),则也会影响最终存储值。由于触发器可以对列做处理或补充,使用 RETURNING 获取到的结果比单独 SELECT 更能反映语句的最终效果。
开发者在引入 RETURNING OLD/NEW 时,务必检查已存在的触发器逻辑以保证结果与预期一致。 对并发与事务语义的理解同样重要。RETURNING 返回的行是在当前事务和语句作用下的结果,直到事务提交之前,这些变更对其他事务仍不可见。利用 RETURNING 向外部系统推送变更时,要考虑是否先提交事务再发送通知,或者在同一事务内收集变更并在提交后异步传递,以避免向外部系统暴露尚未提交的中间状态或在回滚后出现不一致。很多架构选择在事务提交钩子或后台任务中进行最终通知,以确保一致性。 性能方面,RETURNING 本身是高效的,因为它避免了额外的 SELECT 查询和网络往返。
但当返回大量行或包含完整行数据(OLD.* 与 NEW.*)时,序列化与网络传输的开销会增加,可能影响内存使用与带宽。对于批量修改且要返回数万或数百万行的场景,应该谨慎使用 RETURNING 全行方案,评估是否按需只返回必要列、分批次处理或改用日志管道(如逻辑复制或专门的变更数据捕获工具)来降低负担。 与已有代码的兼容性和迁移策略也值得关注。若现有代码依赖 xmax 或其他实现细节来判断插入与更新,则应优先改为 OLD IS NULL 的写法;若 ORM 或数据库访问库在 DML 返回处理中有特殊假设(例如只期待 NEW 行),则需要更新或调整映射逻辑以兼容同时返回 OLD 与 NEW 的场景。由于旧版本的 PostgreSQL 不支持在 RETURNING 中访问 OLD 与 NEW,部署到混合版本环境时应通过版本检测或条件逻辑分别处理,或在升级数据库集群后统一采用新特性。 在使用 RETURNING OLD/NEW 时,也要注意列名冲突。
直接写 RETURNING OLD.*, NEW.* 会导致相同列名重复出现,客户端处理结果时需要明确区分名称,可以对列进行重命名或只选择必要字段,例如 RETURNING OLD.id AS old_id, NEW.id AS new_id, OLD.quantity AS old_qty, NEW.quantity AS new_qty。通过显式别名能大幅提高结果可读性和后续处理的安全性。 MERGE 语句在多逻辑分支的情形下特别受益于 RETURNING OLD/NEW,开发者可以直接在 MERGE 的 RETURNING 中检查 OLD 与 NEW 的存在性来判断每一条记录是被插入、更新还是删除,从而得到类似 change_type 的标记,简化后续处理流程。例如可以为每种动作返回不同的标识,或将完整旧新值记录到审计表,形成单条语句即可完成的数据同步工作流。 安全与权限方面,使用 RETURNING 返回的列受表级读权限控制。确保调用者拥有查看相关列的权限,避免在 RETURNING 中暴露敏感字段。
如果需要将变更的详细信息传给第三方系统,建议只返回需要的非敏感字段或在返回之前脱敏处理。 总的建议是:在兼容 Postgres 18 或更高版本的环境中,优先使用 RETURNING 中的 OLD 与 NEW 来替代基于实现细节的技巧或额外查询。对于常见的 upsert 判定、审计记录与变更推送,用 OLD IS NULL 的写法既简洁又明确。对于大批量变更或对性能敏感的场景,评估是否需要限制返回列或改用其它变更捕获机制。务必结合触发器、事务提交策略与权限管理进行端到端设计,以保证数据一致性与安全性。 Postgres 的这个增强看似微小,却能在很多实际工程问题上带来清晰度和可靠性提升。
正确使用 RETURNING OLD 与 NEW,不仅能简化 SQL 与应用逻辑,还能减少对内部实现细节的依赖,使代码在未来 PostgreSQL 版本演进中更具兼容性与稳定性。开发者应在测试环境中充分验证现有触发器、约束与业务逻辑的互操作性,逐步将旧的 xmax 或额外 SELECT 的做法替换为基于 OLD/NEW 的直接表达。通过合理设计和周到迁移,可以把这项特性转化为提升开发效率和系统可维护性的长期收益。 。
