<div dir="auto">One more thing,</div><div dir="auto"><br></div><div dir="auto">I found that the query I mentioned before not cached because a logic in a method calls - has_non_immutable_code</div><div dir="auto"><br></div><div dir="auto">Or something like that. </div><div style="direction:rtl" dir="auto"><br></div><div style="direction:ltr" dir="auto">Is it still stands with your explanation?</div><div style="direction:ltr" dir="auto"><br></div><div style="direction:ltr" dir="auto">Thanks, </div><div style="direction:ltr" dir="auto"><br></div><div style="direction:ltr" dir="auto">Avi.</div><div style="direction:ltr" dir="auto"><br></div><div><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, 4 Jul 2022 at 9:45 Avi Raboah <<a href="mailto:avi.raboah@gmail.com">avi.raboah@gmail.com</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)"><div dir="auto">Thanks a lot for your fast reaponse!</div><div dir="auto"><br></div><div dir="auto">Do you know when the fix will be available? </div><div dir="auto"><br></div><div dir="auto">Thanks a lot,</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 9:34 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">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)">> Hi,<br>
> <br>
> I am trying to cache the following query:<br>
> Select ‘2022-02-18 07:00:00.006547’::timestamp<br>
> <br>
> But for some reason this query not cached<br>
> Moreover if I change the timestamp cast to date it cached.<br>
> <br>
> After checking timestamp or time cast not cached<br>
> <br>
> Any thoughts? It should work like this?<br>
<br>
Good catch! This is not of course an expected behavior. It turned out<br>
that it's a bug with Pgpool-II. Pgpool-II mistakenly thought that the<br>
form of cast is kind of SELECT CURRENT_TIMESTAMP, which should not be<br>
cached of course. The reason why casting to DATE works is, the SQL<br>
parser produces the cast "pg_catalog.timestamp" for TIMESTAMP while<br>
"date" for DATE. The difference is just TIMESTAMP is an SQL keyword<br>
whild DATE is not. Pgpool-II only catches type names with pg_catalog<br>
and DATE cast escapes from the check.<br>
<br>
The fix is removing all the consufing code because now (actually since<br>
Pgpool-II 3.7) there's an infrastructure to handle CURRENT_TIMESTAMP<br>
etc. correctly in Pgpool-II and that code is useless anyway.<br>
<br>
I am going push the fix to all supported branches of Pgpool-II.<br>
<br>
Thank you for the report!<br>
<br>
By the way I have attached the parser outputs for TIMESTAMP cast and<br>
DATE cast for those who are interested in the parser behavior. The log<br>
was produced by hacked PostgreSQL 16 dev to print the raw parser<br>
output.<br>
<br>
The first one is "select '2022-07-04 12:00:00'::date;" and next one is<br>
"select '2022-07-04 12:00:00'::timestamp".<br>
<br>
Notice the difference ":names ("date")" and ":names ("pg_catalog""timestamp")",<br>
<br>
2022-07-04 14:49:05.670 JST [872532] LOG: raw parse tree:<br>
2022-07-04 14:49:05.670 JST [872532] 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-07-04\ 12<br>
:00<br>
:00" <br>
:location 7<br>
}<br>
:typeName <br>
{TYPENAME <br>
:names ("date")<br>
:typeOid 0 <br>
:setof false <br>
:pct_type false <br>
:typmods <> <br>
:typemod -1 <br>
:arrayBounds <> <br>
:location 30<br>
}<br>
:location 28<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 34<br>
}<br>
)<br>
<br>
2022-07-04 14:49:20.708 JST [872532] LOG: raw parse tree:<br>
2022-07-04 14:49:20.708 JST [872532] 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-07-04\ 12<br>
:00<br>
:00" <br>
:location 7<br>
}<br>
:typeName <br>
{TYPENAME <br>
:names ("pg_catalog" "timestamp")<br>
:typeOid 0 <br>
:setof false <br>
:pct_type false <br>
:typmods <> <br>
:typemod -1 <br>
:arrayBounds <> <br>
:location 30<br>
}<br>
:location 28<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 39<br>
}<br>
)<br>
<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>
</blockquote></div></div>
</blockquote></div></div>