Attached 34th version of the patches.
On 16.02.2019 8:12, Andres Freund wrote:
On 2018-12-05 02:01:19 +0300, Nikita Glukhov wrote:
+ JsonLexContext *lex;
+ JsonTokenType tok;
+
+ lex = makeJsonLexContext(json, false);
+
+ /* Lex exactly one token from the input and check its type. */
+ PG_TRY();
+ {
+ json_lex(lex);
+ }
+ PG_CATCH();
+ {
+ if (ERRCODE_TO_CATEGORY(geterrcode()) == ERRCODE_DATA_EXCEPTION)
+ {
+ FlushErrorState();
+ MemoryContextSwitchTo(mcxt);
+ PG_RETURN_BOOL(false); /* invalid json */
+ }
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
It baffles me that a year after I raised this as a serious issue, in
this thread, this patch still contains code like this.
PG_TRY/PG_CATCH was removed here: 'throwErrors' flag was added to JsonLexContext
instead.
Also usage of subtransactions is SQL/JSON functions (JsonExpr node) was
optimized: they can be not only omitted in ERROR ON ERROR case but also when
the resulting conversion from the SQL/JSON item type to the target SQL type is
no-op.
Below are the results of simple performance test
(operator #>> uses optimization which I recently posted in the separate patch):
query | time, ms
-------------------------------------------------------------------JSON_VALUE(js, '$.x.y.z' RETURNING text) = '123' | 1923,360 (subtrans!)JSON_VALUE(js, '$.x.y.z' RETURNING text ERROR ON ERROR) = '123' | 970,604JSON_VALUE(js, '$.x.y.z' RETURNING numeric) = '123' | 792,412 JSON_VALUE(js, '$.x.y.z' RETURNING numeric ERROR ON ERROR) = '123' | 786,647
(js->'x'->'y'->'z') = '123' | 1104,470(js->'x'->'y'->'z')::numeric = '123' | 940,037(js->'x'->'y'->>'z') = '123' | 688,484
(js #> '{x,y,z}') = '123' | 1127,661(js #> '{x,y,z}')::numeric = '123' | 971,931(js #>> '{x,y,z}') = '123' | 718,173
Table with jsonb rows like '{"x": {"y": {"z": 123}}}':
CREATE TABLE t AS
SELECT JSON_OBJECT('x' : JSON_OBJECT('y' : JSON_OBJECT('z' : i))) js
FROM generate_series(1, 3000000) i;
Example query:
SELECT * FROM t WHERE JSON_VALUE(js, '$.x.y.z' RETURNING numeric) = '123';
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company