[pgpool-general: 8287] Re: Timestamp cast not cached
Avi Raboah
avi.raboah at gmail.com
Mon Jul 4 15:45:23 JST 2022
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>
More information about the pgpool-general
mailing list