On 09.02.24 10:05, Andy Fan wrote:
2. Where is the current feature blocked for the past few months?
It's error message compatible issue! Continue with above setup:
master:
select * from tb where (a->'b')::numeric > 3::numeric;
ERROR: cannot cast jsonb string to type numeric
select * from tb where (a->'b')::int4 > 3::numeric;
ERROR: cannot cast jsonb string to type integer
You can see the error message is different (numeric vs integer).
Patched:
We still can get the same error message as master BUT the code
looks odd.
select * from tb where (a->'b')::int4 > 3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on public.tb
Output: a
Filter: ((jsonb_finish_numeric(jsonb_object_field_start((tb.a)::internal,
'b'::text), '23'::oid))::integer > 3)
(3 rows)
You can see "jsonb_finish_numeric(.., '23::oid)" the '23::oid' is just
for the *"integer"* output in error message:
"cannot cast jsonb string to type*integer*"
Now the sistuation is either we use the odd argument (23::oid) in
jsonb_finish_numeric, or we use a incompatible error message with the
previous version. I'm not sure which way is better, but this is the
place the current feature is blocked.
I'm not bothered by that. It also happens on occasion in the backend C
code that we pass around extra information to be able to construct
better error messages. The functions here are not backend C code, but
they are internal functions, so similar considerations can apply.
But I have a different question about this patch set. This has some
overlap with the JSON_VALUE function that is being discussed at [0][1].
For example, if I apply the patch
v39-0001-Add-SQL-JSON-query-functions.patch from that thread, I can run
select count(*) from tb where json_value(a, '$.a' returning numeric) = 2;
and I get a noticeable performance boost over
select count(*) from tb where cast (a->'a' as numeric) = 2;
So some questions to think about:
1. Compare performance of base case vs. this patch vs. json_value.
2. Can json_value be optimized further?
3. Is this patch still needed?
3a. If yes, should the internal rewriting make use of json_value or
share code with it?
[0]:
https://www.postgresql.org/message-id/flat/CA+HiwqE4XTdfb1nW=ojoy_tqsrhyt-q_kb6i5d4xckyrlc1...@mail.gmail.com
[1]: https://commitfest.postgresql.org/47/4377/
