Re: Cached plans and statement generalization

On 26.04.2017 01:34, Andres Freund wrote:
> Hi,
>
> (FWIW, on this list we don't do top-quotes)
>
> On 2017-04-25 22:21:22 +0000, Doug Doole wrote:
>> Plan invalidation was no different than for any SQL statement. DB2 keeps a
>> list of the objects the statement depends on. If any of the objects changes
>> in an incompatible way the plan is invalidated and kicked out of the cache.
>>
>> I suspect what is more interesting is plan lookup. DB2 has something called
>> the "compilation environment". This is a collection of everything that
>> impacts how a statement is compiled (SQL path, optimization level, etc.).
>> Plan lookup is done using both the statement text and the compilation
>> environment. So, for example, if my path is DOUG, MYTEAM, SYSIBM and your
>> path is ANDRES, MYTEAM, SYSIBM we will have different compilation
>> environments. If we both issue "SELECT * FROM T" we'll end up with
>> different cache entries even if T in both of our statements resolves to
>> MYTEAM.T. If I execute "SELECT * FROM T", change my SQL path and then
>> execute "SELECT * FROM T" again, I have a new compilation environment so
>> the second invocation of the statement will create a new entry in the
>> cache. The first entry is not kicked out - it will still be there for
>> re-use if I change my SQL path back to my original value (modulo LRU for
>> cache memory management of course).
> It's not always that simple, at least in postgres, unless you disregard
> search_path. Consider e.g. cases like
>
> CREATE SCHEMA a;
> CREATE SCHEMA b;
> CREATE TABLE a.foobar(somecol int);
> SET search_patch = 'b,a';
> SELECT * FROM foobar;
> CREATE TABLE b.foobar(anothercol int);
> SELECT * FROM foobar; -- may not be cached plan from before!
>
> it sounds - my memory of DB2 is very faint, and I never used it much -
> like similar issues could arise in DB2 too?

There is the same problem with explicitly prepared statements, isn't it?
Certainly in case of using prepared statements it is responsibility of
programmer to avoid such collisions.
And in case of autoprepare programmer it is hidden from programming.
But there is guc variable controlling autoprepare feature and by default
it is switched off.
So if programmer or DBA enables it, then them should take in account
effects of such decision.

By the way, isn't it a bug in PostgreSQL that altering search path is
not invalidating cached plans?
As I already mentioned, the same problem can be reproduced with
explicitly prepared statements.

>
> Greetings,
>
> Andres Freund

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