pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Serge Rielau <serge@rielau.com>
To: Doug Doole <ddoole@salesforce.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Cached plans and statement generalization
Date: Tue, 25 Apr 2017 16:06:47 -0700
Message-ID: <6e24b729-30f0-4bdb-8bc4-549266b0776f@rielau.com> (raw)
In-Reply-To: <CAP6UvaO9bS8+U-Upt-1Fkn7tgUyNW_BiCFbh9a4LgcHqh84kQw@mail.gmail.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>
	<CAP6UvaO9bS8+U-Upt-1Fkn7tgUyNW_BiCFbh9a4LgcHqh84kQw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

via Newton Mail [https://cloudmagic.com/k/d/mailapp?ct=dx&cv=9.4.52&pv=10.11.6&source=email_footer_2]
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 = '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’m 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’t scale.

view thread (107+ messages)  latest in thread

Message-ID: <6e24b729-30f0-4bdb-8bc4-549266b0776f@rielau.com>
Permalink:  ../6e24b729-30f0-4bdb-8bc4-549266b0776f@rielau.com/
Also on:    postgresql.org/message-id/6e24b729-30f0-4bdb-8bc4-549266b0776f@rielau.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: serge@rielau.com, ddoole@salesforce.com, andres@anarazel.de, k.knizhnik@postgrespro.ru
  Subject: Re: Cached plans and statement generalization
  In-Reply-To: <6e24b729-30f0-4bdb-8bc4-549266b0776f@rielau.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox