Re: SQL/JSON features for v15

On 2022-08-23 Tu 17:29, Nikita Glukhov wrote:
>
>
> On 23.08.2022 22:31, Jonathan S. Katz wrote:
>> On 8/23/22 2:10 PM, Andrew Dunstan wrote:
>>>
>>> On 2022-08-23 Tu 13:24, Tom Lane wrote:
>>>> "Jonathan S. Katz" <jkatz(at)postgresql(dot)org> writes:
>>>>> I saw Andrew suggest that the controversial parts of the patchset
>>>>> may be
>>>>> severable from some of the new functionality, so I would like to see
>>>>> that proposal and if it is enough to overcome concerns.
>>>> It's an interesting suggestion.  Do people have the cycles available
>>>> to make it happen in the next few days?
>>>>
>>> I will make time although probably Nikita and/or Amit would be quicker
>>> than I would be.
>>
>> If you all can, you have my +1 to try it and see what folks think.
> I am ready to start hacking now, but it's already night in Moscow, so
> any result will be only tomorrow.
>
> 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.
>
> 2. Disable all non-JSON-compatible output types in coerceJsonFuncExpr().
>
> 3. Add missing safe type input functions for integers, numerics, and
> maybe others.
>
> 4. Implement hardcoded coercions using these functions in
> ExecEvalJsonExprCoercion().
>
> 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.

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)

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

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

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com