<div dir="auto">Agree but for some reason timestamptz cached and timestamp not. </div><div dir="auto"><br></div><div dir="auto">I believe it’s happened because the selected cast function for timestamp has provolatile = “s” and the selected cast function for timestamptz has provolatile = “i”.</div><div dir="auto"><br></div><div dir="auto">Both castType have multiple cast function configured. Those functions that are using time zone has provolatile “s” and those are not using time zone have provolatile “i”.</div><div dir="auto"><br></div><div dir="auto">I think in order to fix this issue in a generic way. The solution should understand which cast function are fetched by postgres when the client ask for casting.</div><div dir="auto"><br></div><div dir="auto">I hope it makes sense.</div><div dir="auto"><br></div><div dir="auto">Thanks,</div><div dir="auto"><br></div><div dir="auto">Avi</div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 5 Jul 2022 at 0:38 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)">provolatile column of pg_proc has been already considered in the<br>
existing code. See the code block started with "if (IsA(node,<br>
FuncCall))" in non_immutable_function_call_walker().<br>
<br>
> I thought the provolatile should be considered. Because I saw in the block<br>
> code you disable today in order to fix the issue, a method which called<br>
> isSystemType.<br>
> <br>
> That function has condition which compare some value with the pg_catalog so<br>
> I thought it’s could be related.<br>
> <br>
> Thanks,<br>
> <br>
> Avi<br>
> <br>
> On Mon, 4 Jul 2022 at 15:34 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> provolatile column of pg_proc is not involved here. After PostgreSQL<br>
>> (Pgpool-II) parses "Select ‘2022-02-18<br>
>> 07:00:00.006547+02’::timestamptz;" it produces a parse tree like<br>
>> below. As you can see, there's no function call in it. It is<br>
>> essentially a "type cast" node with type name "timestamptz".<br>
>><br>
>> I think what Pgpool-II needs to do here is, finding a type cast node<br>
>> with its data type "timestamptz" (probably "timetz" should be<br>
>> considered as well). If it finds, mark the SQL as "we should not cache<br>
>> it".<br>
>><br>
>> test=# Select '2022-02-18 07:00:00.006547+02'::timestamptz;<br>
>> 2022-07-04 20:16:13.571 JST [896725] LOG: raw parse tree:<br>
>> 2022-07-04 20:16:13.571 JST [896725] DETAIL: (<br>
>> {RAWSTMT<br>
>> :stmt<br>
>> {SELECT<br>
>> :distinctClause <><br>
>> :intoClause <><br>
>> :targetList (<br>
>> {RESTARGET<br>
>> :name <><br>
>> :indirection <><br>
>> :val<br>
>> {TYPECAST<br>
>> :arg<br>
>> {A_CONST<br>
>> :val "\2022-02-18\ 07<br>
>> :00<br>
>> :00.006547+02"<br>
>> :location 7<br>
>> }<br>
>> :typeName<br>
>> {TYPENAME<br>
>> :names ("timestamptz")<br>
>> :typeOid 0<br>
>> :setof false<br>
>> :pct_type false<br>
>> :typmods <><br>
>> :typemod -1<br>
>> :arrayBounds <><br>
>> :location 40<br>
>> }<br>
>> :location 38<br>
>> }<br>
>> :location 7<br>
>> }<br>
>> )<br>
>> :fromClause <><br>
>> :whereClause <><br>
>> :groupClause <><br>
>> :groupDistinct false<br>
>> :havingClause <><br>
>> :windowClause <><br>
>> :valuesLists <><br>
>> :sortClause <><br>
>> :limitOffset <><br>
>> :limitCount <><br>
>> :limitOption 0<br>
>> :lockingClause <><br>
>> :withClause <><br>
>> :op 0<br>
>> :all false<br>
>> :larg <><br>
>> :rarg <><br>
>> }<br>
>> :stmt_location 0<br>
>> :stmt_len 51<br>
>> }<br>
>> )<br>
>><br>
>> Best reagards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > Maybe it’s not a big issue.<br>
>> ><br>
>> > But more than that the way postgresql using casting function is related<br>
>> to<br>
>> > the procvolatile.<br>
>> ><br>
>> > I believe the solution for pgpool should be related to which casting<br>
>> > function postgres using.<br>
>> ><br>
>> > In case the procvolatile for that cast function is “i” the query should<br>
>> be<br>
>> > cached and if the procvolatile is “s” it shouldn’t.<br>
>> ><br>
>> > But maybe I am wrong.<br>
>> ><br>
>> > Do your magic.<br>
>> ><br>
>> > Thanks,<br>
>> ><br>
>> > Avi<br>
>> ><br>
>> > On Mon, 4 Jul 2022 at 14:36 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>> ><br>
>> >> > Hi,<br>
>> >> ><br>
>> >> > I found some issue with timestamptz cast.<br>
>> >> > See the following:<br>
>> >> ><br>
>> >> > 1.<br>
>> >> ><br>
>> >> > 2. Select ‘2022-02-18 07:00:00.006547+02’::timestamptz; ―> will<br>
>> retrieved<br>
>> >> > from cache<br>
>> >> ><br>
>> >> > 3. Set time zone to ‘Some time zone’;<br>
>> >> ><br>
>> >> > 4. Select ‘2022-02-18 07:00:00.006547+02’::timestamptz; ―> will<br>
>> returned<br>
>> >> > from cache but shouldn’t because the time zone has been changed.<br>
>> >> ><br>
>> >> > I think the right behaviour should be that if we using cast which<br>
>> >> involved<br>
>> >> > timezone like timestamptz or timetz these queries shouldn’t saved in<br>
>> >> cache.<br>
>> >> ><br>
>> >> > What are your thoughts?<br>
>> >><br>
>> >> You are right. Let me think about how to deal with the case.<br>
>> >><br>
>> >> > Thanks,<br>
>> >> ><br>
>> >> > Avi.<br>
>> >> ><br>
>> >> > On Mon, 4 Jul 2022 at 11:41 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>> >> ><br>
>> >> >> Glad to hear that :-)<br>
>> >> >><br>
>> >> >> > My mistake it’s working like a charm :)<br>
>> >> >> ><br>
>> >> >> > On Mon, 4 Jul 2022 at 11:32 Avi Raboah <<a href="mailto:avi.raboah@gmail.com" target="_blank">avi.raboah@gmail.com</a>><br>
>> wrote:<br>
>> >> >> ><br>
>> >> >> >> I added the patch and it still not working.<br>
>> >> >> >> After your change the query<br>
>> >> >> >> Select ‘2022-02-18 07:00:00.006547’::timestamp;<br>
>> >> >> >><br>
>> >> >> >> Still not cached<br>
>> >> >> >><br>
>> >> >> >> On Mon, 4 Jul 2022 at 11:21 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> wrote:<br>
>> >> >> >><br>
>> >> >> >>> Hi,<br>
>> >> >> >>><br>
>> >> >> >>> > Hi,<br>
>> >> >> >>> ><br>
>> >> >> >>> > I saw your patch thanks for that.<br>
>> >> >> >>><br>
>> >> >> >>> You are welcome.<br>
>> >> >> >>><br>
>> >> >> >>> > One question, in order to enable the not_used block you add,<br>
>> Do I<br>
>> >> >> need<br>
>> >> >> >>> to<br>
>> >> >> >>> > define this macro in the same page?<br>
>> >> >> >>><br>
>> >> >> >>> No. You should *not* define NOT_USED symbol. Otherwise, the block<br>
>> >> will<br>
>> >> >> >>> be enabled, which is opposite to what the patch wants to do.<br>
>> >> >> >>><br>
>> >> >> >>> > For example:<br>
>> >> >> >>> > #define NOT_USED<br>
>> >> >> >>> > #ifdef NOT_USED<br>
>> >> >> >>> > …<br>
>> >> >> >>> > …<br>
>> >> >> >>> > #endif<br>
>> >> >> >>> ><br>
>> >> >> >>> > Or I don’t need to add that ?<br>
>> >> >> >>> ><br>
>> >> >> >>> > Thanks,<br>
>> >> >> >>> ><br>
>> >> >> >>> > Avi.<br>
>> >> >> >>> ><br>
>> >> >> >>> > On Mon, 4 Jul 2022 at 9:54 Avi Raboah <<a href="mailto:avi.raboah@gmail.com" target="_blank">avi.raboah@gmail.com</a>><br>
>> >> wrote:<br>
>> >> >> >>> ><br>
>> >> >> >>> >> Awesome, thanks!<br>
>> >> >> >>> >><br>
>> >> >> >>> >> On Mon, 4 Jul 2022 at 9:52 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> >> wrote:<br>
>> >> >> >>> >><br>
>> >> >> >>> >>> > Thanks a lot for your fast reaponse!<br>
>> >> >> >>> >>> ><br>
>> >> >> >>> >>> > Do you know when the fix will be available?<br>
>> >> >> >>> >>><br>
>> >> >> >>> >>> I have just pushed the fix. It will available in the next<br>
>> >> scheduled<br>
>> >> >> >>> >>> release (Aug 18).<br>
>> >> >> >>> >>><br>
>> >> >> >>> >>> <a href="https://pgpool.net/mediawiki/index.php/Roadmap" rel="noreferrer" target="_blank">https://pgpool.net/mediawiki/index.php/Roadmap</a><br>
>> >> >> >>> >>><br>
>> >> >> >>> >>> If you need patches, you can grab from the git repository.<br>
>> >> >> >>> >>><br>
>> >> >> >>> >>><br>
>> <a href="https://pgpool.net/mediawiki/index.php/Source_code_repository" rel="noreferrer" target="_blank">https://pgpool.net/mediawiki/index.php/Source_code_repository</a><br>
>> >> >> >>> >>><br>
>> >> >> >>> >>> Best reagards,<br>
>> >> >> >>> >>> --<br>
>> >> >> >>> >>> Tatsuo Ishii<br>
>> >> >> >>> >>> SRA OSS, Inc. Japan<br>
>> >> >> >>> >>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> >> >>> >>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >> >> >>> >>><br>
>> >> >> >>> >><br>
>> >> >> >>><br>
>> >> >> >><br>
>> >> >><br>
>> >><br>
>><br>
</blockquote></div></div>