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)

On 28.06.2018 07:25, Pavel Stehule wrote:
> 2018-06-28 2:18 GMT+02:00 Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru
> <mailto:n(dot)gluhov(at)postgrespro(dot)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