Re: SQL/JSON: functions

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
Cc: Oleg Bartunov <obartunov(at)postgrespro(dot)ru>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, Andres Freund <andres(at)anarazel(dot)de>, 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: 2018-07-03 12:56:52
Message-ID: CAFj8pRA8620MsUBN3tvoqJT00n7rZpYRSG5r_wfLWK9WZnvuyw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

2018-07-03 14:30 GMT+02:00 Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)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(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.
>
> 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
>
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2018-07-03 13:45:55 Re: [HACKERS] plpgsql - additional extra checks
Previous Message Ashutosh Bapat 2018-07-03 12:43:12 Re: Desirability of client-side expressions in psql?