aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/plpgsql.sgml4
-rw-r--r--doc/src/sgml/ref/prepare.sgml22
-rw-r--r--doc/src/sgml/spi.sgml14
-rw-r--r--src/backend/catalog/namespace.c22
-rw-r--r--src/backend/utils/cache/plancache.c84
-rw-r--r--src/include/catalog/namespace.h1
-rw-r--r--src/include/utils/plancache.h3
-rw-r--r--src/test/regress/expected/plancache.out6
-rw-r--r--src/test/regress/sql/plancache.sql2
9 files changed, 111 insertions, 47 deletions
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 95cf4b6b467..b2091635313 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -4258,7 +4258,9 @@ $$ LANGUAGE plpgsql;
on specific parameter values, and caching that for re-use. Typically
this will happen only if the execution plan is not very sensitive to
the values of the <application>PL/pgSQL</> variables referenced in it.
- If it is, generating a plan each time is a net win.
+ If it is, generating a plan each time is a net win. See <xref
+ linkend="sql-prepare"> for more information about the behavior of
+ prepared statements.
</para>
<para>
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
index 8466a63c580..b1698f2bb88 100644
--- a/doc/src/sgml/ref/prepare.sgml
+++ b/doc/src/sgml/ref/prepare.sgml
@@ -153,6 +153,28 @@ PREPARE <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class
</para>
<para>
+ Although the main point of a prepared statement is to avoid repeated parse
+ analysis and planning of the statement, <productname>PostgreSQL</> will
+ force re-analysis and re-planning of the statement before using it
+ whenever database objects used in the statement have undergone
+ definitional (DDL) changes since the previous use of the prepared
+ statement. Also, if the value of <xref linkend="guc-search-path"> changes
+ from one use to the next, the statement will be re-parsed using the new
+ <varname>search_path</>. (This latter behavior is new as of
+ <productname>PostgreSQL</productname> 9.3.) These rules make use of a
+ prepared statement semantically almost equivalent to re-submitting the
+ same query text over and over, but with a performance benefit if no object
+ definitions are changed, especially if the best plan remains the same
+ across uses. An example of a case where the semantic equivalence is not
+ perfect is that if the statement refers to a table by an unqualified name,
+ and then a new table of the same name is created in a schema appearing
+ earlier in the <varname>search_path</>, no automatic re-parse will occur
+ since no object used in the statement changed. However, if some other
+ change forces a re-parse, the new table will be referenced in subsequent
+ uses.
+ </para>
+
+ <para>
You can see all prepared statements available in the session by querying the
<link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link>
system view.
diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml
index 13391689c70..68693667b66 100644
--- a/doc/src/sgml/spi.sgml
+++ b/doc/src/sgml/spi.sgml
@@ -976,6 +976,20 @@ SPIPlanPtr SPI_prepare(const char * <parameter>command</parameter>, int <paramet
</para>
<para>
+ Although the main point of a prepared statement is to avoid repeated parse
+ analysis and planning of the statement, <productname>PostgreSQL</> will
+ force re-analysis and re-planning of the statement before using it
+ whenever database objects used in the statement have undergone
+ definitional (DDL) changes since the previous use of the prepared
+ statement. Also, if the value of <xref linkend="guc-search-path"> changes
+ from one use to the next, the statement will be re-parsed using the new
+ <varname>search_path</>. (This latter behavior is new as of
+ <productname>PostgreSQL</productname> 9.3.) See <xref
+ linkend="sql-prepare"> for more information about the behavior of prepared
+ statements.
+ </para>
+
+ <para>
This function should only be called from a connected procedure.
</para>
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index b256498d451..ca4635dc51f 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -3097,6 +3097,28 @@ CopyOverrideSearchPath(OverrideSearchPath *path)
}
/*
+ * OverrideSearchPathMatchesCurrent - does path match current setting?
+ */
+bool
+OverrideSearchPathMatchesCurrent(OverrideSearchPath *path)
+{
+ /* Easiest way to do this is GetOverrideSearchPath() and compare */
+ bool result;
+ OverrideSearchPath *cur;
+
+ cur = GetOverrideSearchPath(CurrentMemoryContext);
+ if (path->addCatalog == cur->addCatalog &&
+ path->addTemp == cur->addTemp &&
+ equal(path->schemas, cur->schemas))
+ result = true;
+ else
+ result = false;
+ list_free(cur->schemas);
+ pfree(cur);
+ return result;
+}
+
+/*
* PushOverrideSearchPath - temporarily override the search path
*
* We allow nested overrides, hence the push/pop terminology. The GUC
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index cbc7c498d0d..4630c44e740 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -15,13 +15,15 @@
* that matches the event is marked invalid, as is its generic CachedPlan
* if it has one. When (and if) the next demand for a cached plan occurs,
* parse analysis and rewrite is repeated to build a new valid query tree,
- * and then planning is performed as normal.
+ * and then planning is performed as normal. We also force re-analysis and
+ * re-planning if the active search_path is different from the previous time.
*
* Note that if the sinval was a result of user DDL actions, parse analysis
* could throw an error, for example if a column referenced by the query is
- * no longer present. The creator of a cached plan can specify whether it
- * is allowable for the query to change output tupdesc on replan (this
- * could happen with "SELECT *" for example) --- if so, it's up to the
+ * no longer present. Another possibility is for the query's output tupdesc
+ * to change (for instance "SELECT *" might expand differently than before).
+ * The creator of a cached plan can specify whether it is allowable for the
+ * query to change output tupdesc on replan --- if so, it's up to the
* caller to notice changes and cope with them.
*
* Currently, we track exactly the dependencies of plans on relations and
@@ -174,11 +176,11 @@ CreateCachedPlan(Node *raw_parse_tree,
plansource->cursor_options = 0;
plansource->fixed_result = false;
plansource->resultDesc = NULL;
- plansource->search_path = NULL;
plansource->context = source_context;
plansource->query_list = NIL;
plansource->relationOids = NIL;
plansource->invalItems = NIL;
+ plansource->search_path = NULL;
plansource->query_context = NULL;
plansource->gplan = NULL;
plansource->is_oneshot = false;
@@ -239,11 +241,11 @@ CreateOneShotCachedPlan(Node *raw_parse_tree,
plansource->cursor_options = 0;
plansource->fixed_result = false;
plansource->resultDesc = NULL;
- plansource->search_path = NULL;
plansource->context = CurrentMemoryContext;
plansource->query_list = NIL;
plansource->relationOids = NIL;
plansource->invalItems = NIL;
+ plansource->search_path = NULL;
plansource->query_context = NULL;
plansource->gplan = NULL;
plansource->is_oneshot = true;
@@ -361,6 +363,14 @@ CompleteCachedPlan(CachedPlanSource *plansource,
&plansource->invalItems);
/*
+ * Also save the current search_path in the query_context. (This should
+ * not generate much extra cruft either, since almost certainly the path
+ * is already valid.) Again, don't really need it for one-shot plans.
+ */
+ if (!plansource->is_oneshot)
+ plansource->search_path = GetOverrideSearchPath(querytree_context);
+
+ /*
* Save the final parameter types (or other parameter specification data)
* into the source_context, as well as our other parameters. Also save
* the result tuple descriptor.
@@ -383,12 +393,6 @@ CompleteCachedPlan(CachedPlanSource *plansource,
MemoryContextSwitchTo(oldcxt);
- /*
- * Fetch current search_path into dedicated context, but do any
- * recalculation work required in caller's context.
- */
- plansource->search_path = GetOverrideSearchPath(source_context);
-
plansource->is_complete = true;
plansource->is_valid = true;
}
@@ -547,6 +551,23 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
}
/*
+ * If the query is currently valid, we should have a saved search_path ---
+ * check to see if that matches the current environment. If not, we want
+ * to force replan.
+ */
+ if (plansource->is_valid)
+ {
+ Assert(plansource->search_path != NULL);
+ if (!OverrideSearchPathMatchesCurrent(plansource->search_path))
+ {
+ /* Invalidate the querytree and generic plan */
+ plansource->is_valid = false;
+ if (plansource->gplan)
+ plansource->gplan->is_valid = false;
+ }
+ }
+
+ /*
* If the query is currently valid, acquire locks on the referenced
* objects; then check again. We need to do it this way to cover the race
* condition that an invalidation message arrives before we get the locks.
@@ -578,6 +599,7 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
plansource->query_list = NIL;
plansource->relationOids = NIL;
plansource->invalItems = NIL;
+ plansource->search_path = NULL;
/*
* Free the query_context. We don't really expect MemoryContextDelete to
@@ -603,14 +625,6 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
Assert(plansource->is_complete);
/*
- * Restore the search_path that was in use when the plan was made. See
- * comments for PushOverrideSearchPath about limitations of this.
- *
- * (XXX is there anything else we really need to restore?)
- */
- PushOverrideSearchPath(plansource->search_path);
-
- /*
* If a snapshot is already set (the normal case), we can just use that
* for parsing/planning. But if it isn't, install one. Note: no point in
* checking whether parse analysis requires a snapshot; utility commands
@@ -645,9 +659,6 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
if (snapshot_set)
PopActiveSnapshot();
- /* Now we can restore current search path */
- PopOverrideSearchPath();
-
/*
* Check or update the result tupdesc. XXX should we use a weaker
* condition than equalTupleDescs() here?
@@ -699,6 +710,13 @@ RevalidateCachedQuery(CachedPlanSource *plansource)
&plansource->relationOids,
&plansource->invalItems);
+ /*
+ * Also save the current search_path in the query_context. (This should
+ * not generate much extra cruft either, since almost certainly the path
+ * is already valid.)
+ */
+ plansource->search_path = GetOverrideSearchPath(querytree_context);
+
MemoryContextSwitchTo(oldcxt);
/* Now reparent the finished query_context and save the links */
@@ -849,20 +867,6 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
}
/*
- * Restore the search_path that was in use when the plan was made. See
- * comments for PushOverrideSearchPath about limitations of this.
- *
- * (XXX is there anything else we really need to restore?)
- *
- * Note: it's a bit annoying to do this and snapshot-setting twice in the
- * case where we have to do both re-analysis and re-planning. However,
- * until there's some evidence that the cost is actually meaningful
- * compared to parse analysis + planning, I'm not going to contort the
- * code enough to avoid that.
- */
- PushOverrideSearchPath(plansource->search_path);
-
- /*
* If a snapshot is already set (the normal case), we can just use that
* for planning. But if it isn't, and we need one, install one.
*/
@@ -894,9 +898,6 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
if (snapshot_set)
PopActiveSnapshot();
- /* Now we can restore current search path */
- PopOverrideSearchPath();
-
/*
* Normally we make a dedicated memory context for the CachedPlan and its
* subsidiary data. (It's probably not going to be large, but just in
@@ -1268,7 +1269,6 @@ CopyCachedPlan(CachedPlanSource *plansource)
newsource->resultDesc = CreateTupleDescCopy(plansource->resultDesc);
else
newsource->resultDesc = NULL;
- newsource->search_path = CopyOverrideSearchPath(plansource->search_path);
newsource->context = source_context;
querytree_context = AllocSetContextCreate(source_context,
@@ -1280,6 +1280,8 @@ CopyCachedPlan(CachedPlanSource *plansource)
newsource->query_list = (List *) copyObject(plansource->query_list);
newsource->relationOids = (List *) copyObject(plansource->relationOids);
newsource->invalItems = (List *) copyObject(plansource->invalItems);
+ if (plansource->search_path)
+ newsource->search_path = CopyOverrideSearchPath(plansource->search_path);
newsource->query_context = querytree_context;
newsource->gplan = NULL;
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index af82072edb7..c37df8686ea 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -128,6 +128,7 @@ extern void ResetTempTableNamespace(void);
extern OverrideSearchPath *GetOverrideSearchPath(MemoryContext context);
extern OverrideSearchPath *CopyOverrideSearchPath(OverrideSearchPath *path);
+extern bool OverrideSearchPathMatchesCurrent(OverrideSearchPath *path);
extern void PushOverrideSearchPath(OverrideSearchPath *newpath);
extern void PopOverrideSearchPath(void);
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 8185427fc4b..abaf9dc59c7 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -86,12 +86,13 @@ typedef struct CachedPlanSource
int cursor_options; /* cursor options used for planning */
bool fixed_result; /* disallow change in result tupdesc? */
TupleDesc resultDesc; /* result type; NULL = doesn't return tuples */
- struct OverrideSearchPath *search_path; /* saved search_path */
MemoryContext context; /* memory context holding all above */
/* These fields describe the current analyzed-and-rewritten query tree: */
List *query_list; /* list of Query nodes, or NIL if not valid */
List *relationOids; /* OIDs of relations the queries depend on */
List *invalItems; /* other dependencies, as PlanInvalItems */
+ struct OverrideSearchPath *search_path; /* search_path used for
+ * parsing and planning */
MemoryContext query_context; /* context holding the above, or NULL */
/* If we have a generic plan, this is a reference-counted link to it: */
struct CachedPlan *gplan; /* generic plan, or NULL if not valid */
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
index f0aa102334b..864f70f7b54 100644
--- a/src/test/regress/expected/plancache.out
+++ b/src/test/regress/expected/plancache.out
@@ -163,7 +163,7 @@ select cache_test_2();
10007
(1 row)
---- Check that change of search_path is ignored by replans
+--- Check that change of search_path is honored when re-using cached plan
create schema s1
create table abc (f1 int);
create schema s2
@@ -188,14 +188,14 @@ select f1 from abc;
execute p1;
f1
-----
- 123
+ 456
(1 row)
alter table s1.abc add column f2 float8; -- force replan
execute p1;
f1
-----
- 123
+ 456
(1 row)
drop schema s1 cascade;
diff --git a/src/test/regress/sql/plancache.sql b/src/test/regress/sql/plancache.sql
index 26848168f06..bc2086166b9 100644
--- a/src/test/regress/sql/plancache.sql
+++ b/src/test/regress/sql/plancache.sql
@@ -94,7 +94,7 @@ create or replace temp view v1 as
select 2+2+4+(select max(unique1) from tenk1) as f1;
select cache_test_2();
---- Check that change of search_path is ignored by replans
+--- Check that change of search_path is honored when re-using cached plan
create schema s1
create table abc (f1 int);
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-28 17:54:04 +0000