早上批准的两笔，到中午只剩一笔还能改

目标

只用 SQL 取消获批的两笔订单。把批准时的观测留在表里，写出只有在客户、编号、版本、数量、状态全部保持不变时才会修改的 UPDATE，并在真实的数据库中确认，期间被其他负责人改过的那一笔会有 0 行被修改。

为什么重要

这里把在阅读中看到的契约（批准对象是 id、revision、qty，客户标识 tenant 另算）做成表和函数。查看批准界面的时刻与执行的时刻之间，总是有时间流过。用表锁来堵住这段时间，其他业务就会停摆，所以改为把批准时点的观测留成小数据，应用时用条件比较这份观测是否仍然有效。比较对不上时，不是悄悄改成当前值，而是把那一笔留下来，重新交由人判断。本实验的表主键是 (tenant, id)，所以在条件中去掉客户，别的客户的订单就会被一起取消，这一点也会在同一个地方暴露出来。

紧接着的下一个模块的 fde-revision-lab，用 Python 以八个函数实现同样的契约。本实验在那之前，先用 SQL 这一层看看契约本身在数据库里长什么样。

预计 70 分钟。请在到期前点击“＋时间”延长会话。会话结束后 /root 下的文件会全部消失。

环境

PostgreSQL 16 已经在 Pod 内运行。连接用 psql -X -U lab -d labdb，主机是 127.0.0.1（export PGHOST=127.0.0.1）。不需要互联网或额外安装。

本实验只在 schema scope 内操作。不要修改 public 中已有的实验数据或其他 schema。产出物都放在 /root/scope 下。评分器会把文件里写的数字重新去问活的表来核对，函数步骤则在自己的事务里铺设样本数据、调用你的函数，然后回滚——所以评分多少次，数据都不会变。

步骤

创建 /root/scope/01-schema.sql，建立 schema scope 和两张表。scope.orders 有 tenant、id、qty、state、revision 五列，主键是 (tenant, id)。state 只能是 pending、paid、cancelled，qty 在 1 到 1000 之间，revision 在 0 以上。放入六行——blue/1 是 7 个 pending revision 3，blue/2 是 4 个 pending revision 1，blue/3 是 9 个 pending revision 0，blue/4 是 5 个 paid revision 2，green/1 是 7 个 pending revision 3，green/2 是 4 个 pending revision 1。scope.baseline 有同样的五列和同样的主键，用刚建好的 scope.orders 原样复制填充——这是早上查询界面所展示的那一刻的观测。应用 SQL 之后，在 /root/scope/01-schema.txt 中留下 rows、tenants、pk、baseline_rows 四行。
用 /root/scope/02-approval.sql 建立表 scope.approval——有 change_id、tenant、id、revision、qty 五列，主键是 (change_id, tenant, id)。用变更 ID chg-rain-01，从客户 blue 的 1 号、2 号订单中，只挑选在早上观测（scope.baseline）中为 pending 的，连同当时的 revision 和 qty 一起放入。放入之前，先删除相同 change_id 的已有行，使得无论运行多少次结果都相同。然后在 /root/scope/02-approval.txt 中留下 change_id、targets、digest 三行。digest 是把批准行按 id 顺序写成 id:revision:qty 并用逗号连接起来的字符串。
用 /root/scope/03-validate.sql 创建函数 scope.validate_targets(p jsonb) returns jsonb。输入是批准对象数组。如果不是数组，或者元素为 0 个或超过 16 个，或者元素不是对象，或者键不是 id、revision、qty 这三个，或者三个值中有任何一个不是 JSON 数字（true、false 和字符串都不是数字），或者不是整数，或者 id 在 1 到 2147483647 之外，或者 revision 超过 2147483646，或者 qty 在 1 到 1000 之外，或者同一个 id 出现两次，就以 SQLSTATE 22023 抛出异常。通过时，返回按 id 升序排列的新数组，每个元素只有 id、revision、qty 三个键。
用 /root/scope/04-apply.sql 创建函数 scope.apply_change(p_change_id text) returns table(changed integer, skipped integer)。如果该变更 ID 一个批准行都没有，就以 SQLSTATE 22023 抛出异常。有的话就更新 scope.orders，但只修改 tenant、id、revision、qty 与批准时的值相同、且 state 为 pending 的行。被修改的行 state 变为 cancelled，revision 加 1。changed 是实际被修改的行数，skipped 是批准对象数减去 changed 的值。这一步只创建函数，不要把它应用到 chg-rain-01。
中午期间，其他负责人把 blue 的 2 号订单数量改成了 5。这是正常的业务变更，所以 revision 也加 1。用 /root/scope/05-drift.sql 做出这个变更，但要加上只有 revision 仍是 1 时才应用的条件，使得无论运行多少次结果都相同。然后在 /root/scope/05-drift.txt 中留下 tenant、id、new_qty、new_revision、approved_qty、approved_revision 六行。前三个从现在的 scope.orders 中取，后两个从 scope.approval 中取。
实际应用 chg-rain-01——也就是 select * from scope.apply_change('chg-rain-01')。把结果以 changed、skipped、applied_id、blocked_id、other_tenant_changed 五行留在 /root/scope/06-apply.txt 中。applied_id 是实际被取消的订单编号，blocked_id 是属于批准对象但没有被修改的订单编号。other_tenant_changed 是 green 客户的行中与早上观测不同的行数，必须把 scope.baseline 和 scope.orders 对照起来统计。
用 /root/scope/07-reconcile.sql 创建函数 scope.reconcile(p_change_id text) returns table(id integer, verdict text)。对每个批准行，查看现在的 scope.orders 并给出判定——如果没有相同客户、相同编号的行，是 missing；如果有，state 是 cancelled、revision 是批准值 + 1、qty 与批准值相同，就是 matching；其余都是 drifted。结果按 id 升序排列，批准中没有的编号不要放进去。必须是只读函数。创建之后，用 chg-rain-01 调用，并在 /root/scope/07-reconcile.txt 中留下 matching、drifted、missing、verdicts 四行。verdicts 是把判定按 id 顺序写成 id:판정（占位符为判定）并用逗号连接起来的字符串。
最后，把要发给客户的核对表，用六行做在 /root/scope/08-report.txt 中——approved_targets、applied、drifted、missing、changed_rows_total、changed_outside_approval。前四个取自第 7 步的函数，changed_rows_total 是把 scope.baseline 和 scope.orders 对照起来，qty、state、revision 中任何一个不同的行数；changed_outside_approval 是这些有变化的行中，不属于 chg-rain-01 批准对象的行数。六个值都不要手写，直接放入查询结果。

参考

SQL 文件用 psql -X -U lab -d labdb -q -f 파일이름（占位符为文件名）应用。证据文件里的数字不要手写，要把查询结果重定向进去。
常见错误一：条件里漏掉客户。主键是 (tenant, id)，所以只对上编号的话，两个客户的行会一起被命中。
常见错误二：用 UPDATE 之前的 SELECT 去数被修改的行数。这期间值可能又变了。要把 RETURNING 包进 CTE 里去数。
官方文档：UPDATE、JSON 函数、错误码。

把变更对象表和早上的观测一起留下

创建 /root/scope/01-schema.sql，建立 schema scope 和两张表。scope.orders 有 tenant、id、qty、state、revision 五列，主键是 (tenant, id)。state 只能是 pending、paid、cancelled，qty 在 1 到 1000 之间，revision 在 0 以上。放入六行——blue/1 是 7 个 pending revision 3，blue/2 是 4 个 pending revision 1，blue/3 是 9 个 pending revision 0，blue/4 是 5 个 paid revision 2，green/1 是 7 个 pending revision 3，green/2 是 4 个 pending revision 1。scope.baseline 有同样的五列和同样的主键，用刚建好的 scope.orders 原样复制填充——这是早上查询界面所展示的那一刻的观测。应用 SQL 之后，在 /root/scope/01-schema.txt 中留下 rows、tenants、pk、baseline_rows 四行。

如果把主键定为 id 一个，green 的 1 号订单根本放不进去。客户不同时同一个订单编号可以存在，这是这张表的前提。为了重新运行也得到相同结果，请使用 create table if not exists 和 on conflict do nothing。pk 一行里，按顺序用逗号连接主键的列名。

把批准时的观测钉死在表里

用 /root/scope/02-approval.sql 建立表 scope.approval——有 change_id、tenant、id、revision、qty 五列，主键是 (change_id, tenant, id)。用变更 ID chg-rain-01，从客户 blue 的 1 号、2 号订单中，只挑选在早上观测（scope.baseline）中为 pending 的，连同当时的 revision 和 qty 一起放入。放入之前，先删除相同 change_id 的已有行，使得无论运行多少次结果都相同。然后在 /root/scope/02-approval.txt 中留下 change_id、targets、digest 三行。digest 是把批准行按 id 顺序写成 id:revision:qty 并用逗号连接起来的字符串。

批准快照存放的不是现在的值，而是批准那一刻的值。所以要读取的地方不是 scope.orders，而是 scope.baseline。digest 如果用 string_agg 加上 order by 来做，即使行的顺序变了，也会得到同样的字符串。

在写入之前，先过滤重复、空列表和非数字

用 /root/scope/03-validate.sql 创建函数 scope.validate_targets(p jsonb) returns jsonb。输入是批准对象数组。如果不是数组，或者元素为 0 个或超过 16 个，或者元素不是对象，或者键不是 id、revision、qty 这三个，或者三个值中有任何一个不是 JSON 数字（true、false 和字符串都不是数字），或者不是整数，或者 id 在 1 到 2147483647 之外，或者 revision 超过 2147483646，或者 qty 在 1 到 1000 之外，或者同一个 id 出现两次，就以 SQLSTATE 22023 抛出异常。通过时，返回按 id 升序排列的新数组，每个元素只有 id、revision、qty 三个键。

jsonb_typeof 会把 true、false 区分为 boolean，把加了引号的数字区分为 string。小数点用 typeof 抓不到，所以要转成字符串再检查一次是不是整数。抛出异常时若要指定 SQLSTATE，请用 raise exception using errcode。

只有批准的值全部原样不变时才修改的函数

用 /root/scope/04-apply.sql 创建函数 scope.apply_change(p_change_id text) returns table(changed integer, skipped integer)。如果该变更 ID 一个批准行都没有，就以 SQLSTATE 22023 抛出异常。有的话就更新 scope.orders，但只修改 tenant、id、revision、qty 与批准时的值相同、且 state 为 pending 的行。被修改的行 state 变为 cancelled，revision 加 1。changed 是实际被修改的行数，skipped 是批准对象数减去 changed 的值。这一步只创建函数，不要把它应用到 chg-rain-01。

给 UPDATE 用 FROM 子句接上批准表，就能把五个条件放在一条语句里。实际改了几行，用 CTE 包住 RETURNING 来数才准确——先用 SELECT 数好再 UPDATE，会漏掉其间的变更。评分器会在自己的事务里铺设样本数据并调用这个函数，然后回滚，所以函数里把 schema 名称固定写死就可以。

批准之后，其他负责人改了一笔

中午期间，其他负责人把 blue 的 2 号订单数量改成了 5。这是正常的业务变更，所以 revision 也加 1。用 /root/scope/05-drift.sql 做出这个变更，但要加上只有 revision 仍是 1 时才应用的条件，使得无论运行多少次结果都相同。然后在 /root/scope/05-drift.txt 中留下 tenant、id、new_qty、new_revision、approved_qty、approved_revision 六行。前三个从现在的 scope.orders 中取，后两个从 scope.approval 中取。

批准快照在这一步里一个字都不能变。变的只是当前的行。两个数字互相不同，这个事实本身，就是下一步会有 0 行被修改的原因。

应用之后只有一笔被修改

实际应用 chg-rain-01——也就是 select * from scope.apply_change('chg-rain-01')。把结果以 changed、skipped、applied_id、blocked_id、other_tenant_changed 五行留在 /root/scope/06-apply.txt 中。applied_id 是实际被取消的订单编号，blocked_id 是属于批准对象但没有被修改的订单编号。other_tenant_changed 是 green 客户的行中与早上观测不同的行数，必须把 scope.baseline 和 scope.orders 对照起来统计。

批准对象有两笔，其中一笔的值已经与早上的观测不同了。五个条件全部一致才会修改，所以这一行会直接略过——不是错误，而是 0 行。green 的 1 号订单与 blue 的 1 号订单，编号、版本、数量都相同。条件中有没有客户，会在这里暴露出来。

不按数量，而按对象来核对

用 /root/scope/07-reconcile.sql 创建函数 scope.reconcile(p_change_id text) returns table(id integer, verdict text)。对每个批准行，查看现在的 scope.orders 并给出判定——如果没有相同客户、相同编号的行，是 missing；如果有，state 是 cancelled、revision 是批准值 + 1、qty 与批准值相同，就是 matching；其余都是 drifted。结果按 id 升序排列，批准中没有的编号不要放进去。必须是只读函数。创建之后，用 chg-rain-01 调用，并在 /root/scope/07-reconcile.txt 中留下 matching、drifted、missing、verdicts 四行。verdicts 是把判定按 id 顺序写成 id:판정（占位符为判定）并用逗号连接起来的字符串。

把批准行放在左边，对当前行做 LEFT JOIN，消失的对象就会留成 NULL，从而能区分出 missing。连接条件里要同时放上客户和编号，其他客户的相同编号才不会被连上。数量即使相同，只要版本不同，那就是之后发生的另一次变更。

把批准范围和实际变更并排写出

最后，把要发给客户的核对表，用六行做在 /root/scope/08-report.txt 中——approved_targets、applied、drifted、missing、changed_rows_total、changed_outside_approval。前四个取自第 7 步的函数，changed_rows_total 是把 scope.baseline 和 scope.orders 对照起来，qty、state、revision 中任何一个不同的行数；changed_outside_approval 是这些有变化的行中，不属于 chg-rain-01 批准对象的行数。六个值都不要手写，直接放入查询结果。

最后一行就是本实验的结论。它是用对象而不是数量，来展示批准范围之外没有任何东西被改变的数字。比较早上的观测和现在时，可能混进 NULL，所以用 is distinct from 比较安全。