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

[Avi Raboah]

One more thing,

I found that the query I mentioned before not cached because a logic in a
method calls - has_non_immutable_code

Or something like that.

Is it still stands with your explanation?

Thanks,

Avi.

On Mon, 4 Jul 2022 at 9:45 Avi Raboah <avi.raboah at gmail.com> wrote:

> Thanks a lot for your fast reaponse!
>
> Do you know when the fix will be available?
>
> Thanks a lot,
>
> Avi.
>
> On Mon, 4 Jul 2022 at 9:34 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
>> > 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
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220704/866ab23f/attachment.htm>