<div dir="auto"> I thought the provolatile should be considered. Because I saw in the block code you disable today in order to fix the issue, a method which called isSystemType.</div><div dir="auto"><br></div><div dir="auto">That function has condition which compare some value with the pg_catalog so I thought it’s could be related.</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 Mon, 4 Jul 2022 at 15:34 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 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 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 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 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 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>> 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>> 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, 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>
>> >> >>> >>> <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>
</blockquote></div></div>