Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0d5fbdc

Browse files
committed
Change plan caching to honor, not resist, changes in search_path.
In the initial implementation of plan caching, we saved the activesearch_path when a plan was first cached, then reinstalled that pathanytime we needed to reparse or replan. The idea of that was to try toreselect the same referenced objects, in somewhat the same way that viewscontinue to refer to the same objects in the face of schema or namechanges. Of course, that analogy doesn't bear close inspection, sinceholding the search_path fixed doesn't cope with object drops or renames.Moreover sticking with the old path seems to create more surprises thanit avoids. So instead of doing that, consider that the cached plan dependson search_path, and force reparse/replan if the active search_path isdifferent than it was when we last saved the plan.This gets us fairly close to having "transparency" of plan caching, in thesense that the cached statement acts the same as if you'd just resubmittedthe original query text for another execution. There are still some cornercases where this fails though: a new object added in the search pathschema(s) might capture a reference in the query text, but we'd not realizethat and force a reparse. We might try to fix that in the future, but forthe moment it looks too expensive and complicated.
1 parentd309be0 commit0d5fbdc

File tree

9 files changed

+111
-47
lines changed

9 files changed

+111
-47
lines changed

‎doc/src/sgml/plpgsql.sgml‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4258,7 +4258,9 @@ $$ LANGUAGE plpgsql;
42584258
on specific parameter values, and caching that for re-use. Typically
42594259
this will happen only if the execution plan is not very sensitive to
42604260
the values of the <application>PL/pgSQL</> variables referenced in it.
4261-
If it is, generating a plan each time is a net win.
4261+
If it is, generating a plan each time is a net win. See <xref
4262+
linkend="sql-prepare"> for more information about the behavior of
4263+
prepared statements.
42624264
</para>
42634265

42644266
<para>

‎doc/src/sgml/ref/prepare.sgml‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -152,6 +152,28 @@ PREPARE <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class
152152
documentation.
153153
</para>
154154

155+
<para>
156+
Although the main point of a prepared statement is to avoid repeated parse
157+
analysis and planning of the statement, <productname>PostgreSQL</> will
158+
force re-analysis and re-planning of the statement before using it
159+
whenever database objects used in the statement have undergone
160+
definitional (DDL) changes since the previous use of the prepared
161+
statement. Also, if the value of <xref linkend="guc-search-path"> changes
162+
from one use to the next, the statement will be re-parsed using the new
163+
<varname>search_path</>. (This latter behavior is new as of
164+
<productname>PostgreSQL</productname> 9.3.) These rules make use of a
165+
prepared statement semantically almost equivalent to re-submitting the
166+
same query text over and over, but with a performance benefit if no object
167+
definitions are changed, especially if the best plan remains the same
168+
across uses. An example of a case where the semantic equivalence is not
169+
perfect is that if the statement refers to a table by an unqualified name,
170+
and then a new table of the same name is created in a schema appearing
171+
earlier in the <varname>search_path</>, no automatic re-parse will occur
172+
since no object used in the statement changed. However, if some other
173+
change forces a re-parse, the new table will be referenced in subsequent
174+
uses.
175+
</para>
176+
155177
<para>
156178
You can see all prepared statements available in the session by querying the
157179
<link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link>

‎doc/src/sgml/spi.sgml‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -975,6 +975,20 @@ SPIPlanPtr SPI_prepare(const char * <parameter>command</parameter>, int <paramet
975975
plans respectively.
976976
</para>
977977

978+
<para>
979+
Although the main point of a prepared statement is to avoid repeated parse
980+
analysis and planning of the statement, <productname>PostgreSQL</> will
981+
force re-analysis and re-planning of the statement before using it
982+
whenever database objects used in the statement have undergone
983+
definitional (DDL) changes since the previous use of the prepared
984+
statement. Also, if the value of <xref linkend="guc-search-path"> changes
985+
from one use to the next, the statement will be re-parsed using the new
986+
<varname>search_path</>. (This latter behavior is new as of
987+
<productname>PostgreSQL</productname> 9.3.) See <xref
988+
linkend="sql-prepare"> for more information about the behavior of prepared
989+
statements.
990+
</para>
991+
978992
<para>
979993
This function should only be called from a connected procedure.
980994
</para>

‎src/backend/catalog/namespace.c‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3096,6 +3096,28 @@ CopyOverrideSearchPath(OverrideSearchPath *path)
30963096
returnresult;
30973097
}
30983098

3099+
/*
3100+
* OverrideSearchPathMatchesCurrent - does path match current setting?
3101+
*/
3102+
bool
3103+
OverrideSearchPathMatchesCurrent(OverrideSearchPath*path)
3104+
{
3105+
/* Easiest way to do this is GetOverrideSearchPath() and compare */
3106+
boolresult;
3107+
OverrideSearchPath*cur;
3108+
3109+
cur=GetOverrideSearchPath(CurrentMemoryContext);
3110+
if (path->addCatalog==cur->addCatalog&&
3111+
path->addTemp==cur->addTemp&&
3112+
equal(path->schemas,cur->schemas))
3113+
result= true;
3114+
else
3115+
result= false;
3116+
list_free(cur->schemas);
3117+
pfree(cur);
3118+
returnresult;
3119+
}
3120+
30993121
/*
31003122
* PushOverrideSearchPath - temporarily override the search path
31013123
*

‎src/backend/utils/cache/plancache.c‎

Lines changed: 43 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -15,13 +15,15 @@
1515
* that matches the event is marked invalid, as is its generic CachedPlan
1616
* if it has one. When (and if) the next demand for a cached plan occurs,
1717
* parse analysis and rewrite is repeated to build a new valid query tree,
18-
* and then planning is performed as normal.
18+
* and then planning is performed as normal. We also force re-analysis and
19+
* re-planning if the active search_path is different from the previous time.
1920
*
2021
* Note that if the sinval was a result of user DDL actions, parse analysis
2122
* could throw an error, for example if a column referenced by the query is
22-
* no longer present. The creator of a cached plan can specify whether it
23-
* is allowable for the query to change output tupdesc on replan (this
24-
* could happen with "SELECT *" for example) --- if so, it's up to the
23+
* no longer present. Another possibility is for the query's output tupdesc
24+
* to change (for instance "SELECT *" might expand differently than before).
25+
* The creator of a cached plan can specify whether it is allowable for the
26+
* query to change output tupdesc on replan --- if so, it's up to the
2527
* caller to notice changes and cope with them.
2628
*
2729
* Currently, we track exactly the dependencies of plans on relations and
@@ -174,11 +176,11 @@ CreateCachedPlan(Node *raw_parse_tree,
174176
plansource->cursor_options=0;
175177
plansource->fixed_result= false;
176178
plansource->resultDesc=NULL;
177-
plansource->search_path=NULL;
178179
plansource->context=source_context;
179180
plansource->query_list=NIL;
180181
plansource->relationOids=NIL;
181182
plansource->invalItems=NIL;
183+
plansource->search_path=NULL;
182184
plansource->query_context=NULL;
183185
plansource->gplan=NULL;
184186
plansource->is_oneshot= false;
@@ -239,11 +241,11 @@ CreateOneShotCachedPlan(Node *raw_parse_tree,
239241
plansource->cursor_options=0;
240242
plansource->fixed_result= false;
241243
plansource->resultDesc=NULL;
242-
plansource->search_path=NULL;
243244
plansource->context=CurrentMemoryContext;
244245
plansource->query_list=NIL;
245246
plansource->relationOids=NIL;
246247
plansource->invalItems=NIL;
248+
plansource->search_path=NULL;
247249
plansource->query_context=NULL;
248250
plansource->gplan=NULL;
249251
plansource->is_oneshot= true;
@@ -360,6 +362,14 @@ CompleteCachedPlan(CachedPlanSource *plansource,
360362
&plansource->relationOids,
361363
&plansource->invalItems);
362364

365+
/*
366+
* Also save the current search_path in the query_context. (This should
367+
* not generate much extra cruft either, since almost certainly the path
368+
* is already valid.) Again, don't really need it for one-shot plans.
369+
*/
370+
if (!plansource->is_oneshot)
371+
plansource->search_path=GetOverrideSearchPath(querytree_context);
372+
363373
/*
364374
* Save the final parameter types (or other parameter specification data)
365375
* into the source_context, as well as our other parameters. Also save
@@ -383,12 +393,6 @@ CompleteCachedPlan(CachedPlanSource *plansource,
383393

384394
MemoryContextSwitchTo(oldcxt);
385395

386-
/*
387-
* Fetch current search_path into dedicated context, but do any
388-
* recalculation work required in caller's context.
389-
*/
390-
plansource->search_path=GetOverrideSearchPath(source_context);
391-
392396
plansource->is_complete= true;
393397
plansource->is_valid= true;
394398
}
@@ -546,6 +550,23 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
546550
returnNIL;
547551
}
548552

553+
/*
554+
* If the query is currently valid, we should have a saved search_path ---
555+
* check to see if that matches the current environment. If not, we want
556+
* to force replan.
557+
*/
558+
if (plansource->is_valid)
559+
{
560+
Assert(plansource->search_path!=NULL);
561+
if (!OverrideSearchPathMatchesCurrent(plansource->search_path))
562+
{
563+
/* Invalidate the querytree and generic plan */
564+
plansource->is_valid= false;
565+
if (plansource->gplan)
566+
plansource->gplan->is_valid= false;
567+
}
568+
}
569+
549570
/*
550571
* If the query is currently valid, acquire locks on the referenced
551572
* objects; then check again. We need to do it this way to cover the race
@@ -578,6 +599,7 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
578599
plansource->query_list=NIL;
579600
plansource->relationOids=NIL;
580601
plansource->invalItems=NIL;
602+
plansource->search_path=NULL;
581603

582604
/*
583605
* Free the query_context.We don't really expect MemoryContextDelete to
@@ -602,14 +624,6 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
602624
*/
603625
Assert(plansource->is_complete);
604626

605-
/*
606-
* Restore the search_path that was in use when the plan was made. See
607-
* comments for PushOverrideSearchPath about limitations of this.
608-
*
609-
* (XXX is there anything else we really need to restore?)
610-
*/
611-
PushOverrideSearchPath(plansource->search_path);
612-
613627
/*
614628
* If a snapshot is already set (the normal case), we can just use that
615629
* for parsing/planning. But if it isn't, install one. Note: no point in
@@ -645,9 +659,6 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
645659
if (snapshot_set)
646660
PopActiveSnapshot();
647661

648-
/* Now we can restore current search path */
649-
PopOverrideSearchPath();
650-
651662
/*
652663
* Check or update the result tupdesc.XXX should we use a weaker
653664
* condition than equalTupleDescs() here?
@@ -699,6 +710,13 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
699710
&plansource->relationOids,
700711
&plansource->invalItems);
701712

713+
/*
714+
* Also save the current search_path in the query_context. (This should
715+
* not generate much extra cruft either, since almost certainly the path
716+
* is already valid.)
717+
*/
718+
plansource->search_path=GetOverrideSearchPath(querytree_context);
719+
702720
MemoryContextSwitchTo(oldcxt);
703721

704722
/* Now reparent the finished query_context and save the links */
@@ -848,20 +866,6 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
848866
qlist=plansource->query_list;
849867
}
850868

851-
/*
852-
* Restore the search_path that was in use when the plan was made. See
853-
* comments for PushOverrideSearchPath about limitations of this.
854-
*
855-
* (XXX is there anything else we really need to restore?)
856-
*
857-
* Note: it's a bit annoying to do this and snapshot-setting twice in the
858-
* case where we have to do both re-analysis and re-planning. However,
859-
* until there's some evidence that the cost is actually meaningful
860-
* compared to parse analysis + planning, I'm not going to contort the
861-
* code enough to avoid that.
862-
*/
863-
PushOverrideSearchPath(plansource->search_path);
864-
865869
/*
866870
* If a snapshot is already set (the normal case), we can just use that
867871
* for planning. But if it isn't, and we need one, install one.
@@ -894,9 +898,6 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
894898
if (snapshot_set)
895899
PopActiveSnapshot();
896900

897-
/* Now we can restore current search path */
898-
PopOverrideSearchPath();
899-
900901
/*
901902
* Normally we make a dedicated memory context for the CachedPlan and its
902903
* subsidiary data. (It's probably not going to be large, but just in
@@ -1268,7 +1269,6 @@ CopyCachedPlan(CachedPlanSource *plansource)
12681269
newsource->resultDesc=CreateTupleDescCopy(plansource->resultDesc);
12691270
else
12701271
newsource->resultDesc=NULL;
1271-
newsource->search_path=CopyOverrideSearchPath(plansource->search_path);
12721272
newsource->context=source_context;
12731273

12741274
querytree_context=AllocSetContextCreate(source_context,
@@ -1280,6 +1280,8 @@ CopyCachedPlan(CachedPlanSource *plansource)
12801280
newsource->query_list= (List*)copyObject(plansource->query_list);
12811281
newsource->relationOids= (List*)copyObject(plansource->relationOids);
12821282
newsource->invalItems= (List*)copyObject(plansource->invalItems);
1283+
if (plansource->search_path)
1284+
newsource->search_path=CopyOverrideSearchPath(plansource->search_path);
12831285
newsource->query_context=querytree_context;
12841286

12851287
newsource->gplan=NULL;

‎src/include/catalog/namespace.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -128,6 +128,7 @@ extern void ResetTempTableNamespace(void);
128128

129129
externOverrideSearchPath*GetOverrideSearchPath(MemoryContextcontext);
130130
externOverrideSearchPath*CopyOverrideSearchPath(OverrideSearchPath*path);
131+
externboolOverrideSearchPathMatchesCurrent(OverrideSearchPath*path);
131132
externvoidPushOverrideSearchPath(OverrideSearchPath*newpath);
132133
externvoidPopOverrideSearchPath(void);
133134

‎src/include/utils/plancache.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -86,12 +86,13 @@ typedef struct CachedPlanSource
8686
intcursor_options;/* cursor options used for planning */
8787
boolfixed_result;/* disallow change in result tupdesc? */
8888
TupleDescresultDesc;/* result type; NULL = doesn't return tuples */
89-
structOverrideSearchPath*search_path;/* saved search_path */
9089
MemoryContextcontext;/* memory context holding all above */
9190
/* These fields describe the current analyzed-and-rewritten query tree: */
9291
List*query_list;/* list of Query nodes, or NIL if not valid */
9392
List*relationOids;/* OIDs of relations the queries depend on */
9493
List*invalItems;/* other dependencies, as PlanInvalItems */
94+
structOverrideSearchPath*search_path;/* search_path used for
95+
* parsing and planning */
9596
MemoryContextquery_context;/* context holding the above, or NULL */
9697
/* If we have a generic plan, this is a reference-counted link to it: */
9798
structCachedPlan*gplan;/* generic plan, or NULL if not valid */

‎src/test/regress/expected/plancache.out‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -163,7 +163,7 @@ select cache_test_2();
163163
10007
164164
(1 row)
165165

166-
--- Check that change of search_path isignored by replans
166+
--- Check that change of search_path ishonored when re-using cached plan
167167
create schema s1
168168
create table abc (f1 int);
169169
create schema s2
@@ -188,14 +188,14 @@ select f1 from abc;
188188
execute p1;
189189
f1
190190
-----
191-
123
191+
456
192192
(1 row)
193193

194194
alter table s1.abc add column f2 float8; -- force replan
195195
execute p1;
196196
f1
197197
-----
198-
123
198+
456
199199
(1 row)
200200

201201
drop schema s1 cascade;

‎src/test/regress/sql/plancache.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -94,7 +94,7 @@ create or replace temp view v1 as
9494
select2+2+4+(selectmax(unique1)from tenk1)as f1;
9595
select cache_test_2();
9696

97-
--- Check that change of search_path isignored by replans
97+
--- Check that change of search_path ishonored when re-using cached plan
9898

9999
createschemas1
100100
createtableabc (f1int);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp