<div dir="auto">I am start to think that the first fix you suggested to just remove the condition to check the type cast is the best.</div><div dir="auto"><br></div><div dir="auto">And I shared the consultation with you because you are the expert and I wanted to hear your opinion.</div><div dir="auto"><br></div><div dir="auto">Thanks,</div><div dir="auto"><br></div><div dir="auto">Avi</div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, 11 Jul 2022 at 11:39 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)">Thank you for the thought. However I have no clear idea what you<br>
suggest here to enhance pgpool in terms of query cache. Can you<br>
clarify more?<br>
<br>
> Hi after a consultation with some pg expert that what they answer to me.<br>
> Maybe it could help you with your fix.<br>
> <br>
> Hi Avi Raboah,<br>
> <br>
> To expand on Andrew’s answer, we need to explore the difference between<br>
> IMMUTABLE, STABLE and VOLATILE declared function types in PostgreSQL<br>
> <br>
> IMMUTABLE functions must adhere to three requirements:<br>
> # Not change the database during execution.<br>
> # Not execute any database lookups. Can only use values passed as arguments.<br>
> # Always return the same value when supplied the same arguments.<br>
> <br>
> STABLE functions must adhere to two requirements:<br>
> # Not change the database during execution.<br>
> # Always return the same values when supplied the same arguments during a<br>
> single table scan. However, the return values can change during execution<br>
> of later SQL statements within a transaction.<br>
> <br>
> VOLATILE functions have no ridge requirements, and it is the default type<br>
> for functions.<br>
> <br>
> What does the above mean? PostgreSQL uses the three function types to<br>
> decide on optimization paths to improve performance.<br>
> <br>
> Immutable functions offer the most optimization options as PostgreSQL can<br>
> inline and cache the results of a function call during execution. Example:<br>
> <br>
> <pre><code class="sql"><br>
> CREATE TEMPORARY TABLE test (id integer, tt text);<br>
> <br>
> INSERT INTO test (SELECT (random() * 10)::integer, random()::text FROM<br>
> GENERATE_SERIES(0,999));<br>
> <br>
> EXPLAIN SELECT true FROM test WHERE id = 2+2 ;<br>
> --returns<br>
> --Seq Scan on test (cost=0.00..19.50 rows=88 width=1)<br>
> -- Filter: (id = 4)<br>
> </code></pre><br>
> <br>
> We can see the FILTER condition was executed in-lining the filtering<br>
> condition on the value 4, not on 2 + 2. PostgreSQL rewrote the query to use<br>
> a static value of 4.<br>
> <br>
> <pre><code class="sql"><br>
> SELECT true FROM test WHERE id = 4<br>
> </code></pre><br>
> <br>
> PostgreSQL can do this as the + operator is marked as immutable. This saved<br>
> executing the + operator an additional 999 times when comparing id to 2+2.<br>
> <br>
> Stable functions have similar properties to immutable functions, which<br>
> allows PostgreSQL to cache and inline results while executing a table scan<br>
> to avoid executing costly functions multiple times when scanning thousands<br>
> to millions of rows. However, a stable function must be executed again<br>
> later in a transaction. Example:<br>
> <br>
> <pre><code class="sql"><br>
> BEGIN;<br>
> <br>
> SELECT statement_timestamp(), now();<br>
> SELECT pg_sleep(2);<br>
> SELECT statement_timestamp(), now();<br>
> <br>
> ROLLBACK;<br>
> </code></pre><br>
> <br>
> <br>
> With a volatile function, PostgreSQL cannot make optimization decisions and<br>
> must execute the function every time it is called. This makes volatile<br>
> functions very expensive if used in a table scan, for example.<br>
> <br>
> <br>
> <pre><code class="sql"><br>
> SELECT timeofday() as volatile_time, NOW() stable_time_during_transaction<br>
> FROM generate_series(0,999)<br>
> </code></pre><br>
> <br>
> This creates a thousand row result set where TIMEOFDAY () is changing<br>
> while NOW () is stable and does not change during a transaction.<br>
> <br>
> PostgreSQL functions marked as stable and immutable will always return that<br>
> same value when the same arguments are supplied. They’re not going to be<br>
> volatile, returning an unexpected value.<br>
> <br>
> The three function types Immutable, Stable, Volatile are used by the<br>
> planner to decide which execution plan to create to improve performance.<br>
> <br>
> When would an IMMUTABLE or STABLE function become unstable? An immutable<br>
> function will not become unstable as long as the same arguments are<br>
> supplied. A stable function can become unstable if the function does<br>
> database lookups that changes its behavior. This is the reason stable<br>
> functions have to be executed in later SQL commands in a transaction, while<br>
> IMMUTABLE functions do not.<br>
> <br>
> How do we know which function PostgreSQL chooses to execute/use? PostgreSQL<br>
> will search through the catalog for matching function name, number of<br>
> arguments, argument types, if arguments have default values and will order<br>
> the functions by the session’s SEARCH_PATH. PostgreSQL will use the first<br>
> function it finds that matches all the above requirements.<br>
> <br>
> Leaving out the precision for casting operation, PostgreSQL will match to a<br>
> different function as the arguments can change as TIMESTAMP can have<br>
> variable precision, making the output unstable. This limits optimization<br>
> paths and will cause PostgreSQL to execute the function more times during a<br>
> table scan vs the immutable version of the function.<br>
> <br>
> This leads us to Andrew’s statement about sanitizing the text input for<br>
> security reasons to protect against SQL injection and prevent PostgreSQL<br>
> from executing IMMUTABLE and STABLE functions multiple times as the input<br>
> will be variable.<br>
> <br>
> In summary, the immutable, stable, and volatile type functions are used by<br>
> the planner to decide optimization paths; it does not guarantee/guard<br>
> unexpected behavior. However, if the arguments passed to the function are<br>
> consistent, the results will be immutable for immutable functions. Stable<br>
> functions depend on the specific function. Volatile functions should be<br>
> assumed to be unstable.<br>
> <br>
> Thanks,<br>
> <br>
> Avi.<br>
> <br>
> <br>
> On Thu, 7 Jul 2022 at 15:06 Avi Raboah <<a href="mailto:avi.raboah@gmail.com" target="_blank">avi.raboah@gmail.com</a>> wrote:<br>
> <br>
>> Great, thanks for the update and your efforts.<br>
>><br>
>> really appreciate it<br>
>><br>
>> Avi.<br>
>><br>
>><br>
>> On Thu, 7 Jul 2022 at 4:53 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>><br>
>>> > I have posted a question to pgsql-hackers regarding to_timestamp.<br>
>>> ><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,<br>
>>> 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>
>>><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,<br>
>>> 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>
>>><br>
>><br>
</blockquote></div></div>