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