[pgpool-general: 8301] Re: Timestamp cast not cached
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Jul 5 13:19:19 JST 2022
Attached is a patch to not cache timestamptz and timetz cast (should
be applied on top of the previous patch taken from the git repo).
>> 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
>>> >> >> >> >>> >>>
>>> >> >> >> >>> >>
>>> >> >> >> >>>
>>> >> >> >> >>
>>> >> >> >>
>>> >> >>
>>> >>
>>>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cache-timestamp.patch
Type: text/x-patch
Size: 1216 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220705/05cef56a/attachment.bin>
More information about the pgpool-general
mailing list