| From: | Tomas Vondra <tomas(at)vondra(dot)me> |
|---|---|
| To: | Keyerror Smart <smartkeyerror(at)gmail(dot)com>, Ethan Tran <ethantran(at)quwin(dot)dev> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: [PATCH]Fix planner's strictness check for JsonExpr |
| Date: | 2026-11-17 17:10:12 |
| Message-ID: | d7940635-2c66-4253-925a-968da66c7d31@vondra.me |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
Thanks for the fixes. I've accidentally tripped over this issue while
working on a nearby fix [1], dealing with pushdown to FULL JOIN, so
kinda in the opposite direction.
I've posted a more complex fix in that thread, which also inspects the
expressions in the PASSING/ON ERROR and ON/EMPTY clauses. Which does
allow treating the JsonExpr as strict in more cases (well, at least I
believe so). But when I tried to use your fix, it only really changed a
single query in the (fairly extensive) regression tests I added:
# Values Scan on "*VALUES*"
# - Output: JSON_QUERY("*VALUES*".column1, "*VALUES*".column2 PASSING
0 AS p RETURNING jsonb WITH UNCONDITIONAL WRAPPER KEEP QUOTES EMPTY
ARRAY ON EMPTY EMPTY OBJECT ON ERROR), JSON_EXISTS("*VALUES*".column1,
"*VALUES*".column2 PASSING 0 AS p TRUE ON ERROR)
# + Output: sqljson_query_strict("*VALUES*".column1,
"*VALUES*".column2), sqljson_exists_strict("*VALUES*".column1,
"*VALUES*".column2)
# (2 rows)
#
There are two such failures, but both are about blocking inlining of a
SQL function with JSON_QUERY/JSON_VALUE.
The one thing this v4 fix does not handle is RETURNING with
null-rejecting domains, which is violating strictness (as it turns NULL
into an ERROR). But once I added this:
if (jsexpr->use_json_coercion &&
get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN)
return true;
it disappeared. The CoerceToDomain is an independent issue, but it does
overlap with the JsonExpr in this detail. So maybe this fix should do
this bit too.
There's probably ways to refine these checks. I guess the simpler the
check the better for backpatching. I wonder if this might cause trouble
for users by disabling optimization in cases when it'd be OK. But then
again, better slower than incorrect. I don't have a very good intuition
how common these optimizations are, though.
regards
[1]
https://www.postgresql.org/message-id/b368a3ca-b196-4c28-8050-50320e9c955d%40vondra.me
--
Tomas Vondra
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Sami Imseih | 2026-11-17 17:16:50 | Re: Improve pg_stat_statements scalability |
| Previous Message | Nathan Bossart | 2026-11-17 16:42:48 | create .abi-compliance-history file for v19 |