[PATCH] Fix JSON_SERIALIZE() coercion placeholder type for jsonb input
Hi folks,
Related to:
/messages/by-id/03583FE7-A5EE-407C-907D-67865F3F8BFC@gmail.com
When JSON_SERIALIZE() receives a jsonb-typed argument, the CaseTestExpr
placeholder used to set up coercion was unconditionally assigned JSONOID
(derived from the RETURNING format, which defaults to JS_FORMAT_JSON).
However, the executor passes the input argument value through directly
for JSON_SERIALIZE (see ExecInitExprRec in execExpr.c), so the actual
datum at runtime is jsonb, not json. This type mismatch between the
placeholder and the runtime value caused the wrong coercion path to be
selected.
As Dirkjan mentioned, this results in confusing output.
Attached is a patch to address this, by deriving the placeholder type from
the
actual argument type via exprType(linitial(args)) when the constructor type
is
JSCTOR_JSON_SERIALIZE, rather than from returning->format->format_type.
Added a regression test to ensure JSONB is serialized correctly.
Cheers,
Matt
Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
Hi Zsolt,
Thanks for testing that out and confirming it looks good against master.
Submitted to commitfest for further review and integration consideration.
Matt
On Fri, Mar 13, 2026 at 11:20 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:
Show quoted text
Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
Hi Matt,
I reviewed the patch and tested it against current master.
I built the patch with assertions enabled and ran the full core regression
suite. All 245 tests passed. I also tested:
- typed jsonb parameters in prepared statements
- default text, varchar, bytea, and string-domain results
- NULL jsonb input
- use inside a view and subsequent deparsing
These all behaved as expected. I did not find any security, WAL/recovery,
logical-replication, or performance concerns introduced by the patch.
I have one comment:
Regression tests
To me, some of the additional coverage seems redundant:
- The RETURNING int and RETURNING jsonb cases are rejected before
reaching the changed code.
- The RETURNING jsonb error is already tested immediately above with a
non-jsonb input.
- The new EXPLAIN cases verify deparsing but do not reveal which
coercion function was selected.
So, I think the test addition could be reduced to one default text case and
one RETURNING bytea case.
Otherwise, the implementation looks correct. It's simpler than converting
every jsonb input to json every time.
Regards,
Nikhil
On Tue, Mar 17, 2026 at 12:18 AM Matt Blewitt <mble@planetscale.com> wrote:
Show quoted text
Hi Zsolt,
Thanks for testing that out and confirming it looks good against master.
Submitted to commitfest for further review and integration consideration.Matt
On Fri, Mar 13, 2026 at 11:20 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
Hi Nikhil,
Thanks - I've attached v2 with the revised test cases; there are no
functional changes from v1.
Cheers,
Matt
On Wed, Aug 26, 2026 at 9:07 AM Nikhil Sontakke <nikkhils@gmail.com> wrote:
Show quoted text
Hi Matt,
I reviewed the patch and tested it against current master.
I built the patch with assertions enabled and ran the full core regression
suite. All 245 tests passed. I also tested:- typed jsonb parameters in prepared statements
- default text, varchar, bytea, and string-domain results
- NULL jsonb input
- use inside a view and subsequent deparsingThese all behaved as expected. I did not find any security, WAL/recovery,
logical-replication, or performance concerns introduced by the patch.I have one comment:
Regression tests
To me, some of the additional coverage seems redundant:- The RETURNING int and RETURNING jsonb cases are rejected before
reaching the changed code.
- The RETURNING jsonb error is already tested immediately above with a
non-jsonb input.
- The new EXPLAIN cases verify deparsing but do not reveal which
coercion function was selected.So, I think the test addition could be reduced to one default text case
and one RETURNING bytea case.Otherwise, the implementation looks correct. It's simpler than converting
every jsonb input to json every time.Regards,
Nikhil
On Tue, Mar 17, 2026 at 12:18 AM Matt Blewitt <mble@planetscale.com>
wrote:Hi Zsolt,
Thanks for testing that out and confirming it looks good against master.
Submitted to commitfest for further review and integration consideration.Matt
On Fri, Mar 13, 2026 at 11:20 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
Hi Matt,
Thanks for updating the patch. The simplified tests now cover the default
text and bytea coercion paths,
and the patch passes the full regression suite. Maybe the code can be
tightened further like the example below:
@@ -3743,8 +3742,8 @@ makeJsonConstructorExpr(ParseState *pstate,
JsonConstructorType type,
if (type == JSCTOR_JSON_SERIALIZE)
{
- Assert(args != NIL);
- cte->typeId = exprType(linitial(args));
+ Assert(list_length(args) == 1);
+ cte->typeId = exprType((Node *) linitial(args));
}
else
Also, I think, we can expand the tests to consider the implicit typmod
conversion case as well:
+-- Test implicit typmod coercion with jsonb input
+SELECT JSON_SERIALIZE('{ "a" : 1 } '::jsonb RETURNING varchar(2));
If no backpatch is intended (which I think is the case here),
then any concerns about previously stored expression trees does not apply
for this patch.
Regards,
Nikhil
On Wed, Aug 26, 2026 at 5:44 PM Matt Blewitt <mble@planetscale.com> wrote:
Show quoted text
Hi Nikhil,
Thanks - I've attached v2 with the revised test cases; there are no
functional changes from v1.Cheers,
Matt
On Wed, Aug 26, 2026 at 9:07 AM Nikhil Sontakke <nikkhils@gmail.com>
wrote:Hi Matt,
I reviewed the patch and tested it against current master.
I built the patch with assertions enabled and ran the full core
regression suite. All 245 tests passed. I also tested:- typed jsonb parameters in prepared statements
- default text, varchar, bytea, and string-domain results
- NULL jsonb input
- use inside a view and subsequent deparsingThese all behaved as expected. I did not find any security, WAL/recovery,
logical-replication, or performance concerns introduced by the patch.I have one comment:
Regression tests
To me, some of the additional coverage seems redundant:- The RETURNING int and RETURNING jsonb cases are rejected before
reaching the changed code.
- The RETURNING jsonb error is already tested immediately above with
a non-jsonb input.
- The new EXPLAIN cases verify deparsing but do not reveal which
coercion function was selected.So, I think the test addition could be reduced to one default text case
and one RETURNING bytea case.Otherwise, the implementation looks correct. It's simpler than converting
every jsonb input to json every time.Regards,
Nikhil
On Tue, Mar 17, 2026 at 12:18 AM Matt Blewitt <mble@planetscale.com>
wrote:Hi Zsolt,
Thanks for testing that out and confirming it looks good against master.
Submitted to commitfest for further review and integration consideration.Matt
On Fri, Mar 13, 2026 at 11:20 PM Zsolt Parragi <
zsolt.parragi@percona.com> wrote:Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
Hi Nikhil,
Thanks for the follow-up review. Attached is v3 with the stricter argument
assertion, explicit Node * cast, and implicit typmod-coercion test. I also
rebased it onto current master and ran the full core regression suite with
assertions enabled; all 243 tests passed.
Cheers,
Matt
On Thu, Aug 27, 2026 at 10:45 AM Nikhil Sontakke <nikkhils@gmail.com> wrote:
Show quoted text
Hi Matt,
Thanks for updating the patch. The simplified tests now cover the default
text and bytea coercion paths,
and the patch passes the full regression suite. Maybe the code can be
tightened further like the example below:@@ -3743,8 +3742,8 @@ makeJsonConstructorExpr(ParseState *pstate,
JsonConstructorType type,if (type == JSCTOR_JSON_SERIALIZE) { - Assert(args != NIL); - cte->typeId = exprType(linitial(args)); + Assert(list_length(args) == 1); + cte->typeId = exprType((Node *) linitial(args)); } elseAlso, I think, we can expand the tests to consider the implicit typmod
conversion case as well:+-- Test implicit typmod coercion with jsonb input +SELECT JSON_SERIALIZE('{ "a" : 1 } '::jsonb RETURNING varchar(2));If no backpatch is intended (which I think is the case here),
then any concerns about previously stored expression trees does not apply
for this patch.Regards,
NikhilOn Wed, Aug 26, 2026 at 5:44 PM Matt Blewitt <mble@planetscale.com> wrote:
Hi Nikhil,
Thanks - I've attached v2 with the revised test cases; there are no
functional changes from v1.Cheers,
Matt
On Wed, Aug 26, 2026 at 9:07 AM Nikhil Sontakke <nikkhils@gmail.com>
wrote:Hi Matt,
I reviewed the patch and tested it against current master.
I built the patch with assertions enabled and ran the full core
regression suite. All 245 tests passed. I also tested:- typed jsonb parameters in prepared statements
- default text, varchar, bytea, and string-domain results
- NULL jsonb input
- use inside a view and subsequent deparsingThese all behaved as expected. I did not find any security,
WAL/recovery, logical-replication, or performance concerns introduced by
the patch.I have one comment:
Regression tests
To me, some of the additional coverage seems redundant:- The RETURNING int and RETURNING jsonb cases are rejected before
reaching the changed code.
- The RETURNING jsonb error is already tested immediately above with
a non-jsonb input.
- The new EXPLAIN cases verify deparsing but do not reveal which
coercion function was selected.So, I think the test addition could be reduced to one default text case
and one RETURNING bytea case.Otherwise, the implementation looks correct. It's simpler than
converting every jsonb input to json every time.Regards,
Nikhil
On Tue, Mar 17, 2026 at 12:18 AM Matt Blewitt <mble@planetscale.com>
wrote:Hi Zsolt,
Thanks for testing that out and confirming it looks good against
master. Submitted to commitfest for further review and integration
consideration.Matt
On Fri, Mar 13, 2026 at 11:20 PM Zsolt Parragi <
zsolt.parragi@percona.com> wrote:Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
Thanks Matt,
It all LGTM now. I think this patch can now be marked as "Ready For
Committer".
Regards,
Nikhil
On Thu, Aug 27, 2026 at 6:50 PM Matt Blewitt <mble@planetscale.com> wrote:
Show quoted text
Hi Nikhil,
Thanks for the follow-up review. Attached is v3 with the stricter argument
assertion, explicit Node * cast, and implicit typmod-coercion test. I also
rebased it onto current master and ran the full core regression suite with
assertions enabled; all 243 tests passed.Cheers,
Matt
On Thu, Aug 27, 2026 at 10:45 AM Nikhil Sontakke <nikkhils@gmail.com>
wrote:Hi Matt,
Thanks for updating the patch. The simplified tests now cover the default
text and bytea coercion paths,
and the patch passes the full regression suite. Maybe the code can be
tightened further like the example below:@@ -3743,8 +3742,8 @@ makeJsonConstructorExpr(ParseState *pstate,
JsonConstructorType type,if (type == JSCTOR_JSON_SERIALIZE) { - Assert(args != NIL); - cte->typeId = exprType(linitial(args)); + Assert(list_length(args) == 1); + cte->typeId = exprType((Node *) linitial(args)); } elseAlso, I think, we can expand the tests to consider the implicit typmod
conversion case as well:+-- Test implicit typmod coercion with jsonb input +SELECT JSON_SERIALIZE('{ "a" : 1 } '::jsonb RETURNING varchar(2));If no backpatch is intended (which I think is the case here),
then any concerns about previously stored expression trees does not apply
for this patch.Regards,
NikhilOn Wed, Aug 26, 2026 at 5:44 PM Matt Blewitt <mble@planetscale.com>
wrote:Hi Nikhil,
Thanks - I've attached v2 with the revised test cases; there are no
functional changes from v1.Cheers,
Matt
On Wed, Aug 26, 2026 at 9:07 AM Nikhil Sontakke <nikkhils@gmail.com>
wrote:Hi Matt,
I reviewed the patch and tested it against current master.
I built the patch with assertions enabled and ran the full core
regression suite. All 245 tests passed. I also tested:- typed jsonb parameters in prepared statements
- default text, varchar, bytea, and string-domain results
- NULL jsonb input
- use inside a view and subsequent deparsingThese all behaved as expected. I did not find any security,
WAL/recovery, logical-replication, or performance concerns introduced by
the patch.I have one comment:
Regression tests
To me, some of the additional coverage seems redundant:- The RETURNING int and RETURNING jsonb cases are rejected before
reaching the changed code.
- The RETURNING jsonb error is already tested immediately above
with a non-jsonb input.
- The new EXPLAIN cases verify deparsing but do not reveal which
coercion function was selected.So, I think the test addition could be reduced to one default text case
and one RETURNING bytea case.Otherwise, the implementation looks correct. It's simpler than
converting every jsonb input to json every time.Regards,
Nikhil
On Tue, Mar 17, 2026 at 12:18 AM Matt Blewitt <mble@planetscale.com>
wrote:Hi Zsolt,
Thanks for testing that out and confirming it looks good against
master. Submitted to commitfest for further review and integration
consideration.Matt
On Fri, Mar 13, 2026 at 11:20 PM Zsolt Parragi <
zsolt.parragi@percona.com> wrote:Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
Hi,
One more consequence of this bug that I think matters for the
back-patching question. Nikhil wrote upthread that no back-patch is
intended. The bug doesn't just produce a confusing result: the result is
a text value whose bytes are taken from the jsonb header, so it can be
invalidly encoded, and it can be stored. In a UTF8 database, on 18.6:
CREATE TABLE t (s text);
INSERT INTO t SELECT json_serialize(jsonb_agg(g))
FROM generate_series(1, 200) g;
SELECT encode(s::bytea, 'hex') FROM t; -- c8
SELECT convert_to(s, 'UTF8') FROM t;
ERROR: invalid byte sequence for encoding "UTF8": 0xc8
pg_dump of such a table succeeds, but restoring the dump fails:
ERROR: invalid byte sequence for encoding "UTF8": 0xc8 0x0a
and the table ends up empty. With v3 applied to master, the same INSERT stores the correct
892-byte text. Given that, I think the fix should be back-patched to 17,
where JSON_SERIALIZE() was added (REL_17_STABLE has the same code).
Also, in the original pgsql-bugs thread, Shihao Zhong has since posted
another patch for the same bug:
/messages/by-id/03583FE7-A5EE-407C-907D-67865F3F8BFC@gmail.com
so it's probably worth closing both together.
--
Ilya Staroverov
Hi,
I tested the v3 patch for the JSON_SERIALIZE() issue with jsonb input.
Before applying the patch, I was able to reproduce the issue, where
UTF-8 conversion failed due to invalid byte sequences. After applying
the patch, the output was correct, and the UTF-8 conversion succeeded.
I also ran the regression tests, and all 245 tests passed. The build
completed successfully, and git diff --check reported no issues.
Please let me know if any further testing is needed.
Regards,
solai
Show quoted text
On Mon, Oct 26, 2026 at 5:32 PM Nikhil Sontakke <nikkhils@gmail.com> wrote:
Thanks Matt,
It all LGTM now. I think this patch can now be marked as "Ready For Committer".
Regards,
NikhilOn Thu, Aug 27, 2026 at 6:50 PM Matt Blewitt <mble@planetscale.com> wrote:
Hi Nikhil,
Thanks for the follow-up review. Attached is v3 with the stricter argument assertion, explicit Node * cast, and implicit typmod-coercion test. I also rebased it onto current master and ran the full core regression suite with assertions enabled; all 243 tests passed.
Cheers,
Matt
On Thu, Aug 27, 2026 at 10:45 AM Nikhil Sontakke <nikkhils@gmail.com> wrote:
Hi Matt,
Thanks for updating the patch. The simplified tests now cover the default text and bytea coercion paths,
and the patch passes the full regression suite. Maybe the code can be tightened further like the example below:@@ -3743,8 +3742,8 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type,
if (type == JSCTOR_JSON_SERIALIZE) { - Assert(args != NIL); - cte->typeId = exprType(linitial(args)); + Assert(list_length(args) == 1); + cte->typeId = exprType((Node *) linitial(args)); } elseAlso, I think, we can expand the tests to consider the implicit typmod conversion case as well:
+-- Test implicit typmod coercion with jsonb input +SELECT JSON_SERIALIZE('{ "a" : 1 } '::jsonb RETURNING varchar(2));If no backpatch is intended (which I think is the case here),
then any concerns about previously stored expression trees does not apply for this patch.Regards,
NikhilOn Wed, Aug 26, 2026 at 5:44 PM Matt Blewitt <mble@planetscale.com> wrote:
Hi Nikhil,
Thanks - I've attached v2 with the revised test cases; there are no functional changes from v1.
Cheers,
Matt
On Wed, Aug 26, 2026 at 9:07 AM Nikhil Sontakke <nikkhils@gmail.com> wrote:
Hi Matt,
I reviewed the patch and tested it against current master.
I built the patch with assertions enabled and ran the full core regression suite. All 245 tests passed. I also tested:
typed jsonb parameters in prepared statements
default text, varchar, bytea, and string-domain results
NULL jsonb input
use inside a view and subsequent deparsingThese all behaved as expected. I did not find any security, WAL/recovery, logical-replication, or performance concerns introduced by the patch.
I have one comment:
Regression tests
To me, some of the additional coverage seems redundant:The RETURNING int and RETURNING jsonb cases are rejected before reaching the changed code.
The RETURNING jsonb error is already tested immediately above with a non-jsonb input.
The new EXPLAIN cases verify deparsing but do not reveal which coercion function was selected.So, I think the test addition could be reduced to one default text case and one RETURNING bytea case.
Otherwise, the implementation looks correct. It's simpler than converting every jsonb input to json every time.
Regards,
Nikhil
On Tue, Mar 17, 2026 at 12:18 AM Matt Blewitt <mble@planetscale.com> wrote:
Hi Zsolt,
Thanks for testing that out and confirming it looks good against master. Submitted to commitfest for further review and integration consideration.
Matt
On Fri, Mar 13, 2026 at 11:20 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
Hello!
This is a simple fix and it does what it says, it looks good to me.
I did test it with a few more queries and compared it against master,
all looks good.
On Thu, 15 Oct 2026, Staroverov Ilja <i.staroverov@ftdata.ru> wrote:
Given that, I think the fix should be back-patched to 17,
and
On Thu, 27 Aug 2026, NikhilS <nikkhils@gmail.com> wrote:
If no backpatch is intended (which I think is the case here),
then any concerns about previously stored expression trees does not apply
for this patch.
Since this can affect both indexes and stored generated columns, I think the commit message should mention that this can 1. possibly require a reindex 2. doesn't fix existing stored generated columns affected by this issue. And the same concern is also true for backpatching it.
Hi,
On Thu, 15 Oct 2026, Staroverov Ilja <i.staroverov@ftdata.ru> wrote:
Given that, I think the fix should be back-patched to 17,
On Tue, 27 Oct 2026, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
Since this can affect both indexes and stored generated columns, I
think the commit message should mention that this can 1. possibly
require a reindex 2. doesn't fix existing stored generated columns
affected by this issue.
I had asked (with a question mark) about the need to backpatch. Ilya's example
shows that the wrong output gets stored and then breaks restore, so it indeed
makes sense to back-patch to 17.
I checked and v3 applies cleanly to REL_17_STABLE, REL_18_STABLE, REL_19_STABLE
and master, with no conflicts and an identical diff on every branch. With
assertions enabled, make check passes on all four.
I also tested what happens to objects created before the fix, using an
index, a stored generated column and a view, each on
json_serialize(jsonb_col). Since the fix is in the parser, the outcome
depends on how the cluster reaches a fixed release.
1. Minor update (data directory from stock 17.11 / 18.6 started under
the patched 17 / 18 binaries):
Only re-parsing fixes them: DROP/CREATE INDEX, CREATE OR REPLACE VIEW,
and ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION, which also
recomputes the stored values.
2. pg_upgrade from an affected release into a fixed one (17.11 -> 18,
18.6 -> 19, 18.6 -> master, 19beta3 -> master; the
target was patched each time, with identical results):
The expressions are re-parsed during the upgrade, so all three trees
come out correct and the view gives the right output immediately.
However, the index files are copied, so the index now holds the old
wrong values under a correct expression, and the planner uses it.
Stored generated column values also stay wrong (including
Ilya's invalid-UTF-8 0xc8 row, which pg_upgrade copies without
complaint).
So REINDEX alone is not enough after a minor update, and after
pg_upgrade it is required, since the index silently misses rows.
Attached is v4, which is v3 with only the commit message changed: it
adds the following back-patch note. The code is identical to v3, and
the same patch applies with git am to REL_17_STABLE, REL_18_STABLE,
REL_19_STABLE and master.
Back-patch to v17, where JSON_SERIALIZE() was introduced. The fix
applies at parse time, so views, index expressions and stored
generated columns that use JSON_SERIALIZE() on a jsonb argument and
were created before this fix keep the incorrect coercion; REINDEX
does not repair them. Recreate such views and indexes, and
re-specify such generated columns with ALTER TABLE ... ALTER COLUMN
... SET EXPRESSION. Clusters upgraded with pg_upgrade from an
affected release get corrected expressions, but affected indexes
must be reindexed, since until then they can return wrong results,
and stored generated column values must be recomputed.
The release notes will probably need the same text.
Regards,
Nikhil