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

Tatsuo Ishii ishii at sraoss.co.jp
Tue Jul 5 08:27:33 JST 2022


> Agree but for some reason timestamptz cached and timestamp not.

Yes.

> I believe it’s happened because the selected cast function for timestamp
> has provolatile = “s” and the selected cast function for timestamptz has
> provolatile = “i”.
> 
> Both castType have multiple cast function configured. Those functions that
> are using time zone has provolatile “s” and those are not using time zone
> have provolatile “i”.
> 
> I think in order to fix this issue in a generic way. The solution should
> understand which cast function are fetched by postgres when the client ask
> for casting.

PostgreSQL's parser (and Pgpool-II parser) do not work that way for
type cast. The cast is transformed to a type cast node, which does not
involve function call at all. So looking into pg_proc is not
possible. Probably I can add some codes to handle type cast node to
deal with the issue.

> Thanks,
> 
> Avi
> 
> On Tue, 5 Jul 2022 at 0:38 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> provolatile column of pg_proc has been already considered in the
>> existing code.  See the code block started with "if (IsA(node,
>> FuncCall))" in non_immutable_function_call_walker().
>>
>> >  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.
>> >
>> > That function has condition which compare some value with the pg_catalog
>> so
>> > I thought it’s could be related.
>> >
>> > Thanks,
>> >
>> > Avi
>> >
>> > On Mon, 4 Jul 2022 at 15:34 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >
>> >> 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
>> >> >> >> >>> >>>
>> >> >> >> >>> >>
>> >> >> >> >>>
>> >> >> >> >>
>> >> >> >>
>> >> >>
>> >>
>>


More information about the pgpool-general mailing list