JavaLYG

PostgreSQL 19 导出 JSON 时报 Extra data:用 FORCE_ARRAY 处理多条记录

PostgreSQL 19 可以用 COPY TO 直接导出 JSON。把默认输出交给 json.load(),两条记录就可能报 Extra data。改成逐行读之后,如果查询里还有格式化存储的 json 列,一条记录也可能跨越多行。下面用真实的 19.0 隔离实例复现两种失败,并验证何时该启用 FORCE_ARRAY。

先核对服务器版本和交付格式

PostgreSQL 19 于 2026 年 11 月 19 日发布,发行说明列出了 COPY TO 的 JSON 输出及 FORCE_ARRAY 选项。[1][3] 面对升级前后的混合环境,先在实际连接上执行 SHOW server_version_num。只查看 psql --version 得到的是客户端版本。客户端装了新版、后端仍连到旧实例时,换文件扩展名或改读取代码都影响不了服务端支持的语法。

接着明确消费方需要的是单份 JSON 文档,还是每行一个对象。给既有系统替换导出命令时,可以先取一份协议样本,然后测试无记录、一条记录和多条记录。单条默认输出本身恰好是一个合法对象,这会让只用一条数据的验收通过。到了下一批,两条独立对象连续出现,错误才暴露出来。

PostgreSQL 19 COPY TO JSON 默认逐条输出,FORCE_ARRAY 输出单个数组
同一查询的两种输出。默认逐条输出,含格式化 json 列时,一条记录可以跨多个物理行。启用 FORCE_ARRAY 后整份输出可按 JSON 数组读取。

同一条查询,两种输出形状

对同一条 SELECT,COPY (查询) TO STDOUT (FORMAT JSON) 导出逐条对象。加上 FORCE_ARRAY 后,外层方括号与行之间的逗号由 PostgreSQL 负责,整份结果是一份 JSON 数组。[2] 测试中,两条普通记录经逐行解析后得到两个对象。强制数组得到内容和次序相同的列表。脚本对结果排序,是为了让比较可重复。生产导出需要稳定顺序时,在查询里写出清晰的 ORDER BY,存在同键时再补一个能打破并列的字段。

零条数据也会影响消费方。隔离实验中,默认模式输出零字节。数组模式输出含换行的空数组,用常规 JSON 解析器读到 []。如果下游要求根节点是数组,用 FORCE_ARRAY 能让空批次与非空批次遵循同一个契约。验收时比较解析后的类型与记录数,避免把服务端输出的缩进、换行或逗号位置固定成接口规则。

一条记录能占好几行,先看列类型

先对比两种肉眼容易混淆的换行。在实测样本里,普通 text 列含有换行,导出时它在 JSON 字符串里变成转义序列,仍能按物理行分出记录。同样的查询如果返回带缩进和换行的 json 对象,内部空白会原样出现在导出结果里。对整条对象做 JSON 解析可以成功,只把第一行喂给解析器就会失败。即便记录数为一,行数也会大于一。

PostgreSQL 的 json 类型保存原文本,jsonb 采用分解后的内部表示。文档说明转换后不会保留原有空白、对象键顺序和重复键。[4] 如果消费端确实要求每行一个对象,且业务允许这些变化,可在导出查询对相关列试做 列名::jsonb,再用代表性的样本逐行检查。本文的格式化对象通过了这项对照。对需要原有键次序、重复键或文本形态的列,应在选格式时把这些属性当成契约的一部分。选数组时,也要明确下游解析器可能按自己的规则处理重复键。

把完整实验跑一遍

先准备可执行 Docker 的环境以及 Python 3.7 或更新版本。先运行 docker pull public.ecr.aws/docker/library/postgres:19-alpine,随后将下方代码保存为 copy_json_demo.py 并运行 python3 copy_json_demo.py。脚本使用本地镜像,在隔离容器中启动 PostgreSQL,关闭 TCP 监听,仅通过容器内的 Unix socket 访问临时库。样本保存在临时文件系统,退出时由脚本停止并清理容器。

import json
import subprocess
import time

镜像 = 'public.ecr.aws/docker/library/postgres:19-alpine'
隔离启动 = (
    'initdb -D /tmp/pgdata -U postgres -A trust --no-locale -E UTF8 >/dev/null; '
    'exec postgres -D /tmp/pgdata -c listen_addresses= -k /tmp'
)


def 执行(*参数):
    return subprocess.run(参数, capture_output=True, timeout=20)


def 校验(条件, 名称):
    if not 条件:
        raise RuntimeError(名称)
    print(名称)


def 逐行(数据):
    return [json.loads(行) for 行 in 数据.splitlines()]


启动 = 执行(
    'docker', 'run', '-d', '--rm', '--pull=never', '--network', 'none',
    '--user', 'postgres', '--cap-drop', 'ALL', '--security-opt',
    'no-new-privileges', '--read-only', '--tmpfs', '/var/lib/postgresql:rw',
    '--tmpfs', '/tmp:rw', '--entrypoint', 'sh', 镜像, '-ec', 隔离启动,
)
启动.check_returncode()
容器 = 启动.stdout.decode('ascii').strip()


def 运行SQL(语句):
    return 执行('docker', 'exec', 容器, 'psql', '-X', '-h', '/tmp',
                '-U', 'postgres', '-d', 'postgres', '-qAt',
                '-v', 'ON_ERROR_STOP=1', '-c', 语句)


def 查询(语句):
    结果 = 运行SQL(语句)
    if 结果.returncode:
        raise RuntimeError(结果.stderr.decode('utf-8', errors='replace'))
    return 结果.stdout.decode('utf-8')


def 导出(查询语句, 选项='FORMAT JSON'):
    # 查询和选项仅使用本实验固定表达式。
    return 查询(f'COPY ({查询语句}) TO STDOUT ({选项})')


try:
    截止 = time.monotonic() + 25
    while 执行('docker', 'exec', 容器, 'pg_isready', '-h', '/tmp',
               '-U', 'postgres').returncode:
        if time.monotonic() >= 截止:
            raise RuntimeError('隔离服务器未就绪')
        time.sleep(0.25)
    校验(190000 <= int(查询('SHOW server_version_num')) < 200000, '01 PostgreSQL 19')
    行集 = "SELECT i AS id, E'甲\\n乙' AS note FROM (VALUES(2),(1)) AS t(i) ORDER BY i"
    预期 = [{'id': 1, 'note': '甲\n乙'}, {'id': 2, 'note': '甲\n乙'}]
    默认 = 导出(行集)
    校验(逐行(默认) == 预期 and len(默认.splitlines()) == 2, '02 文本换行与导出顺序')
    try:
        json.loads(默认)
    except json.JSONDecodeError as 错误:
        校验(错误.msg == 'Extra data', '03 两条对象整读报 Extra data')
    else:
        raise RuntimeError('两条对象意外当成单个文档')
    数组选项 = 'FORMAT JSON, FORCE_ARRAY'
    校验(json.loads(导出(行集, 数组选项)) == 预期, '04 数组内容与顺序')
    单行 = 'SELECT 1 AS id'
    校验(json.loads(导出(单行)) == {'id': 1} and
         json.loads(导出(单行, 数组选项)) == [{'id': 1}], '05 单条对象与单元素数组')
    空查询 = 'SELECT 1 AS id WHERE false'
    校验(导出(空查询) == '' and json.loads(导出(空查询, 数组选项)) == [], '06 空输出与空数组')

    多行列 = r'''SELECT E'{\n"a":1\n}'::json AS data'''
    原样 = 导出(多行列)
    校验(json.loads(原样) == {'data': {'a': 1}} and len(原样.splitlines()) > 1,
         '07 json 列保留物理换行')
    try:
        逐行(原样)
    except json.JSONDecodeError:
        print('08 多行 json 无法按物理行解析')
    else:
        raise RuntimeError('多行 json 意外通过逐行解析')
    校验(逐行(导出(多行列.replace('::json', '::jsonb'))) == [{'data': {'a': 1}}],
         '09 jsonb 对照可逐行解析')
    校验(json.loads(导出(多行列, 数组选项)) == [{'data': {'a': 1}}], '10 多行 json 可按数组解析')
    空值句 = "SELECT j AS data, j IS NULL AS sql_null FROM (VALUES (1,NULL::json),(2,'null'::json)) t(i,j) ORDER BY i"
    校验(逐行(导出(空值句)) == [{'data': None, 'sql_null': True},
                              {'data': None, 'sql_null': False}], '11 显式保留 SQL NULL 判据')
    错误 = 运行SQL('BEGIN; CREATE TEMP TABLE t(i int); COPY t FROM STDIN (FORMAT JSON); ROLLBACK;')
    校验(错误.returncode != 0 and b'json' in 错误.stderr.lower(), '12 COPY FROM JSON 被拒绝')
finally:
    执行('docker', 'stop', '-t', '2', 容器).check_returncode()

实验保留了三个可直接观察的边界。两条默认记录整读时返回 Extra data。单条默认记录能整读,根节点是对象。带内部换行的 json 对象可以出现在 FORCE_ARRAY 的数组里,并且数组能整体解析;在这组输入上,逐行的读取法会在尚未读全一条对象时就失败。

导出的 null 能告诉你多少

SQL NULL 和存进 json 或 jsonb 列的 JSON 字面量 null,在导出字段里都会显示为 null。[2] 如果这两个状态在业务上含义不同,例如一条记录还没有填写数据,另一条则明确提交了 JSON 空值,可以在查询里同时投影 data IS NULL AS sql_null。随文实验对两行都实际检查了这个布尔字段,并确认字段值本身完全一样。

此外,本文用的是 TO STDOUT,由客户端接收字节。若把目标写成文件名,文件由数据库服务器进程在服务器视角下写出,所需权限和落点也随之变化。[2] 导入端要单独设计字段映射、类型和错误策略。PostgreSQL 19 的 FORMAT JSON 适用于 COPY TO,同样的格式选项放到 COPY FROM 会被拒绝。[2]

交付时也可以把查询的输出字段名固定下来。特别是多表连接,两边各带一个同名字段时,建议用明确的别名区分账户编号与订单编号,让下游可以按字段名直接核对。把这些别名和空值判据列入接口约定,下次调整查询时就能用同一批样本比较字段是否发生变化。

按现象缩小排查范围

观察到的症状第一项验证下一步
两条记录整读出现 Extra data用两条已知记录确认根节点契约单文档接口启用 FORCE_ARRAY
逐行消费在某些记录上失败核对投影列是否含多行 json 原文改用数组,或验证允许转 jsonb 的列
空批次当作坏文件检查零条查询的实际输出与下游期望为数组接口验收空数组
两个空值状态合在一起确定业务是否区分 SQL NULL 和 JSON null查询同时输出 IS NULL 判据
目标环境不识别 FORMAT JSON读实际服务端版本与原始错误在目标版本演练后选择受支持的导出方式

交付前核对三件事

先核对格式和数据。对实际投影列跑零条、一条、多条的样本,检查客户端退出状态、根类型、行数、关键字段和顺序。如果计划把 JSON 文件交给其他服务,再使用那个服务实际采用的解析器和字段规则跑一遍。示例使用固定 UTF-8 库、固定查询和小样本,处理大批量时,消费者还需要评估整份数组的内存开销以及逐记录处理时的失败策略。

再核对可交付的文件。客户端将标准输出重定向到文件时,目的地可能在命令执行之初就已经打开,一次中途失败可能留下不完整内容。需要给其他进程消费的批次,可以先落到独立的临时文件,成功后按约定核对记录数和关键字段,再发布到正式路径。最后把空批次与错误批次分别记录,保留原始数据库诊断。本文的十二项隔离检查覆盖导出格式和解析边界,文件替换、容器并发和生产吞吐应在对应环境里单独验收。

官方参考资料

  1. [1] PostgreSQL 19 发布公告
  2. [2] PostgreSQL 19 COPY 手册
  3. [3] PostgreSQL 19 发行说明
  4. [4] PostgreSQL 19 的 JSON 数据类型

🔕 评论已关闭