Re: SQL/JSON: functions

From: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Oleg Bartunov <obartunov(at)postgrespro(dot)ru>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, Michael Paquier <michael(at)paquier(dot)xyz>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>
Subject: Re: SQL/JSON: functions
Date: 2019-03-01 01:14:09
Message-ID: 70d2bf4f-f932-15f3-4037-90acce48a3b6@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Implementation-of-SQL-JSON-path-language-v34.patch.gz application/gzip 101.7 KB
0006-Add-invisible-coercion-form-v34.patch text/x-patch 7.2 KB
0007-Add-function-formats-v34.patch text/x-patch 10.8 KB
0008-Implementation-of-SQL-JSON-functions-v34.patch.gz application/gzip 67.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-03-01 01:19:29 Re: get_controlfile() can leak fds in the backend
Previous Message Michael Paquier 2019-03-01 01:11:53 Re: get_controlfile() can leak fds in the backend