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 1qYKMK-001OPo-3C for pgsql-hackers@arkaria.postgresql.org; Tue, 22 Aug 2023 05:55:20 +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 1qYKMJ-00F1d4-2i for pgsql-hackers@arkaria.postgresql.org; Tue, 22 Aug 2023 05:55:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYKLi-00EyJo-M5 for pgsql-hackers@lists.postgresql.org; Tue, 22 Aug 2023 05:54:42 +0000 Received: from mail-oo1-xc35.google.com ([2607:f8b0:4864:20::c35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qYKLf-000HeM-AD for pgsql-hackers@postgresql.org; Tue, 22 Aug 2023 05:54:41 +0000 Received: by mail-oo1-xc35.google.com with SMTP id 006d021491bc7-570bfbce160so1523293eaf.2 for ; Mon, 21 Aug 2023 22:54:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692683678; x=1693288478; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=JBQczWsHzst29KNCEXXj2luNHnWPBkATpx/zIsHHhkk=; b=lUPyPtxQ80piUhTmr0xlxOwicnW7o6D19SLYWV6WFbRnkuaZmUIYl+ozR3Krc6Qoty XIWZaJNQEn5Fgkh0kNqpEhdh2JAOAD9LTB4OyUIut97nPKLsAoLrJTZAlZy7CHRVZFnd mpek8zlE7ZEdPDj6XnV2Tcu/trU4qZ27fF/BRo0lZlQnRrelhmQfCZDNflLiVEmHUBB5 XSFw4hhZD3PWrGTt4MYcOIiTcefEBDB4wTZGD+kpH4GM87HR6WnU6W6craD/cwxvtLDC 7GbRIm46oKpEsTU+UlTtQe6ZblWUfWrnJby22q0PYwmUhMHIoCUEWQODpqlp0o3sYCch iToQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692683678; x=1693288478; 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=JBQczWsHzst29KNCEXXj2luNHnWPBkATpx/zIsHHhkk=; b=h288JM5TFU91VXpQG3bnmEDjFrnne/p2z7ndf6puPgVP7WX0wLE4v7hr26FSriwSwf WK48KKkDZz2Y5SBgxOCs5g7XtiFUHgSw6vqnPDLLnHxnxAt2Mc0x1LeC6jridyvNLbwp 6s9sP/GjevQGx/D+zaroM9qmJE8/iiAcBiZnHPgRkyoUF8ccB4CKawyDLwZhx3li+HPh utqttb79p1JUn7B09skU/mjzugI/XTk2GW+OfG3MdMFGQODloOWhWa64Lvr/IYk1Bb2n s/CbOOvgimN+s2hVNxrKSMmv8FngvQmP9a1cLlasFYQIZjc9/CQPKErMzLyrqQX95y1Y X0jw== X-Gm-Message-State: AOJu0Yzn0gY+kC1SLhVW14V7t4S85fEHMRTdWBNjN9zbl21qZb7z9BuG geYLLlT8T86v81z0QAtqPea9l/zwuWKGBqeGC9Q= X-Google-Smtp-Source: AGHT+IHTP2Dskp3TqgSIzL6U8bOWCdRO3Fh5FHjatAWL33DWCS6+p8I5H8I7pdYnMndlz0IRqBoyD+wmeHM/8lauQyo= X-Received: by 2002:a4a:275b:0:b0:56c:e856:8b2c with SMTP id w27-20020a4a275b000000b0056ce8568b2cmr7697974oow.9.1692683678594; Mon, 21 Aug 2023 22:54:38 -0700 (PDT) MIME-Version: 1.0 References: <3507485.1691090027@sss.pgh.pa.us> <900892914fdc3f477b101d699efb40e0@anastigmatix.net> <903341.1692022214@sss.pgh.pa.us> <4b97f1a1dd9b6e45443d24870d3be698@anastigmatix.net> <111272f2dc112c7becdd35ad89f6b935@anastigmatix.net> <5138c6b5fd239e7ce4e1a4e63826ac27@anastigmatix.net> In-Reply-To: From: Andy Fan Date: Tue, 22 Aug 2023 13:54:27 +0800 Message-ID: Subject: Re: Extract numeric filed in JSONB more effectively To: Chapman Flack Cc: jian he , Pavel Stehule , Tom Lane , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000613bef06037ca176" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000613bef06037ca176 Content-Type: text/plain; charset="UTF-8" > > >>> Perhaps one of the more senior developers will chime in, but to me, >>> leaving out the relabel nodes looks more like "all of PostgreSQL's >>> type checking happened before the SupportRequestSimplify, so nothing >>> has noticed that we rewrote the tree with mismatched types, and as >>> long as nothing crashes we sort of got away with it." >>> >>> Suppose somebody writes an extension to double-check that plan >>> trees are correctly typed. Or improves EXPLAIN to check a little more >>> carefully than it seems to. Omitting the relabel nodes could spell >>> trouble then. >>> >>> Or, someone more familiar with the code than I am might say "oh, >>> mismatches like that are common in rewritten trees, we live with it." >>> But unless somebody tells me that, I'm not believing it. >>> >> >> Well, this sounds long-lived. I kind of prefer to label it now. Adding >> the 3rd commit to relabel the arg and return value. >> >> After we label it, we will get error like this: select (a->'a')::int4 from m; ERROR: cannot display a value of type internal However the following statement can work well. select ('{"a": 12345}'::jsonb->'a')::numeric; numeric --------- 12345 That's mainly because the later query doesn't go through the planner support function. I didn't realize this before so the test case doesn't catch it. Will add the test case in the next version. The reason why we get the error for the first query is because the query tree says we should output an "internal" result at last and then pg doesn't know how to output an internal data type. This is kind of in conflict with our goal. So currently the only choices are: PATCH 001 or PATCH 001 + 002. https://www.postgresql.org/message-id/CAKU4AWrs4Pzajm2_tgtUTf%3DCWfDJEx%3D3h45Lhqg7tNOVZw5YxA%40mail.gmail.com -- Best Regards Andy Fan --000000000000613bef06037ca176 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Perhaps one of the more senior developers will chime in, but to me,
leaving out the relabel nodes looks more like "all of PostgreSQL's=
type checking happened before the SupportRequestSimplify, so nothing
has noticed that we rewrote the tree with mismatched types, and as
long as nothing crashes we sort of got away with it."

Suppose somebody writes an extension to double-check that plan
trees are correctly typed. Or improves EXPLAIN to check a little more
carefully than it seems to. Omitting the relabel nodes could spell
trouble then.

Or, someone more familiar with the code than I am might say "oh,
mismatches like that are common in rewritten trees, we live with it."<= br> But unless somebody tells me that, I'm not believing it.

Well, this sounds long-lived.=C2=A0 I kind of prefer = to label it now.=C2=A0 Adding
the 3rd commit to relabel the arg a= nd return value.=C2=A0

<= /div>

After we label it, we will get error = like this:=C2=A0

select (a->'a')::int4 from m;
ERROR: =C2=A0cannot display a v= alue of type internal

However the following statement can work well.

=C2=A0select (= 9;{"a": 12345}'::jsonb->'a')::numeric;
=C2=A0nu= meric
---------
=C2=A0 =C2=A012345

That's mainly because the=C2=A0l= ater=C2=A0query doesn't go through the planner
support function. I didn't realize this before so the test case= doesn't=C2=A0
catch it.=C2=A0 Will add= the test case=C2=A0=C2=A0in the next version.=C2=A0 The reason why=C2=A0
we get the error for the first query is beca= use the query tree says=C2=A0
we should out= put=C2=A0 an "internal"=C2=A0 result at last and then pg doesn= 9;t
know how to output an internal data typ= e. This is kind of in conflict
with our goa= l.

So = currently the only choices are:=C2=A0 PATCH=C2=A0001 or PATCH 001=C2=A0+ 00= 2.=C2=A0

https://www.postgresql.o= rg/message-id/CAKU4AWrs4Pzajm2_tgtUTf%3DCWfDJEx%3D3h45Lhqg7tNOVZw5YxA%40mai= l.gmail.com=C2=A0

--
Best Regards
Andy Fan
<= /div> --000000000000613bef06037ca176--