Re: SQL/JSON features for v15

On 23.08.2022 19:06, Pavel Stehule wrote:
> Hi
>
> út 23. 8. 2022 v 17:55 odesílatel Andres Freund <andres@anarazel.de> 
> napsal:
>
>     Hi,
>
>     On 2022-08-23 10:51:04 -0400, Robert Haas wrote:
>     > I do not think that using subtransactions as part of the expression
>     > evaluation process is a sound idea pretty much under any
>     > circumstances. Maybe if the subtransations aren't commonly
>     created and
>     > don't usually get XIDs there wouldn't be a big problem in practice,
>     > but it's an awfully heavyweight operation to be done inside
>     expression
>     > evaluation even in corner cases. I think that if we need to make
>     > certain operations that would throw errors not throw errors, we need
>     > to refactor interfaces until it's possible to return an error
>     > indicator up to the appropriate level, not just let the error be
>     > thrown and catch it.
>
>     I don't think that's quite realistic - that's the input/output
>     functions for
>     all types, basically.  I'd be somewhat content if we'd a small
>     list of very
>     common coercion paths we knew wouldn't error out, leaving things
>     like OOM
>     aside. Even just knowing that for ->text conversions would be a
>     huge deal in
>     the context of this patch.  One problem here is that the whole
>     type coercion
>     infrastructure doesn't make it easy to know what "happened inside"
>     atm, one
>     has to reconstruct it from the emitted expressions, where there can be
>     multiple layers of things to poke through.
>
>
> The errors that should be handled are related to json structure 
> errors. I don't think so we have to handle all errors and all conversions.
>
> The JSON knows only three types - and these conversions can be written 
> specially for this case - or we can write json io routines to be able 
> to signal error
> without an exception.


I also wanted to suggest to limit the set of returning types to the
predefined set of JSON-compatible types for which can write safe
conversion functions: character types (text, char), boolean, number
types (integers, floats types, numeric), datetime types.  The SQL
standard even does not require support of other returning types.

For the float8 and datetime types we already have safe input functions
like float8in_internal_opt_error() and parse_datetime() which are used
inside jsonpath and return error code instead of throwing errors.
We need to implement numeric_intN_safe() and maybe a few other trivial
functions like that.

The set of returning types, for which we do not need any special
coercions, is very limited: json, jsonb, text.  More precisely,
even RETURNING json[b] can throw errors in JSON_QUERY(OMIT QUOTES),
and we also need safe json parsing, but it can be easily done
with pg_parse_json(), which returns error code.