Re: SQL/JSON: functions

Attached 16th version of the patches:* changed type of new SQL keyword STRING   (STRING is used as a function parameter name in Pl/Tcl tests)* removed implicit coercion via I/O from JSON_VALUE (see below)

2018-06-28 2:18 GMT+02:00 Nikita Glukhov <n.gluhov@postgrespro.ru>:

On 15.03.2018 20:04, Nikita Glukhov wrote:

Attached 13th version of the patches:

* Subtransactions in PG_TRY/CATCH in ExecEvalJsonExpr() were made unconditional,
  regardless of the volatility of expressions.

* PG_TRY/CATCH in ExecEvalExprPassingCaseValue() was removed along with the
  entire function.

Attached 15th version of the patches:
 * disabled parallel execution of SQL/JSON query functions when internal
   subtransactions are used (if ERROR ON ERROR is not specified)
 * added experimental optimization of internal subtransactions (see below)


The new patch #14 is an experimental attempt to reduce overhead of
subtransaction start/commit which can result in 2x-slowdown in the simplest
cases.  By the idea of Alexander Korotkov, subtransaction is not really
committed if it has not touched the database and its XID has not been assigned
(DB modification is not expected in type casts functions) and then can be reused
when the next subtransaction is started.  So, all rows in JsonExpr can be
executed in the single cached subtransaction.  This optimization really helps
to reduce overhead from 100% to 5-10%:

I read a technical report for SQL/JSON. If I understand it well, then ON ERROR clause is primary related to structural errors, not to all errors.

So your implementation is maybe too tolerant, what has this issue. There was not any example, so this clause should to handle cast errors or any other errors than JSON structural.

The playing with other implementation of subtransactions doesn't look like safe way, more if it is not necessary

The other possible error are casts errors. We can introduce new exception safe input functions. These functions can be interesting for fault tolerant COPY for example.

SQL/JSON standard requires handling of cast errors too.

9.40 Casting an SQL/JSON sequence to an SQL type (pages 724-725):

4) If TEMPST is successful completion, then: b) If the length of SEQ is 1 (one), then let I be the SQL/JSON item in SEQ.   Case:     ...   iii) Otherwise, let IDT be the data type of I.     Case:     1) If IDT cannot be cast to target type DT according to the Syntax Rules        of Subclause 6.13, "<cast specification>", then let TEMPST be data        exception — SQL/JSON item cannot be cast to target type.     2) Otherwise, let X be an SQL variable whose value is I. Let V be the        value of CAST (X AS DT).  If an exception condition is raised by this        <cast specification>, then let TEMPST be that exception condition. ...
5) Case: a) If TEMPST is successful completion, then let OUTST be successful    completion. b) If ONERROR is ERROR, then let OUTST be TEMPST. c) If ONERROR is NULL, then let V be the SQL null value and let OUTST be    successful completion. d) If ONERROR immediately contains DEFAULT, then let VE be the    <value expression> immediately contained in ONERROR. Let V be the value of    CAST (VE AS DT)   Case:   i) If an exception condition is raised by this <cast specification>, then      let OUTST be that exception condition.   ii) Otherwise, let OUTST be successful completion.


In 4.b.iii.1 said that there should be an error if the desired cast does not exist.
In the previous versions of the patches there was implicit coercion via I/O here 
instead of error, so I decided to fix it the last version (fix is combined with a
minor refactoring of ExecEvalJsonExpr()).

-- 
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company