Subject: Re: Cached plans and statement generalization
To: Andres Freund <andres@anarazel.de>, Doug Doole <ddoole@salesforce.com>
References: <f0bfcdd6-dba3-e9a8-2108-146e1f880839@postgrespro.ru>
 <CBF37923-CDA5-48EF-9E9E-4A338D7988ED@rielau.com>
 <1f4c1a22-ecbe-811d-4695-ee4701b8f6c7@postgrespro.ru>
 <e85343e6-5ab5-447c-9995-5cce992fedf3@rielau.com>
 <58FFB391.5080101@postgrespro.ru>
 <C0064A85-7CE9-44E6-B14F-98E541B328FD@rielau.com>
 <58FFB5F0.2020900@postgrespro.ru>
 <CAP6UvaMjiumn5-Lre_w11j=ZAOB9auO1x=-meTKsWRfcsQ7Pcw@mail.gmail.com>
 <20170425214746.3mngxgbo7yndfgax@alap3.anarazel.de>
 <CAP6UvaNvFYprWdghZ4=MFUFLczzj1br9CX+vAQFz8wAGd14yug@mail.gmail.com>
 <20170425223448.amnhdludwondarph@alap3.anarazel.de>
Cc: Serge Rielau <serge@rielau.com>, Pg Hackers <pgsql-hackers@postgresql.org>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <617f73cb-b679-81e3-8c4e-92166ad4d0fc@postgrespro.ru>
Date: Wed, 26 Apr 2017 10:39:05 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: <20170425223448.amnhdludwondarph@alap3.anarazel.de>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers