12.2 为服务设计查询接口

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

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 中实际运行过的 SQL，不另外发明一套“正文专用”伪代码。

12.2.1 参数化 SQL 与稳定结果语义

绑定参数只解决 value

pgx 使用 $1、$2：

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：

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

动态 identifier 需要：

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

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

把原子决策放进语句结果

库存预留：

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 包含：

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 可能失败。

服务查询逐列列出：

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：

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

第一页：

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

下一页：

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

核心 predicate：

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 时使用：

string(payload)

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

12.2.2 CTE、窗口函数和 LATERAL 的工程用法

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

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：

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。另一种常见形态：

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 表达分页阶段

本章列表查询：

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;

阶段关系清楚：

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()：

row_number() OVER (ORDER BY page.order_id)

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

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 错误码、约束名与领域错误映射

先保留原始身份

服务内部 error 至少保存：

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

外部响应：

{
  "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。内部结构化日志保留：

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

一个建议映射表

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。

本章故障矩阵分别注入：

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：

ch12_sales_order_state_check

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

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

不要吞掉未知错误

最危险的映射：

if err != nil {
    return notFound
}

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

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
• PostgreSQL 18：WITH Queries
• PostgreSQL 18：Window Functions
• PostgreSQL 18：Error Codes
• pgx v5.10.0：QueryExecMode

上一节：数据库契约与应用边界 · 返回本章目录 · 下一节：Go 服务中的连接与事务 · 查看全书目录 · 查看索引中心