Re: finish TODOs in to_json_is_immutable, to_jsonb_is_immutable also add tests on it - Mailing list pgsql-hackers

On 2026-03-10 Tu 2:31 AM, jian he wrote:
> On Fri, Mar 6, 2026 at 6:09 AM Andrew Dunstan <andrew@dunslane.net> wrote:
>> Here's a rework of this patch. It preserves the original signature of
>> to_json{b}_is_immutable, and fixes some code duplication. It also uses
>> the typecache to get composite info instead of calling relation_open,
>> supports MultiRange types, and exits early if we made a recursive call
>> (no need for json_categorize_type etc. in these cases).
>>
>
> In json_categorize_type, we have:
>          case INT2OID:
>          case INT4OID:
>          case INT8OID:
>          case FLOAT4OID:
>          case FLOAT8OID:
>          case NUMERICOID:
>              getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
>              *tcategory = JSONTYPE_NUMERIC;
>
> select proname, provolatile from pg_Proc where proname in ('int8out',
> 'int2out', 'int4out', 'float4out', 'float8out', 'numeric_out');
>
>     proname   | provolatile
> -------------+-------------
>   int2out     | i
>   int4out     | i
>   float4out   | i
>   float8out   | i
>   int8out     | i
>   numeric_out | i
> (6 rows)
>
> Therefore for JSONTYPE_NUMERIC, has_mutable in json_check_mutability
> will be false.
> I slightly changed the `switch (tcategory)` handling in `json_check_mutability`.
> Make the handling order the same as JsonTypeCategory.
>
>
> Cosmetic change in src/test/regress/sql/sqljson.sql
> --error
> To
> -- error
>


OK, here's a v7.


. added a break in the loop if we found something mutable

. added test for JSON generated columns (was present for JSONB 
expression indexes but missing for JSON).

. added test block demonstrating that range_int (subtype=int) and 
multirange_int are now correctly treated as immutable, allowing 
expression indexes via json_array()
   and json_object()



cheers


andrew


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