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 1qVmXK-001Bqn-Ck for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 05:24:10 +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 1qVmXI-00AXiC-RF for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 05:24:08 +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 1qVmXI-00AXi3-G9 for pgsql-hackers@lists.postgresql.org; Tue, 15 Aug 2023 05:24:08 +0000 Received: from mail-yw1-x1132.google.com ([2607:f8b0:4864:20::1132]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qVmXG-000Dux-9k for pgsql-hackers@postgresql.org; Tue, 15 Aug 2023 05:24:08 +0000 Received: by mail-yw1-x1132.google.com with SMTP id 00721157ae682-5899ed05210so52808187b3.3 for ; Mon, 14 Aug 2023 22:24:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692077044; x=1692681844; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=3DFh1Mk2Uv0zUKSSXm0Llrul76nP8krKdlREy4ROpW0=; b=H8yYTR+hIZAouOMsZbqkn7CphQXoqG2IceioWvQqmNqY2s/xo385I3fsDxKv5g0uvv /8kP92aDHtFqtcofCTQePjI8hcn04MwM+l4FacQnLwBKFB4vZA9SheNqY3VYerW5gsab TgLvOo4tX/u+SvztxahlH9PBE8MN3axZttWE76/0VEy9jAVsn1K3yL1zY3aK26agz6rO Gh8eRRKDBDYq7WZew1Cu5CED8iPV/Pj3TQCZrA0UTskO+ZF+RatCXBa4keCFRoUpNyV3 p/CmwzauuBl9fGtIL+d0yfXEmmZUNRYHzahmr6+6xSElhLK7wE20s0VoC542hL5E+56v gYAg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692077044; x=1692681844; 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=3DFh1Mk2Uv0zUKSSXm0Llrul76nP8krKdlREy4ROpW0=; b=k1/izVhN2Qlr3Vet01nKL8BIvn8Z1Oh1hvwn6B1hWJm/umM4Rtdfz5E+hyEK4PvABo dVyR8W1cj53pxPBMsC09f/hE8GmTPkpOxrPZEmLWG+8YlehsBNQBtb50SU5tBwczKGPC M55IoTuJPWqR8w6q2MNZMcfTr/lm3UtiM/8TNYYuoY6ukBIa/8V83AmN+FmEAhwxaXSu XE4F/Nnzy4RkWZBchv7Cn5AopnqkxoS/HWyQSjZUaAwAwOsd6O5VGgGQYWC2uoga0XHe Zq4o3wHnidgIoJFkf28nEfIFzBrfBn0nvEK8SR+TJXNY6+Z9Q1mo4XU7rzpJ7MWkEXEL 6diA== X-Gm-Message-State: AOJu0Yxvrhu9B7fQkIKmyGwZselez9MoiJYgNjCW4kW0Sw0pcoXE5CBB MFSiOSGNGH1wGP5hfdKlLly1XSdMmx6Cyp2MigJYNuPgs6Y= X-Google-Smtp-Source: AGHT+IH0w3W5zG6H5hnAQXE22dGt/SVcWcQwbCWyESvt9BS5znNGch2sNkFA/D81tWt8isc4RgUt8PcbiiWLMvLWYeY= X-Received: by 2002:a0d:cb4d:0:b0:56d:1528:c56f with SMTP id n74-20020a0dcb4d000000b0056d1528c56fmr12891809ywd.16.1692077044355; Mon, 14 Aug 2023 22:24:04 -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:23:26 +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="0000000000002957eb0602ef6308" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002957eb0602ef6308 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 with XML or Hstore. You need to touch cast functions, which I think is not best, 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 use "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 dat= e With this possibility we don't need to touch to cast functions, and we can simply implement similar functions for other non atomic types. --=20 > Best Regards > Andy Fan > --0000000000002957eb0602ef6308 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

=C3=BAt 15. 8. 2023 v=C2=A05:24 odes=C3=ADlat= el Andy Fan <zhihui.fan1213@= gmail.com> napsal:
<= div>
=C2=A0jsonb_extract_xx_type just cares about the argtype= , but=C2=A0
'explain select xx'=C2=A0 will still access t= he const->constvalue.
const->constvalue is 0 which is set b= y makeNullConst currently,=C2=A0
and it is ok for the current sup= ported 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.



--
Best Regards
Andy Fan
--0000000000002957eb0602ef6308--