agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedpgsql: JSON_TABLE: propagate table-level ON ERROR to columns per SQL st
3+ messages / 3 participants
[nested] [flat]
* pgsql: JSON_TABLE: propagate table-level ON ERROR to columns per SQL st
@ 2026-09-19 14:37 Alexander Korotkov <akorotkov@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Alexander Korotkov @ 2026-09-19 14:37 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
JSON_TABLE: propagate table-level ON ERROR to columns per SQL standard
Per ISO/IEC 9075-2:2023, 7.11 <JSON table>, Syntax Rules 1)e)iv) and
1)f)xi), a regular or formatted JSON_TABLE column that does not specify
its own ON ERROR clause takes its default error behavior from the
table-level ON ERROR clause: with ERROR ON ERROR on the table, the
column behaves as ERROR ON ERROR; otherwise the column defaults to
NULL ON ERROR.
PostgreSQL instead always defaulted such columns to NULL ON ERROR, so
SELECT * FROM JSON_TABLE(jsonb '"err"', '$'
COLUMNS (a int PATH '$') ERROR ON ERROR) jt;
returned a NULL row where the standard requires an error. The
documentation stated the divergence as if it were a rule, saying that
the table-level clause "does not affect the errors that occur when
evaluating columns".
Implement the standard behavior in the JSON_TABLE syntactic
transformation: when a column lacks its own ON ERROR clause and the
table-level behavior is ERROR ON ERROR, synthesize an implicit ERROR ON
ERROR for the column before it is transformed into a JsonExpr. A column
with its own ON ERROR clause is unaffected. EXISTS columns are not
covered by those syntax rules, so they keep their FALSE ON ERROR
default. A JSON_TABLE stored in a view is now deparsed with the
previously implicit ERROR ON ERROR shown explicitly on the affected
columns, which is a semantically equivalent, round-trip-stable form.
86ab7f4c721d introduced this cascade as part of the PLAN clause and
af6fad879fbf reverted it, on the grounds that it should be a deliberate
and separately documented change rather than a side effect of an
unrelated feature. This is that change.
Do not back-patch. A query that specifies ERROR ON ERROR at the table
level and relies on its columns still yielding NULL now raises an error
instead of returning rows. Nothing is silently wrong in the released
branches: the table-level clause is ignored for columns consistently,
and that is what the documentation has promised since PostgreSQL 17, so
users could reasonably have depended on it.
Discussion: https://postgr.es/m/CAPpHfdtDXseGhrL14a2asOkwGnFTVrDk8SQc3iWZgWEEhXXMGw%40mail.gmail.com
Reviewed-by: Nikita Malakhov <hukutoc@gmail.com>
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/e73841ffbceea314cf9fa3f64a5eae9f87a46449
Modified Files
--------------
doc/src/sgml/func/func-json.sgml | 16 ++++++++++----
src/backend/parser/parse_jsontable.c | 29 ++++++++++++++++++++-----
src/test/regress/expected/sqljson_jsontable.out | 28 ++++++++++++++++++------
src/test/regress/sql/sqljson_jsontable.sql | 17 ++++++++++-----
4 files changed, 68 insertions(+), 22 deletions(-)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: JSON_TABLE: propagate table-level ON ERROR to columns per SQL st
@ 2026-09-23 18:20 Robert Haas <robertmhaas@gmail.com>
parent: Alexander Korotkov <akorotkov@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Haas @ 2026-09-23 18:20 UTC (permalink / raw)
To: Alexander Korotkov <akorotkov@postgresql.org>; +Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
On Sat, Sep 19, 2026 at 10:37 AM Alexander Korotkov
<akorotkov@postgresql.org> wrote:
> JSON_TABLE: propagate table-level ON ERROR to columns per SQL standard
This commit has introduced a dump/restore problem. Consider the
following test case:
CREATE VIEW v AS SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS
(a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt;
SELECT * FROM v;
This returns a single-row, single-column result, containing null.
But if you use pg_get_viewdef(), you see that the NULL ON ERROR has vanished:
rhaas=# select * from pg_get_viewdef('v');
pg_get_viewdef
------------------------------------------------------
SELECT a +
FROM JSON_TABLE( +
'"err"'::jsonb, '$' AS json_table_path_0+
COLUMNS ( +
a integer PATH '$' +
) ERROR ON ERROR +
);
(1 row)
And the result of that omission is that if you dump and restore such a
database, the behavior of the view changes as compared to the
original:
$ createdb restore
$ pg_dump | psql restore
$ psql restore
restore=# select * from v;
ERROR: invalid input syntax for type integer: "err"
--
Robert Haas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: JSON_TABLE: propagate table-level ON ERROR to columns per SQL st
@ 2026-09-24 15:48 Alexander Korotkov <aekorotkov@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Alexander Korotkov @ 2026-09-24 15:48 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Alexander Korotkov <akorotkov@postgresql.org>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
On Wed, Sep 23, 2026 at 9:21 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Sat, Sep 19, 2026 at 10:37 AM Alexander Korotkov
> <akorotkov@postgresql.org> wrote:
> > JSON_TABLE: propagate table-level ON ERROR to columns per SQL standard
>
> This commit has introduced a dump/restore problem. Consider the
> following test case:
>
> CREATE VIEW v AS SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS
> (a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt;
> SELECT * FROM v;
>
> This returns a single-row, single-column result, containing null.
>
> But if you use pg_get_viewdef(), you see that the NULL ON ERROR has vanished:
>
> rhaas=# select * from pg_get_viewdef('v');
> pg_get_viewdef
> ------------------------------------------------------
> SELECT a +
> FROM JSON_TABLE( +
> '"err"'::jsonb, '$' AS json_table_path_0+
> COLUMNS ( +
> a integer PATH '$' +
> ) ERROR ON ERROR +
> );
> (1 row)
>
> And the result of that omission is that if you dump and restore such a
> database, the behavior of the view changes as compared to the
> original:
>
> $ createdb restore
> $ pg_dump | psql restore
> $ psql restore
> restore=# select * from v;
> ERROR: invalid input syntax for type integer: "err"
Thank you for your report. Yep, this requires more detailed
consideration. Reverted for now.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-24 15:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-19 14:37 pgsql: JSON_TABLE: propagate table-level ON ERROR to columns per SQL st Alexander Korotkov <akorotkov@postgresql.org>
2026-09-23 18:20 ` Robert Haas <robertmhaas@gmail.com>
2026-09-24 15:48 ` Alexander Korotkov <aekorotkov@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox