Re: [PATCH]Fix planner's strictness check for JsonExpr

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

In response to

Browse pgsql-hackers by date

  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