Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVmgb-001CJj-D4 for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 05:33:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qVmga-00AcFh-4D for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 05:33:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVmgZ-00AcFZ-PH for pgsql-hackers@lists.postgresql.org; Tue, 15 Aug 2023 05:33:43 +0000 Received: from mail-yb1-xb2c.google.com ([2607:f8b0:4864:20::b2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qVmgX-000Dyq-Lq for pgsql-hackers@postgresql.org; Tue, 15 Aug 2023 05:33:43 +0000 Received: by mail-yb1-xb2c.google.com with SMTP id 3f1490d57ef6-cfcebc33d04so4915759276.2 for ; Mon, 14 Aug 2023 22:33:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692077620; x=1692682420; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=s/Mq4YN7NrquuIwGWNLTSEjwKAE9Tz4MLKQqPx17kk4=; b=WSkUQHSl7GzLjt73mnWtlr77z0XNCdCwVaY2peRkouUvpl5JPE/fqiT1mIL4PlJ+2R dz0ddV+Eas3naN5Pi1Wpy9TuiiJECJPGHb5jvwpV0/oriT1hUK227za2N1FG6u3u2Pu+ aSv1/s8WxcVdzhNj2S3Li6nH4Y+wKANBDmTLp/exu4+UffychHdW8Sh926iAZmyM66rE /aOqMWiclWcWv1xAjr8d3NsCSGps4SDi9m13LOXVlegOHlSiZLtl3n7X7oNWCojnRDz3 R2qE0wGIbIBEA851O0Jidb7Nw1wTv7ZcHdGoRsF9dItZ+r8h2aYhpryGBFHeezuATuRX fzEw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692077620; x=1692682420; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=s/Mq4YN7NrquuIwGWNLTSEjwKAE9Tz4MLKQqPx17kk4=; b=bXWhbOsQVz+T88qFR4IN6yIXvwO01KT0vf7K9RQ1ae6MWHYKhhV7wZ68vBhqVAbocG KBwp8D1tU+ODdr7RzJcSR1zUBO1/GGXQnL5tvCSYRrNl1bGb9BZVkZBxqNxpuqVDdyED yNqMjLSh8qlDwVdGBimC3A6zu7txIgvg6hVOjd5ycAsfVZXW7AAP3CQQAfRk5dEimhZI 47/StkFB3gs+PGzHrsKFGrFTEZXJoWuG2Ci+rg4MKX9sidUrSdUKce1V1J+9YzDrzHxt QKw/LHQSAc8UvvFyw5CT3yj+cygdD2PHD38vBBVo328R6w3xGx/gARELb71jlLgTLe3N uacQ== X-Gm-Message-State: AOJu0Yyb439LtqmU11lnq5iluOFPtJrurX1axqtSgLF8u9duclXeiZm2 jCzmfWPWbz94bSXD4GXvYma/qeL8mUaaoP3hoEf+QaNpIHc= X-Google-Smtp-Source: AGHT+IFIuLuhLxBPnZHvObXIF3Akhb+rrZI2/nRCSURP1W92wUtoUi+dljCdeTv/N9AkoWTrOUjA5Ox2kyXWADPEjKQ= X-Received: by 2002:a25:cbcd:0:b0:c00:8d5b:56fe with SMTP id b196-20020a25cbcd000000b00c008d5b56femr13364178ybg.19.1692077619910; Mon, 14 Aug 2023 22:33:39 -0700 (PDT) MIME-Version: 1.0 References: <3507485.1691090027@sss.pgh.pa.us> <900892914fdc3f477b101d699efb40e0@anastigmatix.net> <903341.1692022214@sss.pgh.pa.us> In-Reply-To: From: Pavel Stehule Date: Tue, 15 Aug 2023 07:33:03 +0200 Message-ID: Subject: Re: Extract numeric filed in JSONB more effectively To: Andy Fan Cc: Tom Lane , Chapman Flack , jian he , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000779ec30602ef8575" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000779ec30602ef8575 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C3=BAt 15. 8. 2023 v 7:23 odes=C3=ADlatel Pavel Stehule napsal: > Hi > > =C3=BAt 15. 8. 2023 v 5:24 odes=C3=ADlatel Andy Fan > napsal: > >> >>> jsonb_extract_xx_type just cares about the argtype, but >>> 'explain select xx' will still access the const->constvalue. >>> const->constvalue is 0 which is set by makeNullConst currently, >>> and it is ok for the current supported type. >>> >> >> The exception is numeric data type, the constvalue can't be 0. >> so hack it with the below line. maybe not good enough, but I >> have no better solution now. >> >> + Const *target =3D >> makeNullConst(fexpr->funcresulttype, >> + >> -1, >> + >> InvalidOid); >> + /* >> + * Since all the above functions are strict, we >> can't input >> + * a NULL value. >> + */ >> + target->constisnull =3D false; >> + >> + Assert(target->constbyval || target->consttype = =3D=3D >> NUMERICOID); >> + >> + /* Mock a valid datum for !constbyval type. */ >> + if (fexpr->funcresulttype =3D=3D NUMERICOID) >> + target->constvalue =3D >> DirectFunctionCall1(numeric_in, CStringGetDatum("0")); >> >> > Personally I think this workaround is too dirty, and better to use a > strict function (I believe so the overhead for NULL values is acceptable)= , > or introduce a different mechanism. > > Your design is workable, and I think acceptable, but I don't think it is > an ideal or final solution. It is not really generic. It doesn't help wit= h > XML or Hstore. You need to touch cast functions, which I think is not bes= t, > because cast functions should not cooperate on optimization of execution = of > another function. > > My idea of an ideal solution is the introduction of the possibility to us= e > "any" pseudotype as return type with possibility to set default return > type. Now, "any" is allowed only for arguments. The planner can set the > expected type when it knows it, or can use the default type. > > so for extraction of jsonb field we can use FUNCTION > jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb > > if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb, > if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns d= ate > > With this possibility we don't need to touch to cast functions, and we ca= n > simply implement similar functions for other non atomic types. > this syntax can be used instead NULL::type trick like SELECT jsonb_populate_record('{...}')::pg_class; instead SELECT jsonb_populate_record(NULL::pg_class, '{...}') > > > > -- >> Best Regards >> Andy Fan >> > --000000000000779ec30602ef8575 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
=C3=BAt 15. 8. 2023 v=C2=A07:23 odes= =C3=ADlatel Pavel Stehule <pa= vel.stehule@gmail.com> napsal:
Hi

=C3=BAt 15. 8. 2023 v=C2= =A05:24 odes=C3=ADlatel Andy Fan <zhihui.fan1213@gmail.com> napsal:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">

=C2=A0jsonb_extrac= t_xx_type just cares about the argtype, but=C2=A0
'explain se= lect xx'=C2=A0 will still access the const->constvalue.
co= nst->constvalue is 0 which is set by makeNullConst currently,=C2=A0
and it is ok for the current supported type.=C2=A0

The exception is numeric data type, the c= onstvalue can't be 0.=C2=A0
so hack it with the below line.=C2=A0 m= aybe not good enough,=C2=A0 but I
have no better solution=C2=A0no= w.=C2=A0

+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Const =C2=A0 *target =3D =C2=A0makeNullC= onst(fexpr->funcresulttype,
+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-1,
+ =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Invali= dOid);
+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 /*
+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* Since all the above functions are strict, = we can't input
+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0* a NULL value.
+ =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0*/
+ =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 t= arget->constisnull =3D false;
+ =C2=A0 =C2=A0 =C2=A0
+ =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Assert(t= arget->constbyval || target->consttype =3D=3D NUMERICOID);
+ =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
+ =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 /* Mock a valid datum = for !constbyval type. */
+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if (fexpr->funcresulttype =3D=3D NUMERIC= OID)
+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 target->constvalue =3D DirectF= unctionCall1(numeric_in, CStringGetDatum("0"));


Personally I think this workaround is t= oo dirty, and better to use a strict function (I believe so the overhead fo= r NULL values is acceptable), or introduce a different mechanism.

Your design is workable, and I think acceptable, but I don&= #39;t think it is an ideal or final solution. It is not really generic. It = doesn't help with XML or Hstore. You need to touch cast functions, whic= h I think is not best, because cast functions should not cooperate on optim= ization of execution of another function.

My idea = of an ideal solution is the introduction of the possibility to use "an= y" pseudotype as return type with possibility to set default return ty= pe. Now, "any" is allowed only for arguments. The planner can set= the expected type when it knows it, or can use the default type.

so for extraction of jsonb field we can use FUNCTION jsonb_= extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb
=
if we call SELECT jsonb_extract_field(..., 'x') ->= ; then it returns jsonb, if we use SELECT jsonb_extract_field('...'= , 'x')::date, then it returns date

Wit= h this possibility we don't need to touch to cast functions, and we can= simply implement similar functions for other non atomic types.

this syntax can be used instead = NULL::type trick

like

SEL= ECT jsonb_populate_record('{...}')::pg_class;

<= div>instead=C2=A0

SELECT jsonb_populate_record(NUL= L::pg_class, '{...}')

=C2=A0



--
Best Regards
Andy= Fan
--000000000000779ec30602ef8575--