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

[Avi Raboah]

Great, thanks for the update and your efforts.

really appreciate it

Avi.


On Thu, 7 Jul 2022 at 4:53 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20220707/216f620b/attachment.htm>