# 查询与事务候选规则

LLMS 索引： [llms.txt](/llms.txt)

---

查询规约要保护的是调用合同，事务规约要保护的是失败后的正确性。两者都不适合简化成 SQL 风格检查：`SELECT *` 在交互诊断中很方便，在持久 API 中却会制造列漂移；CTE 可能清晰表达关系步骤，也可能引入不必要 materialization；短事务通常更友好，但把本应原子的一组写入拆开只会得到更快的错误结果。

这一节先固定语义合同，再讨论代价。第 7–10 章会继续为计划、索引和并发规则补证据。

## 6.4.1 明确列、稳定排序与分页语义 {#item-6-4-1}

持久 query interface 至少声明五件事：

```text
input:
  参数名、类型、NULL、范围和授权上下文

output:
  列名、类型、NULL、单位和兼容策略

cardinality:
  0/1/N 行，是否允许重复

order:
  排序键、方向、NULL、collation、tie-breaker

consistency:
  单条语句 snapshot，还是跨页/跨查询一致视图
```

`DEFAULT-QUER-006` 因此要求稳定接口显式投影：

```sql
SELECT
    o.order_id,
    o.order_no,
    o.order_status,
    o.currency_code,
    o.placed_at
FROM shop.sales_order AS o
WHERE o.customer_id = $1;
```

这不是因为 `SELECT *` 在服务器内部必然更慢，而是因为隐式列集合会随 DDL 变化，扩大网络与权限面，破坏 positional decoder，并让调用方不知不觉依赖内部列。短期 `psql` 探索可以使用 `*`；稳定 view consumer、API query 和 migration copy contract 不应使用。

### 没有 `ORDER BY` 就没有顺序合同

PostgreSQL 文档明确指出，不指定 `ORDER BY` 时，返回顺序未定义。一次执行看起来按 primary key 或 heap 顺序返回，只是当前 plan、数据布局和并发状态的结果。加 `LIMIT` 也不会把偶然顺序变成合同：

```sql
-- 不稳定：同一价格之间没有 tie-breaker
ORDER BY total_minor DESC
LIMIT 20;

-- 稳定全序：最后一个键唯一且方向明确
ORDER BY total_minor DESC, order_id DESC
LIMIT 20;
```

唯一 tie-breaker 是 `SAFE-PAGE-010` 的底线。若排序列可为 `NULL`，API 还要固定 `NULLS FIRST/LAST`；若排序受 collation 影响，要固定 collation/normalization，或用稳定 binary/normalized key。否则 cursor 编码相同值时，不同环境可能得到不同边界。

### keyset cursor 必须编码完整排序键

本章样例按：

```sql
ORDER BY placed_at DESC, order_id DESC
```

向后取下一页：

```sql
WHERE placed_at IS NOT NULL
  AND (placed_at, order_id) < ($cursor_placed_at, $cursor_order_id)
ORDER BY placed_at DESC, order_id DESC
LIMIT $page_size;
```

成立前提是两个键都非 NULL、比较语义与排序一致，最后的 `order_id` 唯一。cursor 至少编码两个值、sort version/direction 和必要的 filter identity；对外暴露时通常还需要签名或完整性保护，避免调用方伪造超范围条件。

若混用 ASC/DESC、NULL 或不同 collation，不能机械复制 row comparison；应展开为与排序完全等价的 predicate，并写边界测试。反向翻页也不是把 `<` 改成 `>` 就结束，还要反转内部 order、取得一页后恢复 API 顺序。

`OFFSET` 不是永远禁止：小型后台界面、稳定 snapshot 内的有限页数可以接受。但大 offset 仍要计算并丢弃前面的行；在 Read Committed 下跨页查询之间发生 insert/delete 时，还可能重复或遗漏。keyset 避免按位置跳过，却不能自动提供跨页 snapshot 一致性；排序键被更新时也可能移动。API 必须声明自己提供“实时游标”还是“固定快照导出”。

### 用结果合同而不是 SQL 文本做验收

[`query-contract.sql`](/labs/ch06/query-contract.sql) 不要求 application 复制某一段 SQL 字符串，而是验证：

- `shop_api.order_summary` 恰好包含 11 个发布列；
- 排序显式为 `placed_at DESC, order_id DESC`；
- 第一页和第二页 cursor 严格前进且不重叠；
- `order_no` business key 与 `request_key` idempotency key 仍唯一。

典型输出：

```text
status=ok
query_contract=explicit-columns+stable-keyset
view_column_count=11
cursor_order=placed_at-desc,order_id-desc
page_1_order_id=1002
page_2_order_id=1001
pages_do_not_overlap=t
business_key_unique=t
idempotency_key_unique=t
```

教学 fixture 只有两笔订单，所以这不是性能 benchmark，也没有覆盖 NULL、同 timestamp、大页数和并发移动。它证明 baseline 的最小语义；API 上线前还要添加这些边界用例。

## 6.4.2 事务大小、超时、重试与幂等 {#item-6-4-2}

“事务越短越好”缺少一个关键限定：事务必须先覆盖保持不变量所需的完整正确性单元，然后才在这个边界内缩短。

以“创建订单并预占库存”为例：

```text
BEGIN
  validate request key
  insert order
  insert order lines
  reserve inventory
  record durable event/outbox intent
COMMIT
```

如果这些数据库事实必须共同成立，就不能为了缩短 transaction 把它们拆成无补偿的独立 commit。真正应该移出去的是用户输入、HTTP 调用、邮件发送、长时间计算和无边界 sleep。`DEFAULT-TXNN-007` 要求 transaction diagram 标出：

```text
BEGIN → first lock → database work → COMMIT
                    ↘ external wait?  应移出或重构
```

对大批处理则分批 commit，但必须定义 partial progress、restart cursor、幂等与最终 reconciliation。分批不是放弃原子性，而是把正确性单元重新定义为可恢复的小批次。

### 首个错误才是根因

显式 transaction 中第一条 statement error 会使 transaction 进入 failed state；后续普通 SQL 通常只返回 `25P02 in_failed_sql_transaction`。应用必须保存第一个 SQLSTATE，然后：

- 整体 `ROLLBACK`；或
- 回到事先建立、且业务语义允许的 savepoint。

不能在收到 `25P02` 后继续发业务 SQL，也不能把 failed/idle-in-transaction connection 原样归还 pool。driver/framework 的 cleanup 必须在归还连接前 rollback，并检查 transaction 状态。

第 5 章实验已经证明：

```text
22012 → 25P02
23514 → ROLLBACK TO SAVEPOINT → valid statement → outer ROLLBACK
```

savepoint 是局部恢复工具，不是“忽略错误继续”。若失败改变了后续决策所依赖的业务语义，最安全的边界仍是整体重试。

### timeout 是失败合同的一部分

statement/lock timeout 触发后，当前 statement 失败；若处于显式 transaction，transaction 同样需要 rollback/savepoint 恢复。应用必须区分：

- query 被 server 明确取消；
- 获取 lock 超时；
- client deadline 先到并关闭/取消连接；
- 网络断开导致 commit outcome 不明确。

它们不能统一成“再执行一次”。数据库可能明确回滚 statement，也可能已经 commit 但 ACK 丢失。

### 重试整个正确性单元

`SAFE-RETR-008` 目前定义：

```text
SQLSTATE allowlist（例如 40001 / 40P01）
  → 丢弃旧 transaction/snapshot
  → bounded exponential backoff + jitter
  → 在总 deadline 内从 BEGIN 重跑完整单元
  → 超限后向调用方返回可归因错误
```

`40001 serialization_failure` 与 `40P01 deadlock_detected` 常常可以整体重试，但“可以”仍依赖操作幂等、时间预算和 contention。不能只重放最后一条 SQL：前面的读取与判断来自已经失效的 snapshot。也不能把所有 `08xxx` connection exception 无条件重试，因为 commit 可能已经成功。

allowlist 要按 driver 暴露的 SQLSTATE/class 检查，不能按本地化 message substring。最大次数之外还要有总 deadline，避免数据库过载时 retry storm；jitter 用于打散竞争者，不保证消除热点。

### 幂等要闭合 ambiguous outcome

创建订单使用独立 `request_key`：

```sql
INSERT INTO shop.sales_order (..., request_key)
VALUES (..., $request_key)
ON CONFLICT (request_key) DO NOTHING
RETURNING order_id;
```

但 `DO NOTHING` 只是起点。冲突后必须查询权威结果，并验证同一个 idempotency key 对应的业务 payload 是否一致；否则客户端错误复用 key 会被误当成成功。key 的作用域、保留时间和并发行为都要写入合同。

外部支付、HTTP、消息和邮件不随 PostgreSQL rollback 自动撤销。常见方案是先在同一 database transaction 内写 durable intent/outbox，再由独立 worker 幂等投递；或者由外部系统提供相同 idempotency key 和可查询 outcome。无论采用哪种方案，都要回答：

```text
commit ACK 丢失后查谁？
重复投递怎样识别？
数据库成功、外部失败怎样补偿？
外部成功、数据库未知怎样 reconciliation？
```

本章只把这些问题固化为 review rule。真正的自动重试、deadlock/serialization fixture 与 ambiguous outcome 演练安排在 ch10。因此 v0.1 诚实输出 safety 自动/运行覆盖 9/10。

## 6.4.3 CTE、窗口函数与 `LATERAL` 的可读性门槛 {#item-6-4-3}

高级 SQL 的评审不能变成关键字黑名单。`WITH`、window 和 `LATERAL` 都能让关系责任更直接，也都可能在错误数据分布下产生高成本。`PREF-ASQL-004` 的门槛是：reviewer 能用一句话说明每个构造负责什么，并且样例、边界测试与 plan evidence 支持它。

### CTE：命名关系步骤，也可能改变优化边界

CTE 适合给复杂关系步骤命名：

```sql
WITH paid_orders AS (
    SELECT o.customer_id, o.order_id, o.total_minor
    FROM shop.sales_order AS o
    WHERE o.order_status = 'paid'
)
SELECT customer_id, sum(total_minor)
FROM paid_orders
GROUP BY customer_id;
```

在当前支持版本中，一个无副作用、非递归、只引用一次的 CTE 通常可折叠进父查询；多次引用通常会 materialize。`MATERIALIZED` 与 `NOT MATERIALIZED` 可以显式影响决策，但不是性能咒语：materialization 可能避免重复昂贵计算，也可能阻止父查询 predicate 下推。含 volatile function 或数据修改的 CTE 又有不同语义。

因此，不能继续沿用“PostgreSQL 的 CTE 永远是优化栅栏”这类跨版本口号。每个显式 materialization 都要说明是为了稳定语义、避免重复工作，还是经过计划对照后的成本选择。

### Window：在同一行集上分析，不替代输出排序

window function 保留输入行，同时计算 partition/order/frame 内的值：

```sql
SELECT
    customer_id,
    order_id,
    placed_at,
    row_number() OVER (
        PARTITION BY customer_id
        ORDER BY placed_at DESC, order_id DESC
    ) AS customer_order_rank
FROM shop.sales_order;
```

window 的 `ORDER BY` 决定窗口计算顺序，不保证最终 result order；对外返回仍需顶层 `ORDER BY`。`last_value` 等函数还受默认 frame 影响，必须显式审查 frame。多个不同 window order 可能引入多次 sort；计划与 `work_mem`/spill 证据留到第 7、8 章。

### `LATERAL`：表达逐行依赖，也可能放大外层基数

`LATERAL` 允许 FROM item 引用左侧 item，适合“每个 customer 最近两笔订单”：

```sql
SELECT
    c.customer_id,
    recent.order_id,
    recent.placed_at
FROM shop.customer AS c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.placed_at
    FROM shop.sales_order AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 2
) AS recent;
```

它可以把 application N+1 合并为一次 SQL，也常对应按外层每行执行的参数化路径。外层基数、内层索引和 `loops` 决定它是高效 top-N 还是放大器。评审不能因为“只有一条 SQL”就判断更快。

### 计划证据不做节点名 golden test

`PREF-PLAN-005` 明确：

- Seq Scan 不自动错误，小表/低选择性时可能最优；
- Nested Loop 不自动错误，参数化小结果与合适索引时可能最优；
- planner cost 不是毫秒；
- 一次 `EXPLAIN ANALYZE` 不是未来预测；
- 强制 planner GUC 或新增索引前，先看 estimate/actual、loops、buffers、wait、参数和数据分布。

本章 gate 只验证 query semantics，不固定 plan node。精确 plan evidence 在 ch07 引入，慢查询闭环在 ch08，索引写放大与收益在 ch09。这样的章节边界防止 baseline v0.1 提前把尚未实验的性能偏好升级成 safety。

## 本节验收问题

1. 稳定 query 是否显式列出输入、输出和 cardinality；
2. 对外 result 是否显式排序，并以唯一键形成全序；
3. cursor 是否编码全部 sort keys、direction、NULL/collation 与失效语义；
4. 是否明确需要实时分页还是跨页一致 snapshot；
5. transaction 是否覆盖完整不变量，同时排除用户/远程等待；
6. 首个 SQLSTATE 是否保留，失败连接是否在回 pool 前 rollback；
7. retry 是否重跑完整 transaction，带 allowlist、backoff、jitter、次数和总 deadline；
8. ambiguous commit 是否能通过 idempotency key 与权威查询闭合；
9. 外部副作用是否有 durable intent、幂等或 reconciliation；
10. 每个 CTE/window/LATERAL 是否有一句话职责、边界用例和计划证据；
11. 是否避免用节点名、cost 或一次耗时做 blanket rule。

当这些答案进入 query contract 和变更证据后，SQL 才从“现在能跑”升级为“失败后仍可推理”。

## 参考资料

- [PostgreSQL 18：Sorting Rows](https://www.postgresql.org/docs/18/queries-order.html)
- [PostgreSQL 18：LIMIT and OFFSET](https://www.postgresql.org/docs/18/queries-limit.html)
- [PostgreSQL 18：SELECT](https://www.postgresql.org/docs/18/sql-select.html)
- [PostgreSQL 18：WITH Queries](https://www.postgresql.org/docs/18/queries-with.html)
- [PostgreSQL 18：Window Functions](https://www.postgresql.org/docs/18/tutorial-window.html)
- [PostgreSQL 18：Table Expressions and `LATERAL`](https://www.postgresql.org/docs/18/queries-table-expressions.html)
- [PostgreSQL 18：Error Codes](https://www.postgresql.org/docs/18/errcodes-appendix.html)
- [PostgreSQL 18：Transaction Isolation](https://www.postgresql.org/docs/18/transaction-iso.html)

---

[上一节：模式与 DDL 候选规则](../03/) · [返回本章目录](../) · [下一节：交付物与质量门](../05/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)