Re: SQL/JSON features for v15

Hi,

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