Re: remaining sql/json patches

On Wed, Jan 24, 2024 at 10:11 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> On Tue, Jan 23, 2024 at 12:46 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> > On Mon, Jan 22, 2024 at 10:28 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > >
> > > > based on v35.
> > > > Now I only applied from 0001 to 0007.
> > > > For {DEFAULT expression ON EMPTY} | {DEFAULT expression ON ERROR}
> > > > restrict DEFAULT expression be either Const node or FuncExpr node.
> > > > so these 3 SQL/JSON functions can be used in the btree expression index.
> > >
> > > I'm not really excited about adding these restrictions into the
> > > transformJsonFuncExpr() path. Index or any other code that wants to
> > > put restrictions already have those in place, no need to add them
> > > here. Moreover, by adding these restrictions, we might end up
> > > preventing users from doing useful things with this like specify
> > > column references. If there are semantic issues with allowing that,
> > > we should discuss them.
> > >
> >
> > after applying v36.
> > The following index creation and query operation works. I am not 100%
> > sure about these cases.
> > just want confirmation, sorry for bothering you....
>
> No worries; I really appreciate your testing and suggestions.
>
> > drop table t;
> > create table t(a jsonb, b int);
> > insert into t select '{"hello":11}',1;
> > insert into t select '{"hello":12}',2;
> > CREATE INDEX t_idx2 ON t (JSON_query(a, '$.hello1' RETURNING int
> > default b + random() on error));
> > CREATE INDEX t_idx3 ON t (JSON_query(a, '$.hello1' RETURNING int
> > default random()::int on error));
> > create or replace function ret_setint() returns setof integer as
> > $$
> > begin
> > -- perform pg_sleep(0.1);
> > return query execute 'select 1 union all select 1';
> > end;
> > $$
> > language plpgsql IMMUTABLE;
> > SELECT JSON_query(a, '$.hello1' RETURNING int default ret_setint() on
> > error) from t;
> > SELECT JSON_query(a, '$.hello1' RETURNING int default sum(b) over()
> > on error) from t;
> > SELECT JSON_query(a, '$.hello1' RETURNING int default sum(b) on
> > error) from t group by a;
> >
> > but the following cases will fail related to index and default expression.
> > create table zz(a int, b int);
> > CREATE INDEX zz_idx1 ON zz ( (b + random()::int));
> > create table ssss(a int, b int default ret_setint());
> > create table ssss(a int, b int default sum(b) over());
>
> I think your suggestion to add restrictions on what is allowed for
> DEFAULT makes sense. Also, immutability shouldn't be checked in
> transformJsonBehavior(), but in contain_mutable_functions() as done in
> the attached. Added some tests too.
>
> I still need to take a look at your other report regarding typmod but
> I'm out of energy today.

The attached updated patch should address one of the concerns --
JSON_QUERY() should now work appropriately with RETURNING type with
typmod whether or OMIT QUOTES is specified.

But I wasn't able to address the problems with RETURNING
record_type_with_typmod, that is, the following example you shared
upthread:

create domain char3_domain_not_null as char(3) NOT NULL;
create domain hello as text not null check (value = 'hello');
create domain int42 as int check (value = 42);
create type comp_domain_with_typmod AS (a char3_domain_not_null, b int42);
select json_value(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning
comp_domain_with_typmod);
json_value
------------

(1 row)

select json_value(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning
comp_domain_with_typmod error on error);
ERROR: value too long for type character(3)

select json_value(jsonb'{"rec": "abcd"}', '$.rec' returning
char3_domain_not_null error on error);
json_value
------------
abc
(1 row)

The problem with returning comp_domain_with_typmod from json_value()
seems to be that it's using a text-to-record CoerceViaIO expression
picked from JsonExpr.item_coercions, which behaves differently than
the expression tree that the following uses:

select ('abcd', 42)::comp_domain_with_typmod;
row
----------
(abc,42)
(1 row)

I don't see a good way to make RETURNING record_type_with_typmod to
work cleanly, so I am inclined to either simply disallow the feature
or live with the limitation.

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com