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

[Tatsuo Ishii]

> I have posted a question to pgsql-hackers regarding to_timestamp.
> https://www.postgresql.org/message-id/flat/20220705.172957.2068967435108479827.t-ishii%40sranhm.sra.co.jp
> 
> It turned out that the provolatile value for the two forms of
> to_timestamp are correct.  Surely to_timestamp(1 argument) returns
> different result depending on the time zone but the actual internal
> value of timestamptz is identical. The output difference is merely how
> the function prints timestamptz according to the time zone.
>     
> Here are examples provided by Tom Lane.
> 
> regression=# show timezone;
>      TimeZone     
> ------------------
>  America/New_York
> (1 row)
> 
> regression=# select to_timestamp(0);
>       to_timestamp      
> ------------------------
>  1969-12-31 19:00:00-05
> (1 row)
> 
> regression=# set timezone = 'utc';
> SET
> regression=# select to_timestamp(0);
>       to_timestamp      
> ------------------------
>  1970-01-01 00:00:00+00
> (1 row)
> 
> "1969-12-31 19:00:00-05" and "1970-01-01 00:00:00+00" are actually
> same value as timestamptz data type, which means labeling this form of
> to_timestamp as immutable is correct.
> 
> However this does not solve the problem of query cache in Pgpool-II.
> 
> The disccusion above suggests that even if the function is labeled as
> immutable, there are cases when pgpool should not create a cache for
> the SELECT which uses the function (you already showed a good
> example). I thinkk pgpool should not create a cache if a function is
> labeled other than immutable (we already do it) and if its return type
> is timestamptz or timetz (we have not done it yet).
> 
> But there's more.
> 
> PostgreSQL has similar config settings that change the output style of
> functions/expressions: namely datestyle and client_encoding etc.
> 
> test=# show datestyle;
>  DateStyle 
> -----------
>  ISO, MDY
> (1 row)
> 
> test=# select '2022-07-06'::date;
>     date    
> ------------
>  2022-07-06
> (1 row)
> 
> test=# set datestyle to 'Postgres, mdy';
> SET
> test=# select '2022-07-06'::date;
>     date    
> ------------
>  07-06-2022
> (1 row)
> 
> If we execute these using pgpool with query cache enabled, we get:
> 
> test=# show datestyle;
>  DateStyle 
> -----------
>  ISO, MDY
> (1 row)
> 
> test=# select '2022-07-06'::date;
>     date    
> ------------
>  2022-07-06
> (1 row)
> 
> test=# set datestyle to 'Postgres, mdy';
> SET
> test=# select '2022-07-06'::date;
>     date    
> ------------
>  2022-07-06
> (1 row)
> 
> Notice the last result does reflect the datestyle setting change
> because of query cache.
> 
> I think there are more config parameters that induce the wrong
> behavior of pgpool:
> 
> IntervalStyle, extra_float_digits, lc_messages, lc_monetary, lc_numeric, lc_time
> 
> For now I don't know how to deal with the problem. Maybe we should
> just add this as a restriction to the doc?

I have pushed commits to partially solve the problem.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=47fd46cbcf31b46d93a8ade62fda43b82c39b4e6

With the commit, SELECTs having functions with return type is
timestamptz or timetz are not cached anymore. This will avoid the
problem due to the time zone setting difference.  Note that the fix
has been only applied to Pgpool-II 4.2 or later. Pgpool-II 4.1 an
before are lacking necessary infrastructures to support the commit.

Remaining problems are:
> IntervalStyle, extra_float_digits, lc_messages, lc_monetary, lc_numeric, lc_time

I think we have to add these to the restriction section of Pgpool-II
doc. I am going work on this later.

Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp