Re: Cached plans and statement generalization

On 12.05.2017 18:23, Bruce Momjian wrote:
> On Fri, May 12, 2017 at 10:50:41AM +0300, Konstantin Knizhnik wrote:
>> Definitely changing session context (search_path, date/time format, ...) may
>> cause incorrect behavior of cached statements.
> I wonder if we should clear the cache whenever any SET command is
> issued.

Well, with autoprepare cache disabled on each set variable, alter system
and any slow utility statement
only one regression test is not passed. And only because of different
error message context:

*** /home/knizhnik/postgresql.master/src/test/regress/expected/date.out
2017-04-11 18:07:56.497461208 +0300
--- /home/knizhnik/postgresql.master/src/test/regress/results/date.out
2017-05-12 20:21:19.767566302 +0300
***************
*** 1443,1452 ****
--
SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- ERROR:
timestamp units "microsec" not recognized
ERROR: timestamp units "microsec" not recognized
- CONTEXT: SQL function "date_part" statement 1
SELECT EXTRACT(UNDEFINED FROM DATE 'infinity'); -- ERROR:
timestamp units "undefined" not supported
ERROR: timestamp units "undefined" not supported
- CONTEXT: SQL function "date_part" statement 1
-- test constructors
select make_date(2013, 7, 15);
make_date
--- 1443,1450 ----

======================================================================

>> Actually you may get the same problem with explicitly prepared statements
>> (certainly, in the last case, you better understand what going on and it is
>> your choice whether to use or not to use prepared statement).
>>
>> The fact of failure of 7 regression tests means that autoprepare can really
>> change behavior of existed program. This is why my suggestion is to switch
>> off this feature by default.
> I would like to see us target something that can be enabled by default.
> Even if it only improves performance by 5%, it would be better overall
> than a feature that improves performance by 90% but is only used by 1%
> of our users.

I have to agree with you here.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company