Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d39a5-0003Rc-BS for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Apr 2017 23:09:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d39a4-0003ao-Rr for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Apr 2017 23:09:12 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d39YZ-0000wK-IG for pgsql-hackers@postgresql.org; Tue, 25 Apr 2017 23:07:39 +0000 Received: from p3plsmtpa07-10.prod.phx3.secureserver.net ([173.201.192.239]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d39YW-0003Cw-St for pgsql-hackers@postgresql.org; Tue, 25 Apr 2017 23:07:38 +0000 Received: from [54.210.254.254] ([54.210.254.254]) by :SMTPAUTH: with SMTP id 39XydTte5WVuO39Y0dNpVw; Tue, 25 Apr 2017 16:07:04 -0700 Content-Type: multipart/alternative; boundary="----sinikael-?=_1-14931616244140.4668821459636092" From: Serge Rielau To: Doug Doole Cc: Andres Freund , Konstantin Knizhnik , Pg Hackers In-Reply-To: References: <1f4c1a22-ecbe-811d-4695-ee4701b8f6c7@postgrespro.ru> <58FFB391.5080101@postgrespro.ru> <58FFB5F0.2020900@postgrespro.ru> <20170425214746.3mngxgbo7yndfgax@alap3.anarazel.de> <20170425223448.amnhdludwondarph@alap3.anarazel.de> Subject: Re: Cached plans and statement generalization Message-Id: <6e24b729-30f0-4bdb-8bc4-549266b0776f@rielau.com> Date: Tue, 25 Apr 2017 16:06:47 -0700 X-Cm-Message-Id: 149316160724936100702de121d333d91abea2c6f477abb958ffd6873ced0666778360 X-Cm-Draft-Id: WyJhIiwzLCJkcmFmdF9pZCIsIjE0OTMxNjE2MDcxODciLCJ2IiwxXQ== X-Cm-Tracking-Code: 2.0/1493161607/647b813270dffd4c4b2631f3c9eb3f46/3/9cf3801d03770ada01bb39dc8f52321d/8ec3be928f3151db911c947d723c80b5/619f5dd6cf0c728abb479c46d4dbfbe4 X-Mailer: Newton MIME-Version: 1.0 X-CMAE-Envelope: MS4wfIXoRTKyHuEDBYQ3wElmUKfe6Rua+c/LCCUdKboYrecHHYNFlyLvAtmV7cb6LzBCW1EbcOgo/yZvf22JJEq8JSR9FkNBAE3uEgKCCtuFz62ImsDmKjoR wFZFZlALhngg5x1rqgOiNDC0yNMAwZBIuakmbmAQLhzDm+c7JZfBae0wG+L1aTUKhmiZzQgmdOUC/g== X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org ------sinikael-?=_1-14931616244140.4668821459636092 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=3Ddx&cv=3D9.4.= 52&pv=3D10.11.6&source=3Demail_footer_2] On Tue, Apr 25, 2017 at 3:48 PM, = Doug Doole wrote: 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 =3D '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? DB2 does handle this case. Unfortunately I don't know the details of how it= worked though. A naive option would be to invalidate anything that depends= on table or view *.FOOBAR. You could probably make it a bit smarter by = also requiring that schema A appear in the path. While this specific = scenario does not arise in DB2 since it uses CURRENT SCHEMA only for tables= (much to my dislike) your examples holds for functions and types which are= resolved by path. For encapsulated SQL (in views, functions) conservative = semantics are enforced via including the timestamp. For dynamic SQL the = problem you describe does exist though and I think it is handled in the way= Doug describes. However, as noted by Doug the topic of plan invalidation = is really orthogonal to normalizing the queries. All it does is provide = more opportunities to run into any pre-existing bugs. Cheers Serge PS: I=E2=80=99m just starting to look at the plan invalidation code in PG = because we are dealing with potentially 10s of thousands of cached SQL = statements. So these complete wipe outs or walks of every plan in the cache= don=E2=80=99t scale. ------sinikael-?=_1-14931616244140.4668821459636092 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable



=
On Tue, Apr 25, 2017 at 3:48 PM, Doug Doole = <ddoole@salesforce.com> wrote:
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 =3D '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?

DB2 does handle this case. = Unfortunately I don't know the details of how it worked though.=

A naive option would be to invalidate anything = that depends on table or view *.FOOBAR. You could probably make it a bit = smarter by also requiring that schema A appear in the path.=
While this = specific scenario does not arise in DB2 since it uses CURRENT SCHEMA only = for tables (much to my dislike) your examples holds for functions and types= which are resolved by path.
For = encapsulated SQL (in views, functions) conservative semantics are enforced = via including the timestamp. For dynamic SQL the problem you describe does = exist though and I think it is handled in the way Doug describes.
    
However, as noted by Doug the topic of plan = invalidation is really orthogonal to normalizing the queries. All it does = is provide more opportunities to run into any pre-existing bugs.

Cheers
=
Serge

PS: I=E2=80=99m just starting to look at = the plan invalidation code in PG because we are dealing with potentially = 10s of thousands of cached SQL statements. So these complete wipe outs or = walks of every plan in the cache don=E2=80=99t scale.
------sinikael-?=_1-14931616244140.4668821459636092--