public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Chapman Flack <[email protected]>
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Wed, 16 Aug 2023 14:12:16 +0800
Message-ID: <CAKU4AWrxHFVZM-gGPpOrVPreZMePAOoY580Tq-+CvxDWHmP_uA@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRC+4pvSuibB2xcNKJ=6PSF=TAcOtRNpdLPBXZjijFg7ag@mail.gmail.com>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
<CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
<CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
<CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
<CAFj8pRAO3oEiBaJJ9=HZp6CoP2ffbwSgrKkLKjPfYZwx9wOOuQ@mail.gmail.com>
<CAKU4AWoCHpKAVuQeOrk44cVPy_dVxn1aHrMUvHy5Ag-daFCSsQ@mail.gmail.com>
<CAFj8pRD4cdUmK0RG4oN5B2KRSeDhwfMYaL=XpfEu4iaLeZ_Kow@mail.gmail.com>
<CAKU4AWp8ab61e96v57OaB-Gm1bMfBNVLVy+s17U6_Ne3veB84g@mail.gmail.com>
<[email protected]>
<CAKU4AWp+KLes8g=BWLqZfDmW9+=ZY0UC4G0i3qVcYEviK_dDTA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKU4AWrBY9GHj9oZbvhiOG1BgiWyZC8FGPAET-CfRKDhYyv1HQ@mail.gmail.com>
<CAKU4AWrGM5bK7wi4Y8bTYhKgh=A1fW=X00eC_jfk6_JXyaEURQ@mail.gmail.com>
<CAFj8pRC+4pvSuibB2xcNKJ=6PSF=TAcOtRNpdLPBXZjijFg7ag@mail.gmail.com>
On Tue, Aug 15, 2023 at 1:24 PM Pavel Stehule <[email protected]>
wrote:
> Hi
>
> út 15. 8. 2023 v 5:24 odesílatel Andy Fan <[email protected]>
> 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 =
>> makeNullConst(fexpr->funcresulttype,
>> +
>> -1,
>> +
>> InvalidOid);
>> + /*
>> + * Since all the above functions are strict, we
>> can't input
>> + * a NULL value.
>> + */
>> + target->constisnull = false;
>> +
>> + Assert(target->constbyval || target->consttype ==
>> NUMERICOID);
>> +
>> + /* Mock a valid datum for !constbyval type. */
>> + if (fexpr->funcresulttype == NUMERICOID)
>> + target->constvalue =
>> 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).
>
In the patch v8, I created a new routine named makeDummyConst,
which just sits by makeNullConst. It may be helpful to some extent.
a). The code is self-document for the user/reader. b). We have a
central place to maintain this routine.
Besides the framework, the troubles for the reviewer may be if the
code has some corner case issue or behavior changes. Especially
I have some code refactor when working on jsonb_extract_path.
so the attached test.sql is designed for this. I have compared the
result between master and patched version and I think reviewer
can do some extra testing with it.
v8 is the finished version in my mind, so I think it is ready for review
now.
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v8-0001-optimize-casting-jsonb-to-a-given-type.patch.bak (30.0K, 3-v8-0001-optimize-casting-jsonb-to-a-given-type.patch.bak)
download
[application/octet-stream] test.sql (4.4K, 4-test.sql)
download
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWrxHFVZM-gGPpOrVPreZMePAOoY580Tq-+CvxDWHmP_uA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox