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

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

2.4.1 COPY 与 \copy 的权限和执行边界

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

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

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

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 的进度可以从另一会话观察：

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、文本与错误隔离

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

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

默认策略：一错即停

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

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

PostgreSQL 18 的受限容错导入

基线版本支持：

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. 无法识别或需要人工裁决的行。

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

一个错误隔离练习

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

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 固定随机种子、规模档位与校验和

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

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 会话可以：

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

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

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

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

规模档位要有名字

本书后续使用：

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

校验和必须先定义序列化

verify.sql 采用：

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

在 PostgreSQL 18.4 实测期望值是：

00ed4599a6ed75e4441f5211909480fa

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

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

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

本节验收

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

参考资料

• PostgreSQL 18：COPY
• PostgreSQL 18：COPY 进度
• PostgreSQL 18：随机函数
• PostgreSQL 18：字符串聚合

上一节：编写可靠 SQL 脚本 · 返回本章目录 · 下一节：最小 pgbench 工作负载 · 查看全书目录 · 查看索引中心