JSON_TABLE: table => column ON ERROR propagation
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253338psql -h localhost -U postgresBuilt from patchset v4 (message #4), September 17, 2026 at 02:23 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253338_4 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253338_4 && git checkout t253338_4Patchset v4 (message #4) is on t253338_4
Hi!
While working on the JSON_TABLE PLAN clause [1], I found that our
implementation of JSON_TABLE has a standard divergence. In our
implementation, the table-level ON ERROR clause and the column-level
ON ERROR clause are completely independent. But according to ISO/IEC
9075-2 2023, the table-level ON ERROR clause is propagated to the
column level if the column doesn't have its own explicit ON ERROR
clause.
I can't share my copy of the standard, but I verified that Chapter
7.11 Syntax Rules 1) e) iv) and 1) f) xi) specify that if a column
element lacks an ON ERROR clause and the table-level clause is ERROR
ON ERROR, then ERROR ON ERROR applies to the column. Otherwise (no
table-level clause or table-level clause is NULL ON ERROR), NULL ON
ERROR applies to the column.
For instance, this query returns NULL on the current master, but must
error out according to the standard.
SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS (a int PATH '$')
ERROR ON ERROR) jt;
I've drafted a patch that implements the behavior specified in the
standard. That's not a very complicated patch, but I see a problem:
JSON_TABLE was released in PostgreSQL 17, and this patch changes
user-visible behavior.
I see some options we can go ahead with:
1) Backpatch this to PostgreSQL 17, and issue release notes that users
should check and revise their queries.
2) Patch only master, and issue the same notes for the PostgreSQL 20 release.
3) Add the GUC controlling this behavior (I don't really like that).
Any thoughts?
Links.
1. /messages/by-id/CAPpHfdt=LncQH9PAq9O8qO7KZcTT9rOsxLLanscRF7xDFvK8mA@mail.gmail.com
------
Regards,
Alexander Korotkov
Supabase
Hi!
Alexander, IMHO the 2nd option is the best. To revise queries for 2 versions
could involve a lot of work, testing and system behavior changes.
On Thu, Aug 6, 2026 at 7:01 PM Alexander Korotkov <aekorotkov@gmail.com>
wrote:
Hi!
While working on the JSON_TABLE PLAN clause [1], I found that our
implementation of JSON_TABLE has a standard divergence. In our
implementation, the table-level ON ERROR clause and the column-level
ON ERROR clause are completely independent. But according to ISO/IEC
9075-2 2023, the table-level ON ERROR clause is propagated to the
column level if the column doesn't have its own explicit ON ERROR
clause.I can't share my copy of the standard, but I verified that Chapter
7.11 Syntax Rules 1) e) iv) and 1) f) xi) specify that if a column
element lacks an ON ERROR clause and the table-level clause is ERROR
ON ERROR, then ERROR ON ERROR applies to the column. Otherwise (no
table-level clause or table-level clause is NULL ON ERROR), NULL ON
ERROR applies to the column.For instance, this query returns NULL on the current master, but must
error out according to the standard.SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS (a int PATH '$')
ERROR ON ERROR) jt;I've drafted a patch that implements the behavior specified in the
standard. That's not a very complicated patch, but I see a problem:
JSON_TABLE was released in PostgreSQL 17, and this patch changes
user-visible behavior.I see some options we can go ahead with:
1) Backpatch this to PostgreSQL 17, and issue release notes that users
should check and revise their queries.
2) Patch only master, and issue the same notes for the PostgreSQL 20
release.
3) Add the GUC controlling this behavior (I don't really like that).Any thoughts?
Links.
1.
/messages/by-id/CAPpHfdt=LncQH9PAq9O8qO7KZcTT9rOsxLLanscRF7xDFvK8mA@mail.gmail.com------
Regards,
Alexander Korotkov
Supabase
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
Hi, Nikita!
On Fri, Aug 7, 2026 at 8:00 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
Alexander, IMHO the 2nd option is the best. To revise queries for 2 versions
could involve a lot of work, testing and system behavior changes.
Thank you for your feedback. Any other opinions?
------
Regards,
Alexander Korotkov
Supabase
On Wed, Aug 12, 2026 at 2:44 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
On Fri, Aug 7, 2026 at 8:00 AM Nikita Malakhov <hukutoc@gmail.com> wrote:
Alexander, IMHO the 2nd option is the best. To revise queries for 2 versions
could involve a lot of work, testing and system behavior changes.Thank you for your feedback. Any other opinions?
Hearing nothing back I suppose we should go with option 2: master
only, release note for v20.
Reasons, with the precedents I checked:
* Nothing is silently wrong in 17-19: the table-level clause is
ignored for columns consistently, and the result is the NULL an
explicit NULL ON ERROR would give. Compare 4c75cc78630, which we did
back-patch into 17 long after GA: there RETURNING numeric(4,1) DEFAULT
99999.999 returned a value the type itself rejects, and it could be
stored into a numeric(4,1) column. We back-patch when the old
behavior yields results no correct query could want, not when it is
just non-conforming.
* We documented the current behavior in 17 -- "this clause does not
affect the errors that occur when evaluating columns". We are
retracting a promise, not fixing an undocumented accident.
* 9321d2fdf80 is the closest structural precedent that went to a next
major release only. It changed foreign key collation handling after
the standard's own rule turned out to be wrong. The new rule rejects
schemas that used to be valid, and that is exactly why it was put to
v18: users meet it at a major upgrade, where reading the notes and
adjusting is part of the job. Ours is milder, and fits the same
placement.
* Our change is quite small: only queries with a table-level ERROR ON
ERROR that rely on columns still returning NULL. Without it, or with
explicit per-column clauses, nothing changes.
v2 attached; code unchanged from v1, the commit message records the
above. Suggested release note: a JSON_TABLE column without its own ON
ERROR clause now inherits ERROR ON ERROR from the table-level clause;
add an explicit NULL ON ERROR to keep the old behavior.
I'm going to push this to master if no objections.
------
Regards,
Alexander Korotkov
Supabase