| From: | Henson Choi <assam258(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | Amit Langote <amitlangote09(at)gmail(dot)com> |
| Subject: | [DEPARSE] A JSON DEFAULT cast to a length-limited RETURNING type truncates after restore |
| Date: | 2026-11-17 04:57:13 |
| Message-ID: | CAAAe_zCzfbt9DLWV8mA505asMzZuFzzQ8JW7ic9DiHcURTDSdg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
When the DEFAULT expression of ON EMPTY or ON ERROR in JSON_VALUE,
JSON_QUERY or a JSON_TABLE column is coerced to a RETURNING type with
a length, the parser makes the cast in assignment context, so that a
value that is too long raises an error. But it marks the cast as
explicit, so ruleutils.c prints it, and read back it is an explicit
cast, which truncates the value instead.
The problem
-----------
create view v as
select json_value(jsonb '{}', '$.a' returning varchar(3)
default lower('ABCDEF') on empty) as r;
select * from v;
ERROR: value too long for type character varying(3)
before: ... DEFAULT (lower('ABCDEF'::text))::character varying(3)
ON EMPTY
recreated from pg_get_viewdef(), the view returns abc
after: ... DEFAULT lower('ABCDEF'::text) ON EMPTY
recreated the same way, the view raises the same error
pg_dump writes out the same text, so a restored dump does the same.
A domain over such a type behaves the same way.
The fix
-------
74c96699be3 made this an assignment cast for string and bit string
types, as had been done for the RETURNING clause of the constructor
functions. But where the constructor functions mark the cast as
implicit, it marks it as explicit, also for types with a length, which
until then had been marked implicit. The fix marks it as implicit
again, for every DEFAULT cast built there. The coercion context does
not change, so an expression parsed now behaves as before, and when
the text is read back the RETURNING clause makes the same cast again.
The mark is stored with the expression, so views, SQL-standard
function bodies and other stored expressions created before the fix
keep printing the cast. They still raise the error themselves, but
pg_dump and pg_upgrade write the cast out, and the restored objects
then truncate, even on a fixed server. Only creating them again from
their original SQL fixes them.
Tests
-----
The tests add views to sqljson_queryfuncs. One with varchar(3) and
one with a domain over it are created again from pg_get_viewdef(). A
third has a cast written by the user, which must stay, and its
definition is shown unchanged. They come first with the output before
the fix, so the fix patch shows how the output changes.
Back branches
-------------
I think this should be back-patched to 17, as 74c96699be3 was; these
functions do not exist before 17. The patches apply to 19, 18 and 17
as they are, and the regression tests pass on all of them.
Best regards,
Henson
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Add-tests-for-length-limited-JSON-behavior-DEFAUL.patch | application/octet-stream | 5.8 KB |
| v1-0002-Mark-the-cast-of-a-JSON-behavior-DEFAULT-to-RETUR.patch | application/octet-stream | 4.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Lukas Fittl | 2026-11-17 05:02:09 | Re: Improve pg_stat_statements scalability |
| Previous Message | Fujii Masao | 2026-11-17 04:41:36 | Re: Assertion failure with temporary relations in single-user mode |