I wrote:
> Here's a draft patch for that. I've not looked yet to see if there's
> any documentation that ought to be touched.
And now with the documentation. If I don't hear any objections, I plan
to commit this tomorrow.
regards, tom lane
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 95cf4b6b467aa96fe8d09ed6816be984d0ad3eb6..b20916353137d9ba577349b42e43170748f80648 100644
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** $$ LANGUAGE plpgsql;
*** 4258,4264 ****
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.
</para>
<para>
--- 4258,4266 ----
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. 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 8466a63c58060eaf0d15b9767caa693e037681d8..b1698f2bb8827f13e1bd56e95b585b483d3539db 100644
*** a/doc/src/sgml/ref/prepare.sgml
--- b/doc/src/sgml/ref/prepare.sgml
*************** PREPARE <replaceable class="PARAMETER">n
*** 153,158 ****
--- 153,180 ----
</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 13391689c7010de3c930d8a85823c8fd6fa46290..68693667b66caafe420d1ea4a1286d8db2a85205 100644
*** a/doc/src/sgml/spi.sgml
--- b/doc/src/sgml/spi.sgml
*************** SPIPlanPtr SPI_prepare(const char * <par
*** 976,981 ****
--- 976,995 ----
</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 b256498d4511add3f55c35f07fecf6a92f19e763..ca4635dc51f41b050521d65cd601bccab90b6726 100644
*** a/src/backend/catalog/namespace.c
--- b/src/backend/catalog/namespace.c
*************** CopyOverrideSearchPath(OverrideSearchPat
*** 3097,3102 ****
--- 3097,3124 ----
}
/*
+ * 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 cbc7c498d0d533b149fe4395992d9910a2d6b5cd..4630c44e7407c98a2de2df7bafc2753e78bfcfc6 100644
*** a/src/backend/utils/cache/plancache.c
--- b/src/backend/utils/cache/plancache.c
***************
*** 15,27 ****
* 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.
*
* 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
* caller to notice changes and cope with them.
*
* Currently, we track exactly the dependencies of plans on relations and
--- 15,29 ----
* 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. 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. 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
*************** CreateCachedPlan(Node *raw_parse_tree,
*** 174,184 ****
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->query_context = NULL;
plansource->gplan = NULL;
plansource->is_oneshot = false;
--- 176,186 ----
plansource->cursor_options = 0;
plansource->fixed_result = false;
plansource->resultDesc = 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;
*************** CreateOneShotCachedPlan(Node *raw_parse_
*** 239,249 ****
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->query_context = NULL;
plansource->gplan = NULL;
plansource->is_oneshot = true;
--- 241,251 ----
plansource->cursor_options = 0;
plansource->fixed_result = false;
plansource->resultDesc = 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;
*************** CompleteCachedPlan(CachedPlanSource *pla
*** 361,366 ****
--- 363,376 ----
&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.
*************** CompleteCachedPlan(CachedPlanSource *pla
*** 383,394 ****
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;
}
--- 393,398 ----
*************** RevalidateCachedQuery(CachedPlanSource *
*** 547,552 ****
--- 551,573 ----
}
/*
+ * 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.
*************** RevalidateCachedQuery(CachedPlanSource *
*** 578,583 ****
--- 599,605 ----
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
*************** RevalidateCachedQuery(CachedPlanSource *
*** 603,616 ****
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
--- 625,630 ----
*************** RevalidateCachedQuery(CachedPlanSource *
*** 645,653 ****
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?
--- 659,664 ----
*************** RevalidateCachedQuery(CachedPlanSource *
*** 699,704 ****
--- 710,722 ----
&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 */
*************** BuildCachedPlan(CachedPlanSource *planso
*** 849,868 ****
}
/*
- * 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.
*/
--- 867,872 ----
*************** BuildCachedPlan(CachedPlanSource *planso
*** 894,902 ****
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
--- 898,903 ----
*************** CopyCachedPlan(CachedPlanSource *plansou
*** 1268,1274 ****
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,
--- 1269,1274 ----
*************** CopyCachedPlan(CachedPlanSource *plansou
*** 1280,1285 ****
--- 1280,1287 ----
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 af82072edb7e90f243b0976337ae7d68fd2b9ce8..c37df8686ea2e835145d1de4b9ba358014b3a4f2 100644
*** a/src/include/catalog/namespace.h
--- b/src/include/catalog/namespace.h
*************** extern void ResetTempTableNamespace(void
*** 128,133 ****
--- 128,134 ----
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 8185427fc4b28ca3caca33f1d177439d626f708e..abaf9dc59c763b4b39978a49b52d5c85c19ed732 100644
*** a/src/include/utils/plancache.h
--- b/src/include/utils/plancache.h
*************** typedef struct CachedPlanSource
*** 86,97 ****
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 */
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 */
--- 86,98 ----
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 */
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 f0aa102334be6dce39c58ecd0135792e0ea46c90..864f70f7b54e7dbb021296f5213f023d53201dd0 100644
*** a/src/test/regress/expected/plancache.out
--- b/src/test/regress/expected/plancache.out
*************** select cache_test_2();
*** 163,169 ****
10007
(1 row)
! --- Check that change of search_path is ignored by replans
create schema s1
create table abc (f1 int);
create schema s2
--- 163,169 ----
10007
(1 row)
! --- Check that change of search_path is honored when re-using cached plan
create schema s1
create table abc (f1 int);
create schema s2
*************** select f1 from abc;
*** 188,201 ****
execute p1;
f1
-----
! 123
(1 row)
alter table s1.abc add column f2 float8; -- force replan
execute p1;
f1
-----
! 123
(1 row)
drop schema s1 cascade;
--- 188,201 ----
execute p1;
f1
-----
! 456
(1 row)
alter table s1.abc add column f2 float8; -- force replan
execute p1;
f1
-----
! 456
(1 row)
drop schema s1 cascade;
diff --git a/src/test/regress/sql/plancache.sql b/src/test/regress/sql/plancache.sql
index 26848168f0631c1f423d32305e2a7d4d273428b0..bc2086166b985d7a3886d2899862443edee928bd 100644
*** a/src/test/regress/sql/plancache.sql
--- b/src/test/regress/sql/plancache.sql
*************** create or replace temp view v1 as
*** 94,100 ****
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
create schema s1
create table abc (f1 int);
--- 94,100 ----
select 2+2+4+(select max(unique1) from tenk1) as f1;
select cache_test_2();
! --- Check that change of search_path is honored when re-using cached plan
create schema s1
create table abc (f1 int);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
