[pgpool-general: 8286] Re: Timestamp cast not cached

[Tatsuo Ishii]

> Hi,
> 
> I am trying to cache the following query:
> Select ‘2022-02-18 07:00:00.006547’::timestamp
> 
> But for some reason this query not cached
> Moreover if I change the timestamp cast to date it cached.
> 
> After checking timestamp or time cast not cached
> 
> Any thoughts? It should work like this?

Good catch! This is not of course an expected behavior. It turned out
that it's a bug with Pgpool-II. Pgpool-II mistakenly thought that the
form of cast is kind of SELECT CURRENT_TIMESTAMP, which should not be
cached of course. The reason why casting to DATE works is, the SQL
parser produces the cast "pg_catalog.timestamp" for TIMESTAMP while
"date" for DATE. The difference is just TIMESTAMP is an SQL keyword
whild DATE is not. Pgpool-II only catches type names with pg_catalog
and DATE cast escapes from the check.

The fix is removing all the consufing code because now (actually since
Pgpool-II 3.7) there's an infrastructure to handle CURRENT_TIMESTAMP
etc. correctly in Pgpool-II and that code is useless anyway.

I am going push the fix to all supported branches of Pgpool-II.

Thank you for the report!

By the way I have attached the parser outputs for TIMESTAMP cast and
DATE cast for those who are interested in the parser behavior. The log
was produced by hacked PostgreSQL 16 dev to print the raw parser
output.

The first one is "select '2022-07-04 12:00:00'::date;" and next one is
"select '2022-07-04 12:00:00'::timestamp".

Notice the difference ":names ("date")" and ":names ("pg_catalog""timestamp")",

2022-07-04 14:49:05.670 JST [872532] LOG:  raw parse tree:
2022-07-04 14:49:05.670 JST [872532] DETAIL:  (
	   {RAWSTMT 
	   :stmt 
	      {SELECT 
	      :distinctClause <> 
	      :intoClause <> 
	      :targetList (
	         {RESTARGET 
	         :name <> 
	         :indirection <> 
	         :val 
	            {TYPECAST 
	            :arg 
	               {A_CONST 
	               :val "\2022-07-04\ 12
	               :00
	               :00" 
	               :location 7
	               }
	            :typeName 
	               {TYPENAME 
	               :names ("date")
	               :typeOid 0 
	               :setof false 
	               :pct_type false 
	               :typmods <> 
	               :typemod -1 
	               :arrayBounds <> 
	               :location 30
	               }
	            :location 28
	            }
	         :location 7
	         }
	      )
	      :fromClause <> 
	      :whereClause <> 
	      :groupClause <> 
	      :groupDistinct false 
	      :havingClause <> 
	      :windowClause <> 
	      :valuesLists <> 
	      :sortClause <> 
	      :limitOffset <> 
	      :limitCount <> 
	      :limitOption 0 
	      :lockingClause <> 
	      :withClause <> 
	      :op 0 
	      :all false 
	      :larg <> 
	      :rarg <>
	      }
	   :stmt_location 0 
	   :stmt_len 34
	   }
	)
	
2022-07-04 14:49:20.708 JST [872532] LOG:  raw parse tree:
2022-07-04 14:49:20.708 JST [872532] DETAIL:  (
	   {RAWSTMT 
	   :stmt 
	      {SELECT 
	      :distinctClause <> 
	      :intoClause <> 
	      :targetList (
	         {RESTARGET 
	         :name <> 
	         :indirection <> 
	         :val 
	            {TYPECAST 
	            :arg 
	               {A_CONST 
	               :val "\2022-07-04\ 12
	               :00
	               :00" 
	               :location 7
	               }
	            :typeName 
	               {TYPENAME 
	               :names ("pg_catalog" "timestamp")
	               :typeOid 0 
	               :setof false 
	               :pct_type false 
	               :typmods <> 
	               :typemod -1 
	               :arrayBounds <> 
	               :location 30
	               }
	            :location 28
	            }
	         :location 7
	         }
	      )
	      :fromClause <> 
	      :whereClause <> 
	      :groupClause <> 
	      :groupDistinct false 
	      :havingClause <> 
	      :windowClause <> 
	      :valuesLists <> 
	      :sortClause <> 
	      :limitOffset <> 
	      :limitCount <> 
	      :limitOption 0 
	      :lockingClause <> 
	      :withClause <> 
	      :op 0 
	      :all false 
	      :larg <> 
	      :rarg <>
	      }
	   :stmt_location 0 
	   :stmt_len 39
	   }
	)

--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp