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

[Tatsuo Ishii]

provolatile column of pg_proc is not involved here.  After PostgreSQL
(Pgpool-II) parses "Select ‘2022-02-18
07:00:00.006547+02’::timestamptz;" it produces a parse tree like
below. As you can see, there's no function call in it.  It is
essentially a "type cast" node with type name "timestamptz".

I think what Pgpool-II needs to do here is, finding a type cast node
with its data type "timestamptz" (probably "timetz" should be
considered as well). If it finds, mark the SQL as "we should not cache
it".

test=# Select '2022-02-18 07:00:00.006547+02'::timestamptz;
2022-07-04 20:16:13.571 JST [896725] LOG:  raw parse tree:
2022-07-04 20:16:13.571 JST [896725] DETAIL:  (
	   {RAWSTMT 
	   :stmt 
	      {SELECT 
	      :distinctClause <> 
	      :intoClause <> 
	      :targetList (
	         {RESTARGET 
	         :name <> 
	         :indirection <> 
	         :val 
	            {TYPECAST 
	            :arg 
	               {A_CONST 
	               :val "\2022-02-18\ 07
	               :00
	               :00.006547+02" 
	               :location 7
	               }
	            :typeName 
	               {TYPENAME 
	               :names ("timestamptz")
	               :typeOid 0 
	               :setof false 
	               :pct_type false 
	               :typmods <> 
	               :typemod -1 
	               :arrayBounds <> 
	               :location 40
	               }
	            :location 38
	            }
	         :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 51
	   }
	)
	
Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Maybe it’s not a big issue.
> 
> But more than that the way postgresql using casting function is related to
> the procvolatile.
> 
> I believe the solution for pgpool should be related to which casting
> function postgres using.
> 
> In case the procvolatile for that cast function is “i” the query should be
> cached and if the procvolatile is “s” it shouldn’t.
> 
> But maybe I am wrong.
> 
> Do your magic.
> 
> Thanks,
> 
> Avi
> 
> On Mon, 4 Jul 2022 at 14:36 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > Hi,
>> >
>> > I found some issue with timestamptz cast.
>> > See the following:
>> >
>> > 1. 
>> >
>> > 2. Select ‘2022-02-18 07:00:00.006547+02’::timestamptz; ―> will retrieved
>> > from cache
>> >
>> > 3. Set time zone to ‘Some time zone’;
>> >
>> > 4. Select ‘2022-02-18 07:00:00.006547+02’::timestamptz; ―> will returned
>> > from cache but shouldn’t because the time zone has been changed.
>> >
>> > I think the right behaviour should be that if we using cast which
>> involved
>> > timezone like timestamptz or timetz these queries shouldn’t saved in
>> cache.
>> >
>> > What are your thoughts?
>>
>> You are right. Let me think about how to deal with the case.
>>
>> > Thanks,
>> >
>> > Avi.
>> >
>> > On Mon, 4 Jul 2022 at 11:41 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >
>> >> Glad to hear that :-)
>> >>
>> >> > My mistake it’s working like a charm :)
>> >> >
>> >> > On Mon, 4 Jul 2022 at 11:32 Avi Raboah <avi.raboah at gmail.com> wrote:
>> >> >
>> >> >> I added the patch and it still not working.
>> >> >> After your change the query
>> >> >> Select ‘2022-02-18 07:00:00.006547’::timestamp;
>> >> >>
>> >> >> Still not cached
>> >> >>
>> >> >> On Mon, 4 Jul 2022 at 11:21 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >> >>
>> >> >>> Hi,
>> >> >>>
>> >> >>> > Hi,
>> >> >>> >
>> >> >>> > I saw your patch thanks for that.
>> >> >>>
>> >> >>> You are welcome.
>> >> >>>
>> >> >>> > One question, in order to enable the not_used block you add, Do I
>> >> need
>> >> >>> to
>> >> >>> > define this macro in the same page?
>> >> >>>
>> >> >>> No. You should *not* define NOT_USED symbol. Otherwise, the block
>> will
>> >> >>> be enabled, which is opposite to what the patch wants to do.
>> >> >>>
>> >> >>> > For example:
>> >> >>> > #define NOT_USED
>> >> >>> > #ifdef NOT_USED
>> >> >>> > …
>> >> >>> > …
>> >> >>> > #endif
>> >> >>> >
>> >> >>> > Or I don’t need to add that ?
>> >> >>> >
>> >> >>> > Thanks,
>> >> >>> >
>> >> >>> > Avi.
>> >> >>> >
>> >> >>> > On Mon, 4 Jul 2022 at 9:54 Avi Raboah <avi.raboah at gmail.com>
>> wrote:
>> >> >>> >
>> >> >>> >> Awesome, thanks!
>> >> >>> >>
>> >> >>> >> On Mon, 4 Jul 2022 at 9:52 Tatsuo Ishii <ishii at sraoss.co.jp>
>> wrote:
>> >> >>> >>
>> >> >>> >>> > Thanks a lot for your fast reaponse!
>> >> >>> >>> >
>> >> >>> >>> > Do you know when the fix will be available?
>> >> >>> >>>
>> >> >>> >>> I have just pushed the fix. It will available in the next
>> scheduled
>> >> >>> >>> release (Aug 18).
>> >> >>> >>>
>> >> >>> >>> https://pgpool.net/mediawiki/index.php/Roadmap
>> >> >>> >>>
>> >> >>> >>> If you need patches, you can grab from the git repository.
>> >> >>> >>>
>> >> >>> >>> https://pgpool.net/mediawiki/index.php/Source_code_repository
>> >> >>> >>>
>> >> >>> >>> Best reagards,
>> >> >>> >>> --
>> >> >>> >>> Tatsuo Ishii
>> >> >>> >>> SRA OSS, Inc. Japan
>> >> >>> >>> English: http://www.sraoss.co.jp/index_en.php
>> >> >>> >>> Japanese:http://www.sraoss.co.jp
>> >> >>> >>>
>> >> >>> >>
>> >> >>>
>> >> >>
>> >>
>>