Re: SQL/JSON features for v15

Hi,

On 24.08.2022 01:12, Andrew Dunstan wrote:
> On 2022-08-23 Tu 17:29, Nikita Glukhov wrote:
>
>> Here is my plan:
>>
>> 0. Take my last v7-0001 patch as a base. It already contains refactoring
>> of JsonCoercion code. (Fix 0002 is not needed anymore, because it is for
>> json[b] domains, which simply will not be supported.)
>>
>> 1. Replace JSON_COERCION_VIA_EXPR in JsonCoercion with new
>> JsonCoercionType(s) for hardcoded coercions.

JsonCoerion node were completely removed because they are not needed
anymore (see p. 4).

>> 2. Disable all non-JSON-compatible output types in coerceJsonFuncExpr().
>>
>> 3. Add missing safe type input functions for integers, numerics, and
>> maybe others.

We need to write much more functions, than I expected. And I still didn't
implemented safe input functions for numeric and datetime types.
I will start to do it tomorrow.

>> 4. Implement hardcoded coercions using these functions in
>> ExecEvalJsonExprCoercion().

That was done using simple `switch (returning_typid) { .. }`,
which can be nested into `switch (jbv->type)`.

>> 5. Try to allow only constants (and also maybe column/parameter
>> references) in JSON_VALUE's DEFAULT expressions. This should be enough
>> for the most of practical cases. JSON_QUERY even does not have DEFAULT
>> expressions -- it has only EMPTY ARRAY and EMPTY OBJECT, which can be
>> treated as simple JSON constants.

I have not tried to implement this yet.

> er, really? This is from the regression output:
>
>
> SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
> json_query
> ------------
> "empty"
> (1 row)
>
> SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
> json_query
> ------------
> "empty"
> (1 row)
>
This is another extension. SQL standard defines only
EMPTY ARRAY an EMPTY OBJECT behavior for JSON_QUERY:

<JSON query empty behavior> ::=
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT

<JSON query error behavior> ::=
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT

>> But it is possible to allow all other expressions in ERROR ON ERROR
>> case, and I don't know if it will be consistent enough to allow some
>> expressions in one case and deny in other.
>>
>> And there is another problem: expressions can be only checked for
>> Const-ness only after expression simplification. AFAIU, at the
>> parsing stage they look like 'string'::type. So, it's unclear if it
>> is correct to check expressions in ExecInitExpr().
>>
>> 6. Remove subtransactions.

They were completely removed. Only DEFAULT expression needs to be fixed now.

> Sounds like a good plan, modulo the issues in item 5. I would rather
> lose some features temporarily than try to turn handsprings to make them
> work and jeopardize the rest.
>
> I'll look forward to seeing your patch in the morning :-)
>
v8 - is a highly WIP patch, which I failed to finish today.
Even some test cases fail now, and they simply show unfinished
things like casts to bytea (they can be simply removed) and missing
safe input functions.

--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company