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

[Avi Raboah]

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/1410d7c4/attachment.htm>