| From: | Tomas Vondra <tomas(at)vondra(dot)me> |
|---|---|
| To: | Amit Langote <amitlangote09(at)gmail(dot)com>, Keyerror Smart <smartkeyerror(at)gmail(dot)com> |
| Cc: | ethantran(at)quwin(dot)dev, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: [PATCH]Fix planner's strictness check for JsonExpr |
| Date: | 2026-11-19 14:56:47 |
| Message-ID: | 6f3595a8-6ac7-4120-9413-cf2fefe0428f@vondra.me |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On 11/19/26 14:40, Amit Langote wrote:
> Hi,
>
> Tomas (added him back) asked me to take a look at the SQL/JSON side of
> the issues he's tackling at:
> https://www.postgresql.org/message-id/b368a3ca-b196-4c28-8050-50320e9c955d@vondra.me
>
> His description there of how a NULL context item, path, and PASSING
> value are handled matches what ExecEvalJsonExprPath() does. I
> understand that for the JsonExpr part we're preferring the patch in
> this thread.
>
Right. I think I prefer the simpler patch for backpatching. We may
choose to do the "better" version as an improvement on master, if
someone presents a good case for it (shows a *plausible* query that is
affected by lack of the optimization).
> Also, there's some relevance to the issues described at:
> https://www.postgresql.org/message-id/19851-f88328048ff9770a%40postgresql.org
>
> where I'm about to post a patch that removes the use_json_coercion
> flag from JsonExpr. This patch relies on that flag, so the two
> conflict, and I have some comments given that.
>
OK
> On Thu, Nov 19, 2026 at 10:37 AM Keyerror Smart <smartkeyerror(at)gmail(dot)com> wrote:
>>
>> Hi Tomas,
>>
>> Thanks for looking at this, and for cross-checking v4 against your
>> test suite.
>>
>>> 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).
>>
>> You're right, and it's worse than a missed case: v4 even had a test
>> asserting that this gets inlined. My reasoning was that with NULL /
>> UNKNOWN / ERROR behaviors the result is "either NULL or an error", but
>> of course an error is not NULL -- the STRICT function returns NULL
>> without evaluating its body, while the inlined expression raises. With
>> a NOT NULL domain, both the default NULL ON ERROR ("could not coerce ON
>> ERROR expression (NULL) to the RETURNING type") and ERROR ON ERROR fail
>> for a NULL context item, for both the inlining and the outer-join
>> pull-up paths.
>>
>> Attached v5 adopts your check as-is:
>>
>> if (jsexpr->use_json_coercion &&
>> get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN)
>> return true;
>>
>> so the rule is now: a JsonExpr is non-strict if it coerces its result
>> to a domain via json_populate_type(), if it has PASSING arguments, or
>> if either ON EMPTY / ON ERROR is anything other than NULL, UNKNOWN or
>> ERROR; otherwise its inputs are checked as usual. Like yours, it
>> doesn't look at DomainHasConstraints(), which also keeps it independent
>> of constraints added after planning.
>
> Given how use_json_coercion is set in the parser in
> transformJsonFuncExpr() for JSON_VALUE():
>
> /*
> * Set up to coerce the result value of JsonPathValue() to the
> * RETURNING type (default or user-specified), if needed.
> */
> if (jsexpr->returning->typid != TEXTOID)
> {
> if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
> DomainHasConstraints(jsexpr->returning->typid, NULL))
> jsexpr->use_json_coercion = true;
> else
> jsexpr->use_io_coercion = true;
> }
>
> looking at use_json_coercion already means that you're relying on
> DomainHasConstraints() to make a planning decision that would go stale
> if a constraint is added or dropped. In the other thread I mentioned,
> the idea is to remove that flag exactly because it can become stale if
> the answer of DomainHasConstraints() changes after it is stored in the
> flag, which causes an issue both for a plan that is stored and reused
> and for a parse tree that gets stored in the catalog, such as for
> views.
>
Does it mean we'll remove the risk of it being stale entirely? So the
executor will keep using the "old" value used when planning?
> Today the executor relies on the same flag, so it can skip checking a
> NULL result against a domain whose constraints were added later, which
> is one of the things being fixed in the other thread. Once the
> executor always performs that check, a JsonExpr returning any domain
> can turn a NULL input into an error or the ON ERROR value, so it has
> to be treated as non-strict whatever the flag says.
>
> So we should remove the reference to use_json_coercion from this
> patch, which I'm planning to make obsolete anyway (remove it in master
> and keep it in the back-branches but not use its value), and keep only
> the get_typtype() test. That adds JSON_VALUE() returning a domain
> without constraints to the cases that lose inlining. v5 already loses
> it for JSON_QUERY() returning any domain, since the flag is set there
> whenever the RETURNING type isn't jsonb. I don't see a back-patchable
> way to avoid either, because, as already noted upthread, that would
> need a plan dependency on the domain, which
> contain_nonstrict_functions() has no way to record.
>
How will that affect backbranches / backpatch version of the fix? I
suppose that'll still need to check the use_json_coercion flag?
> Re the v5 patch:
>
> The commit message and the test comment say that for a NULL context
> item or path the ON EMPTY / ON ERROR behavior can still supply a
> non-NULL value. That only happens when the RETURNING type is a domain
> that rejects the NULL, which the domain check already covers;
> otherwise those behaviors aren't consulted at all. The only way a
> behavior can make the expression non-strict by itself is a DEFAULT
> expression that references a parameter, as in your DEFAULT $1 + 0
> test. So treating every behavior other than NULL, UNKNOWN or ERROR as
> non-strict is broader than needed, e.g. for EMPTY ARRAY, a constant
> DEFAULT, or a plain JSON_EXISTS() with its implicit FALSE ON ERROR.
>
> Tomas's 0004 on the other thread does the precise thing: it treats the
> JsonExpr as non-strict only if a Var, PlaceHolderVar or Param appears
> in the PASSING values or in the ON EMPTY / ON ERROR expressions. It's
> small and only looks at the node, so I don't think it's any harder to
> back-patch. Attached is a variant of it that applies on top of v5,
> with the domain check without the flag and the behavior part done as a
> JsonBehavior case in the walker. All of v5's tests pass unchanged
> with it. The only thing given up then is inlining when RETURNING a
> domain, so I'd suggest going that way in the next version, with a test
> showing that a wrapper RETURNING a domain without constraints is not
> inlined.
>
WFM. I'd be fine with the simpler approach, mostly because I'm new to
the SQL/JSON thing and I wasn't sure it's 100% correct. So I was opting
for the more conservative fix. But if you agree it's correct, we can
backpatch, that's fine.
regards
--
Tomas Vondra
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2026-11-19 15:05:14 | Re: Block various aggregate component functions from being called directly |
| Previous Message | Tomas Vondra | 2026-11-19 14:46:40 | Re: [PATCH]Fix planner's strictness check for JsonExpr |