From 0d5fbdc157a17abc379052f5099b1c29a33cebe2 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 25 Jan 2013 14:14:41 -0500 Subject: Change plan caching to honor, not resist, changes in search_path. In the initial implementation of plan caching, we saved the active search_path when a plan was first cached, then reinstalled that path anytime we needed to reparse or replan. The idea of that was to try to reselect the same referenced objects, in somewhat the same way that views continue to refer to the same objects in the face of schema or name changes. Of course, that analogy doesn't bear close inspection, since holding the search_path fixed doesn't cope with object drops or renames. Moreover sticking with the old path seems to create more surprises than it avoids. So instead of doing that, consider that the cached plan depends on search_path, and force reparse/replan if the active search_path is different than it was when we last saved the plan. This gets us fairly close to having "transparency" of plan caching, in the sense that the cached statement acts the same as if you'd just resubmitted the original query text for another execution. There are still some corner cases where this fails though: a new object added in the search path schema(s) might capture a reference in the query text, but we'd not realize that and force a reparse. We might try to fix that in the future, but for the moment it looks too expensive and complicated. --- doc/src/sgml/plpgsql.sgml | 4 +++- doc/src/sgml/ref/prepare.sgml | 22 ++++++++++++++++++++++ doc/src/sgml/spi.sgml | 14 ++++++++++++++ 3 files changed, 39 insertions(+), 1 deletion(-) (limited to 'doc/src') 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 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 for more information about the behavior of + prepared statements. 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 @@ -152,6 +152,28 @@ PREPARE name [ ( + + Although the main point of a prepared statement is to avoid repeated parse + analysis and planning of the statement, 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 changes + from one use to the next, the statement will be re-parsed using the new + search_path. (This latter behavior is new as of + PostgreSQL 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 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. + + You can see all prepared statements available in the session by querying the pg_prepared_statements 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 @@ -975,6 +975,20 @@ SPIPlanPtr SPI_prepare(const char * command, int + + Although the main point of a prepared statement is to avoid repeated parse + analysis and planning of the statement, 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 changes + from one use to the next, the statement will be re-parsed using the new + search_path. (This latter behavior is new as of + PostgreSQL 9.3.) See for more information about the behavior of prepared + statements. + + This function should only be called from a connected procedure. -- cgit v1.2.3