# 为服务设计查询接口

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

---

服务中的 SQL 不是藏在字符串里的实现细节，而是一组版本化接口。好的 query contract 让评审者不看 Go 也能回答：

```text
input type and bound
row cardinality
ordering
null and no-row semantics
locking and transaction requirement
expected SQLSTATE
result shape across schema versions
```

这一节使用 [store.go](/labs/ch12/service/store.go) 中实际运行过的 SQL，不另外发明一套“正文专用”伪代码。

## 12.2.1 参数化 SQL 与稳定结果语义 {#item-12-2-1}

### 绑定参数只解决 value

pgx 使用 `$1`、`$2`：

```sql
SELECT state, total_minor, currency_code
FROM shop_ch12.sales_order
WHERE order_id = $1
FOR UPDATE;
```

参数化的主要价值：

- value 不再与 SQL grammar 拼接；
- 类型编码由 driver/protocol 处理；
- query text 稳定，便于 query identity 与计划复用；
- 日志可以记录 query identity，而不必记录敏感 value；
- 测试可以把恶意输入当数据，不会改变语法。

但参数不能代替 table、column、direction 或 operator：

```sql
-- 不成立：$1 不会被当作列名
ORDER BY $1;
```

动态 identifier 需要：

1. 尽量改成几条固定 SQL；
2. 若确实需要，输入先映射到封闭 enum；
3. 使用 driver 提供的 identifier quoting；
4. value 仍然单独参数化。

不要把用户字符串传入 `fmt.Sprintf("ORDER BY %s", input)`，再声称其他 value 已参数化所以安全。

### 把原子决策放进语句结果

库存预留：

```sql
UPDATE shop_ch12.inventory
SET available = available - $2,
    version = version + 1
WHERE sku = $1
  AND available >= $2
RETURNING
    unit_price_minor,
    currency_code,
    available;
```

这条 SQL 的 contract 包含：

```text
input:
  sku text matching API vocabulary
  quantity int32 in 1..1000

success:
  exactly one row
  price/currency are the values used for this order
  stock and version changed atomically

zero rows:
  SKU absent or quantity unavailable

constraint:
  available remains >= 0 for every writer
```

`RETURNING` 避免一次 UPDATE 后再读“可能已经被别人改过”的当前值。本章不把剩余库存放进响应，因此代码只用 price/currency 完成订单；但证据保留 final inventory。

### 显式列优于 `SELECT *`

`SELECT *` 会把 schema 顺序变成 query contract。新增列后：

- positional scanner 可能列数不符；
- result description cache 可能失效；
- API 无意暴露新字段；
- 大字段可能突然进入热路径；
- 同名列 join 后难以辨认；
- rolling deployment 的 old decoder 可能失败。

服务查询逐列列出：

```sql
SELECT
    orders.order_id,
    orders.customer_ref,
    orders.state,
    orders.total_minor,
    orders.currency_code,
    orders.trace_id,
    orders.created_at,
    items.value,
    payment.value
...
```

“显式”不表示永不改变；它让改变发生在可 review 的 query diff，而不是 table diff 的隐式副作用。

### 稳定 JSON 必须定义内部顺序

聚合 items：

```sql
SELECT COALESCE(
    jsonb_agg(
        jsonb_build_object(
            'line_no', line.line_no,
            'sku', line.sku,
            'quantity', line.quantity,
            'unit_price_minor', line.unit_price_minor,
            'line_total_minor', line.line_total_minor
        )
        ORDER BY line.line_no
    ),
    '[]'::jsonb
)
FROM shop_ch12.sales_order_item AS line
WHERE line.order_id = orders.order_id;
```

没有 aggregate 内部的 `ORDER BY`，上层查询排序不能保证数组元素顺序。空集合用 `[]`，不是 SQL NULL；payment 没有行则返回 JSON `null`。这些都是 API contract，不是格式喜好。

不要用 JSON 文本字节逐字符比较 `jsonb` object key order。稳定语义是字段和值；array order 才由 `ORDER BY` 明确定义。

### Keyset pagination

第一页：

```text
GET /v1/orders?limit=1
→ item 1200001
→ next_cursor=1200001
```

下一页：

```text
GET /v1/orders?limit=1&after=1200001
→ WHERE order_id > 1200001
→ item 1200002
→ next_cursor=null
```

核心 predicate：

```sql
WHERE orders.order_id > $1
ORDER BY orders.order_id
LIMIT $2;
```

相比高 OFFSET，keyset 不必反复扫描并丢弃前 N 行，也更能抵抗前页插入/删除造成的位置漂移。但它要求：

- order key 唯一或追加唯一 tie-breaker；
- cursor 包含完整 sort key；
- filter、sort 与 cursor semantics 绑定版本；
- 向后翻页需要单独设计；
- snapshot 一致性若是需求，不能仅靠 cursor。

### 参数类型与 query mode 也属于合同

本章固定 pgx `QueryExecModeExec`。它使用 extended protocol、text-formatted 参数与结果，并在一个 round trip 执行；它不会像默认 `cache_statement` 那样自动缓存 named prepared statement。

这带来一个容易遗漏的类型边界：在该模式中，Go `[]byte` 会自然表示 PostgreSQL `bytea`；JSON/JSONB 参数应传 string、注册类型或实现相应 codec。本章持久化 response 时使用：

```go
string(payload)
```

而不是假设任意字节都会被数据库自动理解为 JSON。参数化解决 injection，不替你解决不明确的类型映射。

## 12.2.2 CTE、窗口函数和 `LATERAL` 的工程用法 {#item-12-2-2}

这些构造不是“高级 SQL 展示”。它们分别解决：

```text
CTE:       name a query stage and stabilize one statement's shape
window:    compute across related rows without collapsing them
LATERAL:   evaluate a right-side subquery using the current left row
```

### `LATERAL` 生成每个订单的嵌套结果

订单详情先取得一个 order，再为这一行计算 items：

```sql
FROM shop_ch12.sales_order AS orders
CROSS JOIN LATERAL (
    SELECT COALESCE(
        jsonb_agg(... ORDER BY line.line_no),
        '[]'::jsonb
    ) AS value
    FROM shop_ch12.sales_order_item AS line
    WHERE line.order_id = orders.order_id
) AS items
```

`LATERAL` 允许右侧引用 `orders.order_id`。这里 aggregate 即使没有 item 也返回一行，所以 `CROSS JOIN` 不会丢掉 order。另一种常见形态：

```sql
LEFT JOIN LATERAL (
    SELECT ...
    WHERE child.parent_id = parent.id
    ORDER BY ...
    LIMIT 1
) AS latest ON true
```

适合“每个 parent 的 top-N/latest”。风险是外层行很多时，右侧可能反复执行；仍要用 `EXPLAIN (ANALYZE, BUFFERS)` 检查实际 loops、index 与行数，不能因 SQL 简洁就假设代价小。

### CTE 表达分页阶段

本章列表查询：

```sql
WITH page AS (
    SELECT
        orders.order_id,
        orders.state,
        orders.total_minor,
        orders.created_at
    FROM shop_ch12.sales_order AS orders
    WHERE orders.order_id > $1
    ORDER BY orders.order_id
    LIMIT $2
),
ranked AS (
    SELECT
        page.*,
        row_number() OVER (
            ORDER BY page.order_id
        ) AS page_position
    FROM page
)
SELECT ...
FROM ranked
CROSS JOIN LATERAL (...)
ORDER BY ranked.order_id;
```

阶段关系清楚：

```text
page:
  use keyset + limit to bound parent rows

ranked:
  number only the bounded page

final:
  build nested items only for selected parents
```

如果先 join/aggregate 所有 items，再 LIMIT parent，会做无谓工作，甚至把 LIMIT 作用到 join rows 而不是 orders。

CTE 不是永久 materialized temp table。PostgreSQL 会根据引用次数、side effect 与 `MATERIALIZED` / `NOT MATERIALIZED` 选择折叠边界。需要性能结论时看计划；不要拿“CTE 一定是优化屏障”这种旧经验当跨版本规则。

### Window 不改变行基数

`row_number()`：

```sql
row_number() OVER (ORDER BY page.order_id)
```

给 page 内每个 order 编号，但不把多行聚合成一行。窗口函数逻辑上在 `WHERE/GROUP BY/HAVING` 后执行，所以不能直接写：

```sql
WHERE row_number() OVER (...) <= 10;
```

需要再包一层 subquery/CTE 后过滤。

本例的 `page_position` 是响应可解释性，不是全表序号。第一页和下一页都会从 1 开始；若 API 要“全局第几条”，那会引入全局扫描、并发变化与成本合同，不能偷换。

### CTE 不是拆事务

一个 data-modifying CTE 可以在单条 statement 里组合多个写，但：

- 所有子语句仍是同一 statement snapshot；
- 执行顺序不是普通过程语言；
- `RETURNING` 是各阶段传值方式；
- error 会回滚整个 statement；
- 多 statement transaction 仍适合需要条件分支、错误映射与重复请求读取的流程。

本章订单流程用显式 transaction，而不是把所有逻辑压进一条巨大 CTE。选择标准是可验证的 atomicity 与清晰失败语义，不是 SQL 行数最少。

## 12.2.3 错误码、约束名与领域错误映射 {#item-12-2-3}

### 先保留原始身份

服务内部 error 至少保存：

```text
domain code
HTTP status
retryable flag
SQLSTATE when present
constraint name when present
trace_id
cause for internal log/tracing
```

外部响应：

```json
{
  "error": {
    "code": "database_timeout",
    "message": "database statement exceeded its time budget",
    "retryable": true,
    "trace_id": "trace-timeout-001"
  }
}
```

不返回 raw SQL、connection string、table internals 或 PostgreSQL DETAIL。内部结构化日志保留：

```json
{
  "msg": "request_error",
  "error_code": "database_timeout",
  "status": 504,
  "retryable": true,
  "trace_id": "trace-timeout-001",
  "sqlstate": "57014"
}
```

### 一个建议映射表

| 条件 | HTTP/领域 | 默认 retryable | 备注 |
|---|---|---:|---|
| invalid JSON/value | 400 invalid_* | false | 在 DB 前拒绝 |
| missing row | 404 *_not_found | false | 只对明确 no-row |
| same key/different fingerprint | 409 idempotency_conflict | false | 客户端必须换 payload/key |
| insufficient inventory | 409 insufficient_inventory | false | 业务竞争，不是 DB 故障 |
| amount mismatch | 422 amount_mismatch | false | 语义可解析但不满足合同 |
| `23505` | 409 unique_conflict | usually false | 最好按 constraint 细分 |
| `23503` / `23514` | 422 database_constraint | false | 不暴露内部 message |
| `40001` / `40P01` after budget | 503 transaction_retry_exhausted | true | 中间尝试不返回给 client |
| `57014` from DB timeout | 504 database_timeout | conditional | 要结合幂等性 |
| pool acquire deadline | 503 pool_unavailable | true | SQL 尚未执行 |
| client context canceled | 499 internal log | n/a | 客户端通常已离开 |
| `42501` | 500 database_privilege | false | deployment defect |

`retryable=true` 不是“任意客户端立刻重放”。它只表示协议允许在同一 idempotency contract 下重试；客户端仍要有 deadline、backoff、attempt budget。

### 同一 SQLSTATE 需要上下文

`57014` 的 symbolic condition 是 `query_canceled`。来源可以是：

- `statement_timeout`；
- client cancel request；
- operator `pg_cancel_backend()`；
- driver context cancellation。

本章故障矩阵分别注入：

```text
SET LOCAL statement_timeout='50ms'
SELECT pg_sleep(0.2)
→ PostgreSQL 57014
→ service 504 database_timeout

HTTP client times out while pg_sleep
→ request context canceled
→ driver cancels DB work
→ service log client_cancelled/499
→ active worker reaches zero
```

只看到 SQLSTATE 57014 时不要武断写“数据库慢”。需要同时看 application cancellation cause、timeout 配置、database log 与 request timeline。

### Constraint name 是可版本化 API

如果服务要把某个 `23514` 细分为 `invalid_state_transition`，约束名就成为 error contract：

```text
ch12_sales_order_state_check
```

重命名、拆分或合并约束都可能改变映射。发布时应：

- 所有重要约束显式命名；
- 映射 unknown constraint 到安全通用错误；
- 在 app/schema coexistence 期接受 old/new 名称；
- 测试 SQLSTATE + name，不测试英文 message；
- 记录 PostgreSQL version difference。

### 不要吞掉未知错误

最危险的映射：

```go
if err != nil {
    return notFound
}
```

它会把权限失败、连接断开、取消、decode bug 和 schema drift 全伪装成业务缺失。正确的默认分支应：

```text
return controlled 500
preserve trace and internal cause
increment error metric
do not expose raw detail
page/operator alert if it represents contract drift
```

未知错误不是“用户体验问题”，而是你发现合同不完整的信号。

## 本节检查表

- [ ] value 使用 `$n`，identifier 来自封闭白名单；
- [ ] query 显式列出结果，不依赖 `SELECT *`；
- [ ] row cardinality、no-row 与 null 已定义；
- [ ] array aggregate 内部有 `ORDER BY`；
- [ ] pagination 有唯一完整 sort key；
- [ ] CTE 各阶段有明确基数，性能结论来自计划；
- [ ] `LATERAL` loops 与索引在真实规模评估；
- [ ] window 的 partition/order/frame 语义明确；
- [ ] query mode 与 Go/PostgreSQL 类型映射已测试；
- [ ] SQLSTATE/constraint identity 在 error 中保留；
- [ ] 外部错误不泄露 SQL、凭据或敏感参数；
- [ ] retryable 只在幂等和预算条件下成立；
- [ ] unknown DB error 不被错误映射成 404/409。

## 参考资料

- [PostgreSQL 18：LATERAL Subqueries](https://www.postgresql.org/docs/18/queries-table-expressions.html#QUERIES-LATERAL)
- [PostgreSQL 18：WITH Queries](https://www.postgresql.org/docs/18/queries-with.html)
- [PostgreSQL 18：Window Functions](https://www.postgresql.org/docs/18/functions-window.html)
- [PostgreSQL 18：Error Codes](https://www.postgresql.org/docs/18/errcodes-appendix.html)
- [pgx v5.10.0：QueryExecMode](https://pkg.go.dev/github.com/jackc/pgx/v5@v5.10.0#QueryExecMode)

---

[上一节：数据库契约与应用边界](../01/) · [返回本章目录](../) · [下一节：Go 服务中的连接与事务](../03/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)