# 输入、输出与确定性数据

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

---

可复现实验需要确定的输入，也需要能被另一工具重新读取的输出。这里先解决小型数据交换和教学夹具；大规模装载、在线迁移、外部表和生产数据管道会在各自章节展开。

## 2.4.1 `COPY` 与 `\copy` 的权限和执行边界 {#item-2-4-1}

`COPY` 是 PostgreSQL SQL 命令，`\copy` 是 `psql` 元命令。两者可以传输相同数据格式，但文件由哪台机器、哪个操作系统用户读写完全不同。

| 写法 | 文件所在位置 | 文件访问身份 | 数据通道 | 典型用途 |
|---|---|---|---|---|
| `COPY ... TO '/path/file'` | 数据库服务器 | PostgreSQL 服务进程用户 | 服务端直接访问文件 | 受控服务器侧批量作业 |
| `COPY ... TO STDOUT` | 无固定文件 | 客户端接收 | PostgreSQL 连接 | 应用或工具流式处理 |
| `\copy ... TO 'file'` | `psql` 客户端 | 当前 Linux 用户 | 客户端发起 `COPY ... STDOUT` | 开发机导入导出、小型迁移 |

服务端文件版 `COPY` 需要超级用户，或 `pg_read_server_files`、`pg_write_server_files`、`pg_execute_server_program` 等高权限角色；路径从数据库服务器视角解析。不要为了方便给应用角色授予这些权限，它们可能读写数据库服务账号可访问的任意文件。

`\copy` 不需要服务端文件角色，因为 `psql` 自己打开本地文件，再通过标准输入/输出传输。导出本章夹具：

```bash
mkdir -p evidence/ch02
psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 <<'PSQL'
\copy (SELECT fixture_id, sku, label, amount, payload FROM shop.ch02_fixture ORDER BY fixture_id) TO 'evidence/ch02/fixture.csv' WITH (FORMAT csv, HEADER true, NULL '\N')
PSQL
```

这里的相对路径属于运行 `psql` 的客户端当前目录，不是 L1 数据库节点的 `$PGDATA`。`\copy` 对整行参数采用自己的解析规则，命令必须写在一条逻辑行内，也不进行普通 `psql` 变量替换；动态文件路径更适合由受控 Shell 生成完整命令，且必须正确处理空格与引号。

服务器侧 `COPY PROGRAM` 会以 PostgreSQL 服务账号启动命令。即使当前角色有权使用，也不能把不可信输入拼进命令字符串；Shell 元字符可能升级成服务器命令执行。第 2 章不使用它。

长时间 COPY 的进度可以从另一会话观察：

```sql
SELECT
    pid,
    datname,
    relid::regclass AS relation,
    command,
    type,
    bytes_processed,
    tuples_processed,
    tuples_excluded
FROM pg_catalog.pg_stat_progress_copy
ORDER BY pid;
```

视图中的计数是运行中证据，不替代完成后的行数、边界值和业务校验。

## 2.4.2 CSV、文本与错误隔离 {#item-2-4-2}

PostgreSQL `COPY` 支持 text、CSV 和 binary。选择标准不是“哪个最快”：

| 格式 | 优势 | 风险与限制 |
|---|---|---|
| text | PostgreSQL 原生、转义明确、适合工具链 | 不是普通 TSV；反斜杠与 `\N` 有专门语义 |
| CSV | 易与表格工具和其他系统交换 | CSV 是约定族；换行、引号、编码、NULL 与空串需明确 |
| binary | 类型保真、解析开销较低 | 类型和版本耦合更强，不适合作为长期可读交换格式 |

CSV 默认用未加引号的空字段表示 `NULL`，用 `""` 表示空字符串。这两个业务含义不同。实验显式写 `NULL '\N'`，让证据文件更容易肉眼审查；导入时必须使用同一约定。

### 默认策略：一错即停

```text
\copy shop.ch02_fixture FROM 'fixture.csv'
  WITH (FORMAT csv, HEADER true, NULL '\N')
```

默认 `ON_ERROR stop`。任一输入转换错误会使整条 `COPY` 失败；若外层事务也失败，目标状态可以保持不变。错误发生前处理过的行虽然不可见，却可能暂时占用表空间，后续由 vacuum 回收，因此“大文件试错”仍应先在隔离 staging 中演练。

### PostgreSQL 18 的受限容错导入

基线版本支持：

```sql
COPY shop.import_stage
FROM STDIN
WITH (
    FORMAT csv,
    HEADER true,
    ON_ERROR ignore,
    REJECT_LIMIT 3,
    LOG_VERBOSITY verbose
);
```

- `ON_ERROR ignore` 只忽略 text/CSV 输入转换错误，不是“忽略所有约束和触发器错误”；
- `REJECT_LIMIT 3` 表示第 4 个转换错误使命令失败；
- `LOG_VERBOSITY verbose` 为被丢弃行输出更详细的 NOTICE；
- 若不设置 reject limit，`ignore` 可能跳过任意数量错误，形成“任务成功、数据大面积消失”的假象。

`ON_ERROR` 在 PostgreSQL 17 引入，`REJECT_LIMIT` 属于 PostgreSQL 18 能力。面向 14–16 的可移植方案不是删掉验收，而是先导入全 `text` staging 表，再用显式验证查询区分：

1. 可转换且满足业务规则的行；
2. 原始内容与错误原因；
3. 无法识别或需要人工裁决的行。

最后在一个事务里把通过验证的数据转换进目标表。生产数据管道还要保存原始文件哈希、来源批次、拒绝行数量与处理决策。

### 一个错误隔离练习

在临时表中测试，不污染夹具：

```sql
CREATE TEMP TABLE amount_stage (
    source_line bigint GENERATED ALWAYS AS IDENTITY,
    sku text,
    amount_text text
);

INSERT INTO amount_stage (sku, amount_text)
VALUES
    ('SKU-0001', '1.23'),
    ('SKU-0002', 'not-a-number'),
    ('SKU-0003', '-4.00');

SELECT
    source_line,
    sku,
    amount_text,
    CASE
      WHEN amount_text ~ '^[0-9]+(\.[0-9]{1,2})?$'
      THEN amount_text::numeric(10,2)
    END AS parsed_amount,
    CASE
      WHEN amount_text !~ '^[0-9]+(\.[0-9]{1,2})?$'
      THEN 'invalid non-negative decimal'
    END AS rejection_reason
FROM amount_stage
ORDER BY source_line;
```

正则这里只服务受控教学格式，不是国际化金额解析器。重要的是保留原值与拒绝理由，再决定是否导入，而不是让 `NULL` 悄悄代表所有错误。

## 2.4.3 固定随机种子、规模档位与校验和 {#item-2-4-3}

“重新生成 100 行”还不够；行内容、顺序和摘要也必须可解释。本章夹具不用真正随机数，而是从行号计算：

```sql
SELECT
    n AS fixture_id,
    'SKU-' || lpad(n::text, 4, '0') AS sku,
    'fixture-' || substr(md5('label:' || n), 1, 12) AS label,
    (((n * 37) % 10000)::numeric / 100)::numeric(10,2) AS amount,
    md5('pg36:' || n) AS payload
FROM generate_series(1, 100) AS g(n)
ORDER BY n;
```

相同 PostgreSQL 语义下，输入行号唯一决定输出。它比调用 `random()` 后希望种子“差不多一样”更容易审查。

### 随机种子固定什么

PostgreSQL 会话可以：

```sql
SELECT setseed(0.36);
SELECT random()
FROM generate_series(1, 5);
```

同一会话重新设置相同种子，会重启伪随机序列。`pgbench` 也支持 `--random-seed=20260729`。但种子只约束随机数流，不会固定：

- 多线程或多客户端的调度顺序；
- 并发事务的交错与锁等待；
- 缓存命中、CPU 频率、网络和后台任务；
- 不同主要版本对未承诺实现细节的变化；
- 没有显式 `ORDER BY` 的结果顺序。

因此，小型语义夹具优先用可计算哈希；需要随机分布时记录生成器、种子、线程数、版本和规模档位。

### 规模档位要有名字

本书后续使用：

| 档位 | 目的 | 是否允许性能外推 |
|---|---|---|
| `tiny` | 快速验证语法与状态机 | 否 |
| `small` | L1 完整功能实验 | 否 |
| `medium` | L2 观察计划、维护和容量趋势 | 只解释方法 |
| `benchmark` | ch26 明确硬件与噪声后的正式运行 | 仅在记录的边界内 |

本章 100 行是 `tiny`。它只让错误注入、COPY、dump 和 pgbench 快速完成。

### 校验和必须先定义序列化

`verify.sql` 采用：

```sql
SELECT md5(
         string_agg(
           fixture_id || '|' || sku || '|' || payload,
           E'\n'
           ORDER BY fixture_id
         )
       ) AS checksum
FROM shop.ch02_fixture;
```

在 PostgreSQL 18.6 实测期望值是：

```text
00ed4599a6ed75e4441f5211909480fa
```

显式字段、分隔符与排序共同定义了序列化。若字段可以含 `|` 或换行，就要采用长度前缀、JSON、binary 或其他无歧义编码。对大表也不应把全部内容聚合成一个内存字符串；应按稳定键分块或使用面向数据管道的校验工具。

校验和证明“按这套序列化得到相同字节”，不证明业务正确。验收同时保留：

- 行数 `100`；
- 最小/最大 ID 为 `1`/`100`；
- 每行能由确定公式重新计算；
- 校验和匹配。

### 本节验收

- 能解释服务器文件 `COPY` 与客户端 `\copy` 的路径和权限差异；
- CSV 中 `NULL` 与空字符串有明确约定；
- 容错导入保存拒绝数量与原因，不静默跳过无限错误；
- 能说明 `ON_ERROR`/`REJECT_LIMIT` 的版本边界；
- 夹具重建后的行数、边界、逐行公式和校验和全部一致。

## 参考资料

- [PostgreSQL 18：COPY](https://www.postgresql.org/docs/18/sql-copy.html)
- [PostgreSQL 18：COPY 进度](https://www.postgresql.org/docs/18/progress-reporting.html#COPY-PROGRESS-REPORTING)
- [PostgreSQL 18：随机函数](https://www.postgresql.org/docs/18/functions-math.html#FUNCTIONS-MATH-RANDOM-TABLE)
- [PostgreSQL 18：字符串聚合](https://www.postgresql.org/docs/18/functions-aggregate.html)

---

[上一节：编写可靠 SQL 脚本](../03/) · [返回本章目录](../) · [下一节：最小 pgbench 工作负载](../05/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)