Horton
Menu

Re: SQL/JSON: functions

Nikita Glukhov <n.gluhov@postgrespro.ru>

View thread
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Oleg Bartunov <obartunov@postgrespro.ru>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>, Alexander Korotkov <a.korotkov@postgrespro.ru>, Andres Freund <andres@anarazel.de>, Michael Paquier <michael@paquier.xyz>, Simon Riggs <simon@2ndquadrant.com>, Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Date: 2018-07-03T12:30:27Z
Lists: pgsql-hackers

Attachments

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. JSON_TABLE: Add support for NESTED paths and columns

  2. Add basic JSON_TABLE() functionality

  3. Add SQL/JSON query functions

  4. Add soft error handling to some expression nodes

  5. Adjust populate_record_field() to handle errors softly

  6. Refactor code used by jsonpath executor to fetch variables

  7. Add more SQL/JSON constructor functions

  8. SQL/JSON: support the IS JSON predicate

  9. SQL/JSON: add standard JSON constructor functions

  10. Improve readability of SQL/JSON documentation.

  11. Documentation for SQL/JSON features

  12. RETURNING clause for JSON() and JSON_SCALAR()

  13. SQL JSON functions

  14. SQL/JSON query functions

  15. IS JSON predicate

  16. SQL/JSON constructors

  17. Common SQL/JSON clauses

  18. Doc: remove obsolete entries in table of BRIN strategy numbers.

  19. Doc: standardize markup a bit more.

  20. Make pg_regress.c unset PGDATABASE during make installcheck.

  21. Fix handling of GENERATED columns in CREATE TABLE LIKE INCLUDING DEFAULTS.