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
