返回资讯中心
外部精选
软件工程
#开发者实时流

实测 PostgreSQL 16 至 18 触发 JSON 类型输入语法错误的情况

来源标题:Chris van Eijk: invalid input syntax for type JSON in PostgreSQL, measured

PostgreSQL 在将非合法 JSON 文本转换为 json 或 jsonb 时会报 22P02 错误,作者在 PostgreSQL 16.15、17.11 和 18.6 上测试了 38 个字符串。三个版本的报错信息、DETAIL 和 SQLSTATE 完全一致,仅 LOCATION 下的源码行不同。PostgreSQL 18 中有一个变通方法发生了变化。

Hacker News 最新omegaSigma952026/11/17 · 今天30 分钟阅读

以下正文由 AI 翻译自 Hacker News 最新(原文版权归原站所有),可能存在偏差,重要内容请以原文为准。

把文本转成 json 或 jsonb 时报出 ERROR: invalid input syntax for type json,意思是 PostgreSQL 拿到了一段要转成 json 或 jsonb 的文本,而这段文本不是该类型能接受的 JSON。即使列是 jsonb 类型,消息里写的也是 type json;它的 SQLSTATE 是 22P02(invalid_text_representation),DETAIL 会指出解析器第一个无法使用的 token,例如 Token "'" is invalid。cast、带参数的 insert 或 ALTER 都不会告诉你出问题的表、列或行;只有 COPY 会把它们加进 CONTEXT。

我们把 38 个字符串分别喂给 PostgreSQL 16.15、17.11 和 18.6 上的 ::json 与 ::jsonb,又通过 node-postgres 和 psycopg 发送 JavaScript 与 Python 的值,还把一张含坏行的 text 列迁移成 jsonb——在一张九行的表和一张一百万行的表上各做了一遍。就 cast 而言,三个版本上的每条消息、DETAIL 和 SQLSTATE 都相同;只有 LOCATION 下面的源码行不同。有一个 workaround 在 PostgreSQL 18 里确实变了,我们在下文会讲到。

失败的大多是从某个并非 JSON 序列化器的地方来的、长得像 JSON 的文本:Python 对 dict 调用 str() 的结果、JavaScript 的 NaN 或 undefined、PostgreSQL 的数组字面量、多余的尾逗号。下面这些结果取自 pg_input_error_info,与真实 cast 得到的结果一致:

SQL NULL 转成这两种类型中的任何一种都得到 NULL,而 JSON 文本 null 得到 JSON null,两者都不报错。

主消息对 ::jsonb 同样是 invalid input syntax for type json,所以搜“invalid input syntax for type jsonb”找不到 PostgreSQL 实际写出的任何消息。表中唯一的例外是 jsonb 里的 \u0000:SQLSTATE 不同,是 22P05(untranslatable_character),消息也不同,为 unsupported Unicode escape sequence。捕获 22P02 的代码捕获不到它。也捕获不到超出 numeric 范围的数字:{"a": 1e1000000} 是合法的 json,而作为 jsonb 它给出 22003,value overflows numeric format。这两种错误码与其他错误码的对照,见我们对 SQLSTATE 错误码的概览。

CONTEXT 显示的是解析器停在哪里,而不是值从哪里来。对一个 28,914 字符的输入、错误在末尾的情况,它读作 JSON data, line 1: ...94, 4995, 4996, 4997, 4998, 4999, 5000], "bad": x....。在我们试过的三个长输入里,首尾两个点之间的部分都是 49 字节,并终止于出错的 token:49 个 ASCII 字符,但如果全是 é 就只有 30 个。错误之后的内容一概不显示。line N 统计换行:一个格式化打印的文档第三行出错,报的是 line 3。

通过驱动发送时会多一行,unnamed portal parameter $1 = '...',而值并不在里面。这是默认的 log_parameter_max_length_on_error = 0 所致:在会话里执行 set log_parameter_max_length_on_error = -1 之后,同样的错误会显示 $1 = '{"1","2"}',与我们在 uuid 那次测量中看到的一样。

这是因为 node-postgres 在还不知道值要发往哪里之前,就先把 JavaScript 数组转成了 PostgreSQL 数组字面量。它的文档对一般参数就是这么写的,“Converted to a string that describes a Postgres array”,并说对象会走 JSON.stringify。讲查询的那一页和讲类型的那一页都没有提醒说这对 json 或 jsonb 列同样适用。用 pg 8.23.1 对 PostgreSQL 18.6,[1, 2] 发出去是 {"1","2"},insert 失败,报 22P02 和 DETAIL: Expected ":", but found ",".:解析器把花括号读成了对象的开头。对象数组和嵌套数组也同样以 22P02 失败。json 列、显式的 $1::jsonb,以及把数组原样传给 pg 的 Kysely 0.29.8,结果都一样。

空数组更糟,因为它能成功。[] 发出去是 {},这是合法 JSON,于是被存成空对象,json 列和 jsonb 列都是如此,且不报错。把值读回来、以为它是数组的代码拿到的是 {}。对象里的空数组不受影响:{a: []} 走 JSON.stringify,被存成 {"a": []}。

在 json 和 jsonb 两种列上各用 17 个值做了测量;两边每个分支的结果都相同,只有一个含 \u0000 的字符串例外,jsonb 列以 22P05 拒绝了它。一个 Date 发出去是 2026-11-12T00:00:00.000+00:00,失败于 Expected end of input, but found "-11"。

修法是自行序列化数组:JSON.stringify([1, 2]) 被存成数组 [1,2],两种列类型、经由 Kysely 都是如此。对对象来说 node-postgres 本来就这么做,所以图里那些静默变化——NaN 变成 null、值为 undefined 的键被丢弃——都是 JSON.stringify 造成的,无论你用哪种方式发送都会发生。

这取决于列表里装的是什么。用 psycopg 3.3.8 且不加包装时,[1, 2] 被拒绝,报 42804,column "jb" is of type jsonb but expression is of type smallint[],这个错误我们在 column is of type jsonb but expression is of type text 一文里拆解过。['a', 'b'] 报 22P02,Token "a" is invalid。而 [] 被存成 {},一个空对象,不报错,与 node-postgres 一样。同一个类型有三种结果。psycopg 的 dumper 把 [] 变成无类型(OID 0)的文本 {},把 ['a', 'b'] 变成无类型的 {a,b},把 [1, 2] 变成类型为 smallint[] 的 {1,2};三种结果全由此而来。我们是从 dumper 读出来的,没有抓取 wire 数据。

所以要把值包进 Jsonb(...),json 列则用 Json(...),绝不要用 str() 去拼文本。包装并不能让你躲开 NaN:值里的 float nan 仍然以 NaN 到达 PostgreSQL。包装走的是 Python 的 json.dumps,它默认会写出 NaN;dumper 产出的是 {"x": NaN}。

因为 jsonb 检查得更多。文档说 json 只“为语法正确性”检查 Unicode 转义,而 jsonb“拒绝 \u0000(因为它无法在 PostgreSQL 的 text 类型中表示)”,并要求代理对正确。我们看到的正是如此:"\u0000" 和 "\ud800" 是合法的 json、非法的 jsonb。

(原文较长,此处译出前 6000 字,完整内容请点「打开原文」。)

正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。

打开原文
去社区讨论 →