<div dir="auto">Great, thanks for the update and your efforts.</div><div dir="auto"><br></div><div dir="auto">really appreciate it </div><div dir="auto"><br></div><div dir="auto">Avi.</div><div dir="auto"><br></div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, 7 Jul 2022 at 4:53 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)">> I have posted a question to pgsql-hackers regarding to_timestamp.<br>
> <a href="https://www.postgresql.org/message-id/flat/20220705.172957.2068967435108479827.t-ishii%40sranhm.sra.co.jp" rel="noreferrer" target="_blank">https://www.postgresql.org/message-id/flat/20220705.172957.2068967435108479827.t-ishii%40sranhm.sra.co.jp</a><br>
> <br>
> It turned out that the provolatile value for the two forms of<br>
> to_timestamp are correct. Surely to_timestamp(1 argument) returns<br>
> different result depending on the time zone but the actual internal<br>
> value of timestamptz is identical. The output difference is merely how<br>
> the function prints timestamptz according to the time zone.<br>
> <br>
> Here are examples provided by Tom Lane.<br>
> <br>
> regression=# show timezone;<br>
> TimeZone <br>
> ------------------<br>
> America/New_York<br>
> (1 row)<br>
> <br>
> regression=# select to_timestamp(0);<br>
> to_timestamp <br>
> ------------------------<br>
> 1969-12-31 19:00:00-05<br>
> (1 row)<br>
> <br>
> regression=# set timezone = 'utc';<br>
> SET<br>
> regression=# select to_timestamp(0);<br>
> to_timestamp <br>
> ------------------------<br>
> 1970-01-01 00:00:00+00<br>
> (1 row)<br>
> <br>
> "1969-12-31 19:00:00-05" and "1970-01-01 00:00:00+00" are actually<br>
> same value as timestamptz data type, which means labeling this form of<br>
> to_timestamp as immutable is correct.<br>
> <br>
> However this does not solve the problem of query cache in Pgpool-II.<br>
> <br>
> The disccusion above suggests that even if the function is labeled as<br>
> immutable, there are cases when pgpool should not create a cache for<br>
> the SELECT which uses the function (you already showed a good<br>
> example). I thinkk pgpool should not create a cache if a function is<br>
> labeled other than immutable (we already do it) and if its return type<br>
> is timestamptz or timetz (we have not done it yet).<br>
> <br>
> But there's more.<br>
> <br>
> PostgreSQL has similar config settings that change the output style of<br>
> functions/expressions: namely datestyle and client_encoding etc.<br>
> <br>
> test=# show datestyle;<br>
> DateStyle <br>
> -----------<br>
> ISO, MDY<br>
> (1 row)<br>
> <br>
> test=# select '2022-07-06'::date;<br>
> date <br>
> ------------<br>
> 2022-07-06<br>
> (1 row)<br>
> <br>
> test=# set datestyle to 'Postgres, mdy';<br>
> SET<br>
> test=# select '2022-07-06'::date;<br>
> date <br>
> ------------<br>
> 07-06-2022<br>
> (1 row)<br>
> <br>
> If we execute these using pgpool with query cache enabled, we get:<br>
> <br>
> test=# show datestyle;<br>
> DateStyle <br>
> -----------<br>
> ISO, MDY<br>
> (1 row)<br>
> <br>
> test=# select '2022-07-06'::date;<br>
> date <br>
> ------------<br>
> 2022-07-06<br>
> (1 row)<br>
> <br>
> test=# set datestyle to 'Postgres, mdy';<br>
> SET<br>
> test=# select '2022-07-06'::date;<br>
> date <br>
> ------------<br>
> 2022-07-06<br>
> (1 row)<br>
> <br>
> Notice the last result does reflect the datestyle setting change<br>
> because of query cache.<br>
> <br>
> I think there are more config parameters that induce the wrong<br>
> behavior of pgpool:<br>
> <br>
> IntervalStyle, extra_float_digits, lc_messages, lc_monetary, lc_numeric, lc_time<br>
> <br>
> For now I don't know how to deal with the problem. Maybe we should<br>
> just add this as a restriction to the doc?<br>
<br>
I have pushed commits to partially solve the problem.<br>
<a href="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=47fd46cbcf31b46d93a8ade62fda43b82c39b4e6" rel="noreferrer" target="_blank">https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=47fd46cbcf31b46d93a8ade62fda43b82c39b4e6</a><br>
<br>
With the commit, SELECTs having functions with return type is<br>
timestamptz or timetz are not cached anymore. This will avoid the<br>
problem due to the time zone setting difference. Note that the fix<br>
has been only applied to Pgpool-II 4.2 or later. Pgpool-II 4.1 an<br>
before are lacking necessary infrastructures to support the commit.<br>
<br>
Remaining problems are:<br>
> IntervalStyle, extra_float_digits, lc_messages, lc_monetary, lc_numeric, lc_time<br>
<br>
I think we have to add these to the restriction section of Pgpool-II<br>
doc. I am going work on this later.<br>
<br>
Best reagards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
</blockquote></div></div>