Re: SQL/JSON: functions

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,604
JSON_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