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