| 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:22:51 |
| Message-ID: | d559245b-4b3b-4039-b75e-ff20f52831a3@vondra.me |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On 11/17/26 18:10, Tomas Vondra wrote:
> 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.
>
To clarify, that does not mean the cases treated as non-strict by v4 is
very narrow. Expressions that stop being inlined in a STRICT SQL
function are for example:
- plain JSON_EXISTS(js, '$.a'), because of its implicit FALSE ON ERROR
- TRUE ON ERROR
- a constant PASSING 1 AS v , in JSON_EXISTS (with ERROR ON ERROR) and
in JSON_VALUE
- DEFAULT 0 ON EMPTY, DEFAULT -1 ON ERROR, and even DEFAULT NULL
- EMPTY ARRAY ON EMPTY and EMPTY OBJECT ON ERROR
AFAIK all of those are treated as strict with my fix. This can trigger
annoying plan changes:
- An expression index on the inlined expression is no longer used: Index
Scan becomes Seq Scan.
- A subquery pulled up from the nullable side of an outer join now gets
PlaceHolderVars.
...
But I'm still not convinced that makes it a serious issue, because how
many people are actually creating SQL functions wrapping JSON queries?
regards
--
Tomas Vondra
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Bharath Rupireddy | 2026-11-17 17:17:40 | Re: pg_walinspect: add functions to locate and list WAL by time and LSN |