Re: SQL/JSON: functions

2018-07-03 14:30 GMT+02:00 Nikita Glukhov <n.gluhov@postgrespro.ru>:

> 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.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.
>
> I didn't speak something else. But cast (and in this case it is from JSON
to some else) can be exception safe.

Regards

Pavel


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