并发更新审计日志失真？PostgreSQL 18 单语句修复
原文：https://dev.to/remdore/your-audit-log-is-probably-lying-to-you-postgres-18-fixes-it-in-one-statement-15nb（作者 @remdore）
我用四五种语言写过很多次这样的代码：
SELECT balance FROM accounts WHERE id = 1; -- 应用程序执行算术 UPDATE accounts SET balance = $new WHERE id = 1; INSERT INTO audit_log (account_id, old_balance, new_balance) VALUES (1, $old, $new);
读出来，算好，写回去。记录下发生了什么。在代码审查中，这看起来毫无问题，但一旦有两个并发请求，它立刻就会崩溃。烦人的是，你为了捕捉此类问题而添加的审计日志，恰恰掩盖了问题。
PostgreSQL 18 将这个过程整合到了一个语句中。但在那之前，这个 bug 值得仔细研究，因为当它最终发生时，造成的损害比我预想的要大得多。
复现问题
假设一个账户初始余额为 100。十个并发工作者，每个取出 10。结果本应显而易见：最终余额为零，审计日志应呈现阶梯式下降，从 100 到 90，再到 80，依此类推。
每个工作者都执行上述的“读-再-写”操作。我在中间加入了 50 毫秒的间隔，以便每次运行的时机都相同：
最终余额 | 90 审计行数 | 10 记录的 distinct old_balance 值数量 | 1
old_balance | new_balance | times_logged
-------------+-------------+--------------
100 | 90 | 10
九十。九笔提款凭空消失。所有十个工作者都读取了相同的初始值 100，写回了相同的值 90，并且每一个都把自己记录为执行者。
后一点正是我希望有人能注意到的地方。问题不仅是钱少了。审计日志看起来自洽。十行整齐的记录，每一行内部都一致，没有空缺，没有空值，任何验证器都不会标红。如果事件发生后你把这张表给我看，我会得出结论：是同一笔提款被重试了十次。然后我会去检查重试逻辑，发现它没问题。
去掉 sleep 语句后，结果不再是确定的，但问题依然存在。连续运行三次：
最终余额 50，记录的 distinct old 值数量 5 最终余额 40，记录的 distinct old 值数量 6 最终余额 60，记录的 distinct old 值数量 4
在这台机器上，没有做任何速度限制的情况下，10 次写入中有 4 到 6 次丢失。你不需要调试器和特定运气也能碰到它。这基本上就像抛硬币一样随机。
单语句版本
PostgreSQL 18 允许你在 RETURNING 子句中直接命名变更前后的行：
UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING old.balance AS was, new.balance AS now;
was | now -----+----- 100 | 90
这里有两个变化，而有趣的那个并非新语法。
在语句内部执行 balance - 10，意味着读和写是原子性的，所以没有任何间隙能让其他人插入。这其实一直都可以做到，而这正是阻止更新丢失的关键点。
PostgreSQL 18 新增的是，你可以在同一个替换了值的语句中，被告知该值曾经是多少。不是你上次查看时的值，而是这个特定的 UPDATE 操作实际覆盖掉的值。因此，审计行可以由同一操作写入：
WITH moved AS ( UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING old.balance AS was, new.balance AS now ) INSERT INTO audit_log (account_id, old_balance, new_balance) SELECT 1, was, now FROM moved;
十个并发工作者同时操作：
最终余额 | 0 审计行数 | 10 记录的 distinct old_balance 值数量 | 10
old_balance | new_balance
-------------+-------------
100 | 90
90 | 80
80 | 70
70 | 60
60 | 50
50 | 40
40 | 30
30 | 20
20 | 10
10 | 0
零，而且阶梯式记录完好无损。十个工作者同时操作，日志依然按序输出，因为每一行都是由执行操作的语句本身写入，而不是由应用程序重复它片刻前被告知的值。
在 PostgreSQL 18 之前，你也能实现这一点，但需要使用带有 OLD 和 NEW 的触发器，这意味着审计逻辑会存在于应用程序代码阅读者永远看不到的地方。
最能节省时间的用法
RETURNING old 还附赠了一个额外功能。在执行 INSERT 时没有旧行，因此 old 为 null。这意味着 upsert 操作终于可以告诉你它走了哪个分支：
INSERT INTO accounts VALUES (3, 10) ON CONFLICT (id) DO UPDATE SET balance = EXCLUDED.balance RETURNING old.id IS NULL AS was_inserted, old.balance, new.balance;
第一次运行时，不存在对应行：
was_inserted | balance | balance --------------+---------+--------- t | | 10
再次对已存在的该行运行：
was_inserted | balance | balance --------------+---------+--------- f | 10 | 20
如果你使用 PostgreSQL 有一段时间，你会认出这替代了什么。老方法是：
RETURNING (xmax::text::bigint <> 0) AS was_update
读取系统列，将其转换为文本，再转换为 bigint，然后与零比较，以此判断当前语句是执行了插入还是更新。这方法可行，它遍布于老旧的代码库中。但它需要你知道 xmax 是什么，对于不了解的人来说，这看起来就像个 bug。
而 old.id IS NULL 则一目了然，无需脚注。
我在初次尝试时搞错的三件事
在 `INSERT` 时，`old` 下的所有内容均为 `null`，而在 `DELETE` 时，`new` 下的所有内容均为 `null`。 一旦说出口，这显而易见，但当你只编写了一个审计辅助函数并让所有语句都指向它时，就很容易忽略。在 INSERT 时记录 old.balance，你会永远静默地得到 null。
我假设有列名为 `old` 的表会出错。 实际上不会。裸名 old 仍然解析为你的列，因此现有查询仍然有效：
UPDATE legacy SET old = 'CHANGED2' WHERE id = 1 RETURNING old; -- 返回 CHANGED2，是列的值，而非更新前的行数据
是 old. 和 new. 这两个前缀激活了别名。如果需要在单条语句中同时使用两者，可以重命名它们：
RETURNING WITH (OLD AS prev, NEW AS cur) prev.old, cur.old
此功能仅适用于 PostgreSQL 18。 在 17 版本上，你会收到一个错误，该错误并未明确指向版本问题：
ERROR: missing FROM-clause entry for table "old"
我曾在 postgres:17 上运行以验证，如果你是通过搜索该错误信息来到这里的，那么这就是答案。
亲手试一试
以上所有内容均来自在笔记本电脑上运行的 Docker 镜像 postgres:18，版本 18.6。无需云账户，无需注册：
docker run -d --name pg18 -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo postgres:18 docker exec -it pg18 psql -U postgres -d demo
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL); INSERT INTO accounts VALUES (1, 100); UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING old.balance AS was, new.balance AS now;
你的并发数据不会与我的完全一致，这正是竞态条件的意义所在。多运行几次那个朴素版本，观察最终的余额每次落在不同的地方。
我从中获得的启示
这个功能本身很小。一句话就能描述：RETURNING 现在理解 old 和 new。
它之所以值得我花一个晚上去研究，是因为它在此过程中揭示的问题。我多年来一直在编写“先读后写”的模式，并且添加审计表来捕获该模式所产生的那类问题，却从未注意到审计表继承了相同的竞态条件，因此也无法察觉它。十行数据，看似完全一致，实则全部错误。
如果你的代码库中存在这种模式，那么将计算逻辑移入 SQL 是紧急的修复措施，它适用于任何版本。而 old/new 的这一半功能，则让你能够删除那个为了规避该问题而编写的触发器。
原文：https://dev.to/remdore/your-audit-log-is-probably-lying-to-you-postgres-18-fixes-it-in-one-statement-15nb（作者 @remdore）