[pgpool-general: 9478] Re: Question about usage of cache

[Tatsuo Ishii]

> the cache gets used. However if I change the select by adding date_bin
> which is an immutable function, I then get :
> 
> postgres@[local]/dynacom=# SELECT date_bin(('2025-05-08
> 00:00:00'::timestamp - '2025-05-07 23:59:59')/(100) , utcts,
> '2025-05-07' ) as utctsb  ,  utcts,  tagid FROM tagsdata.tagsrawdata
> WH
> ERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
> '2025-05-07 00:30:00' ;
[snip]
> DEBUG:  checking the function matches the given type name
> DETAIL:  search result = 1 (function name: "date_bin" type name:
> timestamptz)
[snip]
> and it gets sent to the backend . Why is this happening ?

There are two types of date_bin which have different return types:
timestamp with time zone and timestamp without time zone, depending on
the input data types. Unfortunately pgpool is not clever enough to
distinguish them and thinks that your date_bin call is for the one
returning timestamp with time zone, and refuses to create query cache.
Workaround is, create a wrapper function for date_bin which returns
timestamp without time zone and use it in your query.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp