From e6faf910d75027bdce7cd0f2033db4e912592bcc Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 16 Sep 2011 00:42:53 -0400 Subject: Redesign the plancache mechanism for more flexibility and efficiency. Rewrite plancache.c so that a "cached plan" (which is rather a misnomer at this point) can support generation of custom, parameter-value-dependent plans, and can make an intelligent choice between using custom plans and the traditional generic-plan approach. The specific choice algorithm implemented here can probably be improved in future, but this commit is all about getting the mechanism in place, not the policy. In addition, restructure the API to greatly reduce the amount of extraneous data copying needed. The main compromise needed to make that possible was to split the initial creation of a CachedPlanSource into two steps. It's worth noting in particular that SPI_saveplan is now deprecated in favor of SPI_keepplan, which accomplishes the same end result with zero data copying, and no need to then spend even more cycles throwing away the original SPIPlan. The risk of long-term memory leaks while manipulating SPIPlans has also been greatly reduced. Most of this improvement is based on use of the recently-added MemoryContextSetParent primitive. --- doc/src/sgml/plpgsql.sgml | 119 +++++++++------------ doc/src/sgml/protocol.sgml | 50 +++------ doc/src/sgml/ref/prepare.sgml | 44 ++++---- doc/src/sgml/spi.sgml | 237 +++++++++++++++++++++++++++++------------- 4 files changed, 256 insertions(+), 194 deletions(-) (limited to 'doc') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index d9b4e615e09..84fb012d1f3 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -839,12 +839,10 @@ PREPARE statement_name(integer, integer) AS SELECT $1 < $2; and then this prepared statement is EXECUTEd for each execution of the IF statement, with the current values of the PL/pgSQL variables supplied as - parameter values. - The query plan prepared in this way is saved for the life of the database - connection, as described in - . Normally these details are + parameter values. Normally these details are not important to a PL/pgSQL user, but they are useful to know when trying to diagnose a problem. + More information appears in . @@ -919,10 +917,9 @@ my_record.user_id := 20; When executing a SQL command in this way, - PL/pgSQL plans the command just once - and re-uses the plan on subsequent executions, for the life of - the database connection. The implications of this are discussed - in detail in . + PL/pgSQL may cache and re-use the execution + plan for the command, as discussed in + . @@ -1137,8 +1134,8 @@ EXECUTE command-string INT Also, there is no plan caching for commands executed via - EXECUTE. Instead, the - command is prepared each time the statement is run. Thus the command + EXECUTE. Instead, the command is always planned + each time the statement is run. Thus the command string can be dynamically created within the function to perform actions on different tables and columns. @@ -1206,11 +1203,11 @@ EXECUTE 'SELECT count(*) FROM ' The important difference is that EXECUTE will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereas - PL/pgSQL normally creates a generic plan - and caches it for re-use. In situations where the best plan depends - strongly on the parameter values, EXECUTE can be - significantly faster; while when the plan is not sensitive to parameter - values, re-planning will be a waste. + PL/pgSQL may otherwise create a generic plan + and cache it for re-use. In situations where the best plan depends + strongly on the parameter values, it can be helpful to use + EXECUTE to positively ensure that a generic plan is not + selected. @@ -4103,17 +4100,14 @@ $$ LANGUAGE plpgsql; As each expression and SQL command is first executed in the function, the PL/pgSQL interpreter - creates a prepared execution plan (using the - SPI manager's SPI_prepare - and SPI_saveplan functions). + parses and analyzes the command to create a prepared statement, + using the SPI manager's + SPI_prepare function. Subsequent visits to that expression or command - reuse the prepared plan. Thus, a function with conditional code - that contains many statements for which execution plans might be - required will only prepare and save those plans that are really - used during the lifetime of the database connection. This can - substantially reduce the total amount of time required to parse - and generate execution plans for the statements in a - PL/pgSQL function. A disadvantage is that errors + reuse the prepared statement. Thus, a function with conditional code + paths that are seldom visited will never incur the overhead of + analyzing those commands that are never executed within the current + session. A disadvantage is that errors in a specific expression or command cannot be detected until that part of the function is reached in execution. (Trivial syntax errors will be detected during the initial parsing pass, but @@ -4121,46 +4115,31 @@ $$ LANGUAGE plpgsql; - A saved plan will be re-planned automatically if there is any schema - change to any table used in the query, or if any user-defined function - used in the query is redefined. This makes the re-use of prepared plans - transparent in most cases, but there are corner cases where a stale plan - might be re-used. An example is that dropping and re-creating a - user-defined operator won't affect already-cached plans; they'll continue - to call the original operator's underlying function, if that has not been - changed. When necessary, the cache can be flushed by starting a fresh - database session. + PL/pgSQL (or more precisely, the SPI manager) can + furthermore attempt to cache the execution plan associated with any + particular prepared statement. If a cached plan is not used, then + a fresh execution plan is generated on each visit to the statement, + and the current parameter values (that is, PL/pgSQL + variable values) can be used to optimize the selected plan. If the + statement has no parameters, or is executed many times, the SPI manager + will consider creating a generic plan that is not dependent + 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. - Because PL/pgSQL saves execution plans - in this way, SQL commands that appear directly in a + Because PL/pgSQL saves prepared statements + and sometimes execution plans in this way, + SQL commands that appear directly in a PL/pgSQL function must refer to the same tables and columns on every execution; that is, you cannot use a parameter as the name of a table or column in an SQL command. To get around this restriction, you can construct dynamic commands using the PL/pgSQL EXECUTE - statement — at the price of constructing a new execution plan on - every execution. - - - - Another important point is that the prepared plans are parameterized - to allow the values of PL/pgSQL variables - to change from one use to the next, as discussed in detail above. - Sometimes this means that a plan is less efficient than it would be - if generated for a specific variable value. As an example, consider - -SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term; - - where search_term is a PL/pgSQL - variable. The cached plan for this query will never use an index on - word, since the planner cannot assume that the - LIKE pattern will be left-anchored at run time. To use - an index the query must be planned with a specific constant - LIKE pattern provided. This is another situation where - EXECUTE can be used to force a new plan to be - generated for each execution. + statement — at the price of performing new parse analysis and + constructing a new execution plan on every execution. @@ -4168,14 +4147,14 @@ SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term; connection. When fields of a record variable are used in expressions or statements, the data types of the fields must not change from one call of the function to the next, since each - expression will be planned using the data type that is present + expression will be analyzed using the data type that is present when the expression is first reached. EXECUTE can be used to get around this problem when necessary. If the same function is used as a trigger for more than one table, - PL/pgSQL prepares and caches plans + PL/pgSQL prepares and caches statements independently for each such table — that is, there is a cache for each trigger function and table combination, not just for each function. This alleviates some of the problems with varying @@ -4186,14 +4165,14 @@ SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term; Likewise, functions having polymorphic argument types have a separate - plan cache for each combination of actual argument types they have been - invoked for, so that data type differences do not cause unexpected + statement cache for each combination of actual argument types they have + been invoked for, so that data type differences do not cause unexpected failures. - Plan caching can sometimes have surprising effects on the interpretation - of time-sensitive values. For example there + Statement caching can sometimes have surprising effects on the + interpretation of time-sensitive values. For example there is a difference between what these two functions do: @@ -4221,15 +4200,17 @@ $$ LANGUAGE plpgsql; In the case of logfunc1, the PostgreSQL main parser knows when - preparing the plan for the INSERT that the + analyzing the INSERT that the string 'now' should be interpreted as timestamp, because the target column of logtable is of that type. Thus, - 'now' will be converted to a constant when the - INSERT is planned, and then used in all + 'now' will be converted to a timestamp + constant when the + INSERT is analyzed, and then used in all invocations of logfunc1 during the lifetime of the session. Needless to say, this isn't what the programmer - wanted. + wanted. A better idea is to use the now() or + current_timestamp function. @@ -4243,7 +4224,9 @@ $$ LANGUAGE plpgsql; string to the timestamp type by calling the text_out and timestamp_in functions for the conversion. So, the computed time stamp is updated - on each execution as the programmer expects. + on each execution as the programmer expects. Even though this + happens to work as expected, it's not terribly efficient, so + use of the now() function would still be a better idea. diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 508991bcc2b..19c9686fff4 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -125,9 +125,8 @@ into multiple steps. The state retained between steps is represented by two types of objects: prepared statements and portals. A prepared statement represents the result of - parsing, semantic analysis, and (optionally) planning of a textual query - string. - A prepared statement is not necessarily ready to execute, because it might + parsing and semantic analysis of a textual query string. + A prepared statement is not in itself ready to execute, because it might lack specific values for parameters. A portal represents a ready-to-execute or already-partially-executed statement, with any missing parameter values filled in. (For SELECT statements, @@ -692,7 +691,7 @@ the unnamed statement as destination is issued. (Note that a simple Query message also destroys the unnamed statement.) Named prepared statements must be explicitly closed before they can be redefined by - a Parse message, but this is not required for the unnamed statement. + another Parse message, but this is not required for the unnamed statement. Named prepared statements can also be created and accessed at the SQL command level, using PREPARE and EXECUTE. @@ -722,44 +721,23 @@ - Query planning for named prepared-statement objects occurs when the Parse - message is processed. If a query will be repeatedly executed with - different parameters, it might be beneficial to send a single Parse message - containing a parameterized query, followed by multiple Bind - and Execute messages. This will avoid replanning the query on each - execution. + Query planning typically occurs when the Bind message is processed. + If the prepared statement has no parameters, or is executed repeatedly, + the server might save the created plan and re-use it during subsequent + Bind messages for the same prepared statement. However, it will do so + only if it finds that a generic plan can be created that is not much + less efficient than a plan that depends on the specific parameter values + supplied. This happens transparently so far as the protocol is concerned. - - The unnamed prepared statement is likewise planned during Parse processing - if the Parse message defines no parameters. But if there are parameters, - query planning occurs every time Bind parameters are supplied. This allows the - planner to make use of the actual values of the parameters provided by - each Bind message, rather than use generic estimates. - - - - - Query plans generated from a parameterized query might be less - efficient than query plans generated from an equivalent query with actual - parameter values substituted. The query planner cannot make decisions - based on actual parameter values (for example, index selectivity) when - planning a parameterized query assigned to a named prepared-statement - object. This possible penalty is avoided when using the unnamed - statement, since it is not planned until actual parameter values are - available. The cost is that planning must occur afresh for each Bind, - even if the query stays the same. - - - If successfully created, a named portal object lasts till the end of the current transaction, unless explicitly destroyed. An unnamed portal is destroyed at the end of the transaction, or as soon as the next Bind statement specifying the unnamed portal as destination is issued. (Note that a simple Query message also destroys the unnamed portal.) Named - portals must be explicitly closed before they can be redefined by a Bind - message, but this is not required for the unnamed portal. + portals must be explicitly closed before they can be redefined by another + Bind message, but this is not required for the unnamed portal. Named portals can also be created and accessed at the SQL command level, using DECLARE CURSOR and FETCH. @@ -1280,7 +1258,9 @@ The frontend should also be prepared to handle an ErrorMessage response to SSLRequest from the server. This would only occur if the server predates the addition of SSL support - to PostgreSQL. In this case the connection must + to PostgreSQL. (Such servers are now very ancient, + and likely do not exist in the wild anymore.) + In this case the connection must be closed, but the frontend might choose to open a fresh connection and proceed without requesting SSL. diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml index 6f18a97ca59..8466a63c580 100644 --- a/doc/src/sgml/ref/prepare.sgml +++ b/doc/src/sgml/ref/prepare.sgml @@ -37,11 +37,11 @@ PREPARE name [ ( PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is - executed, the specified statement is parsed, rewritten, and - planned. When an EXECUTE command is subsequently - issued, the prepared statement need only be executed. Thus, the - parsing, rewriting, and planning stages are only performed once, - instead of every time the statement is executed. + executed, the specified statement is parsed, analyzed, and rewritten. + When an EXECUTE command is subsequently + issued, the prepared statement is planned and executed. This division + of labor avoids repetitive parse analysis work, while allowing + the execution plan to depend on the specific parameter values supplied. @@ -65,7 +65,7 @@ PREPARE name [ ( command. @@ -127,20 +127,22 @@ PREPARE name [ ( Notes - In some situations, the query plan produced for a prepared - statement will be inferior to the query plan that would have been - chosen if the statement had been submitted and executed - normally. This is because when the statement is planned and the - planner attempts to determine the optimal query plan, the actual - values of any parameters specified in the statement are - unavailable. PostgreSQL collects - statistics on the distribution of data in the table, and can use - constant values in a statement to make guesses about the likely - result of executing the statement. Since this data is unavailable - when planning prepared statements with parameters, the chosen plan - might be suboptimal. To examine the query plan - PostgreSQL has chosen for a prepared - statement, use . + If a prepared statement is executed enough times, the server may eventually + decide to save and re-use a generic plan rather than re-planning each time. + This will occur immediately if the prepared statement has no parameters; + otherwise it occurs only if the generic plan appears to be not much more + expensive than a plan that depends on specific parameter values. + Typically, a generic plan will be selected only if the query's performance + is estimated to be fairly insensitive to the specific parameter values + supplied. + + + + To examine the query plan PostgreSQL is using + for a prepared statement, use . + If a generic plan is in use, it will contain parameter symbols + $n, while a custom plan will have the + current actual parameter values substituted into it. @@ -151,7 +153,7 @@ PREPARE name [ ( - You can see all available prepared statements of a session by querying the + You can see all prepared statements available in the session by querying the pg_prepared_statements system view. diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index 1b41cb40b3c..7162fdb7aa3 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -733,7 +733,8 @@ int SPI_execute_with_args(const char *command, Similar results can be achieved with SPI_prepare followed by SPI_execute_plan; however, when using this function - the query plan is customized to the specific parameter values provided. + the query plan is always customized to the specific parameter values + provided. For one-time query execution, this function should be preferred. If the same command is to be executed with many different parameters, either method might be faster, depending on the cost of re-planning @@ -840,7 +841,7 @@ int SPI_execute_with_args(const char *command, SPI_prepare - prepare a plan for a command, without executing it yet + prepare a statement, without executing it yet SPI_prepare @@ -855,17 +856,22 @@ SPIPlanPtr SPI_prepare(const char * command, int Description - SPI_prepare creates and returns an execution - plan for the specified command, but doesn't execute the command. - This function should only be called from a connected procedure. + SPI_prepare creates and returns a prepared + statement for the specified command, but doesn't execute the command. + The prepared statement can later be executed repeatedly using + SPI_execute_plan. When the same or a similar command is to be executed repeatedly, it - might be advantageous to perform the planning only once. - SPI_prepare converts a command string into an - execution plan that can be executed repeatedly using - SPI_execute_plan. + is generally advantageous to perform parse analysis only once, and + might furthermore be advantageous to re-use an execution plan for the + command. + SPI_prepare converts a command string into a + prepared statement that encapsulates the results of parse analysis. + The prepared statement also provides a place for caching an execution plan + if it is found that generating a custom plan for each execution is not + helpful. @@ -878,11 +884,11 @@ SPIPlanPtr SPI_prepare(const char * command, int - The plan returned by SPI_prepare can be used + The statement returned by SPI_prepare can be used only in the current invocation of the procedure, since - SPI_finish frees memory allocated for a plan. - But a plan can be saved for longer using the function - SPI_saveplan. + SPI_finish frees memory allocated for such a + statement. But the statement can be saved for longer using the functions + SPI_keepplan or SPI_saveplan. @@ -925,7 +931,8 @@ SPIPlanPtr SPI_prepare(const char * command, int SPI_prepare returns a non-null pointer to an - execution plan. On error, NULL will be returned, + SPIPlan, which is an opaque struct representing a prepared + statement. On error, NULL will be returned, and SPI_result will be set to one of the same error codes used by SPI_execute, except that it is set to SPI_ERROR_ARGUMENT if @@ -938,6 +945,26 @@ SPIPlanPtr SPI_prepare(const char * command, int Notes + + If no parameters are defined, a generic plan will be created at the + first use of SPI_execute_plan, and used for all + subsequent executions as well. If there are parameters, the first few uses + of SPI_execute_plan will generate custom plans + that are specific to the supplied parameter values. After enough uses + of the same prepared statement, SPI_execute_plan will + build a generic plan, and if that is not too much more expensive than the + custom plans, it will start using the generic plan instead of re-planning + each time. If this default behavior is unsuitable, you can alter it by + passing the CURSOR_OPT_GENERIC_PLAN or + CURSOR_OPT_CUSTOM_PLAN flag to + SPI_prepare_cursor, to force use of generic or custom + plans respectively. + + + + This function should only be called from a connected procedure. + + SPIPlanPtr is declared as a pointer to an opaque struct type in spi.h. It is unwise to try to access its contents @@ -946,10 +973,8 @@ SPIPlanPtr SPI_prepare(const char * command, int - There is a disadvantage to using parameters: since the planner does - not know the values that will be supplied for the parameters, it - might make worse planning choices than it would make for a normal - command with all constants visible. + The name SPIPlanPtr is somewhat historical, since the data + structure no longer necessarily contains an execution plan. @@ -964,7 +989,7 @@ SPIPlanPtr SPI_prepare(const char * command, int SPI_prepare_cursor - prepare a plan for a command, without executing it yet + prepare a statement, without executing it yet SPI_prepare_cursor @@ -1047,8 +1072,10 @@ SPIPlanPtr SPI_prepare_cursor(const char * command, int < Useful bits to set in cursorOptions include CURSOR_OPT_SCROLL, - CURSOR_OPT_NO_SCROLL, and - CURSOR_OPT_FAST_PLAN. Note in particular that + CURSOR_OPT_NO_SCROLL, + CURSOR_OPT_FAST_PLAN, + CURSOR_OPT_GENERIC_PLAN, and + CURSOR_OPT_CUSTOM_PLAN. Note in particular that CURSOR_OPT_HOLD is ignored. @@ -1064,7 +1091,7 @@ SPIPlanPtr SPI_prepare_cursor(const char * command, int < SPI_prepare_params - prepare a plan for a command, without executing it yet + prepare a statement, without executing it yet SPI_prepare_params @@ -1082,8 +1109,8 @@ SPIPlanPtr SPI_prepare_params(const char * command, Description - SPI_prepare_params creates and returns an execution - plan for the specified command, but doesn't execute the command. + SPI_prepare_params creates and returns a prepared + statement for the specified command, but doesn't execute the command. This function is equivalent to SPI_prepare_cursor, with the addition that the caller can specify parser hook functions to control the parsing of external parameter references. @@ -1152,7 +1179,7 @@ SPIPlanPtr SPI_prepare_params(const char * command, SPI_getargcount - return the number of arguments needed by a plan + return the number of arguments needed by a statement prepared by SPI_prepare @@ -1169,7 +1196,7 @@ int SPI_getargcount(SPIPlanPtr plan) SPI_getargcount returns the number of arguments needed - to execute a plan prepared by SPI_prepare. + to execute a statement prepared by SPI_prepare. @@ -1181,7 +1208,7 @@ int SPI_getargcount(SPIPlanPtr plan) SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -1210,7 +1237,7 @@ int SPI_getargcount(SPIPlanPtr plan) SPI_getargtypeid return the data type OID for an argument of - a plan prepared by SPI_prepare + a statement prepared by SPI_prepare SPI_getargtypeid @@ -1226,7 +1253,7 @@ Oid SPI_getargtypeid(SPIPlanPtr plan, int argI SPI_getargtypeid returns the OID representing the type - for the argIndex'th argument of a plan prepared by + for the argIndex'th argument of a statement prepared by SPI_prepare. First argument is at index zero. @@ -1239,7 +1266,7 @@ Oid SPI_getargtypeid(SPIPlanPtr plan, int argI SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -1279,7 +1306,7 @@ Oid SPI_getargtypeid(SPIPlanPtr plan, int argI SPI_is_cursor_plan - return true if a plan + return true if a statement prepared by SPI_prepare can be used with SPI_cursor_open @@ -1297,7 +1324,7 @@ bool SPI_is_cursor_plan(SPIPlanPtr plan) SPI_is_cursor_plan returns true - if a plan prepared by SPI_prepare can be passed + if a statement prepared by SPI_prepare can be passed as an argument to SPI_cursor_open, or false if that is not the case. The criteria are that the plan represents one single command and that this @@ -1316,7 +1343,7 @@ bool SPI_is_cursor_plan(SPIPlanPtr plan) SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -1348,7 +1375,7 @@ bool SPI_is_cursor_plan(SPIPlanPtr plan) SPI_execute_plan - execute a plan prepared by SPI_prepare + execute a statement prepared by SPI_prepare SPI_execute_plan @@ -1364,8 +1391,9 @@ int SPI_execute_plan(SPIPlanPtr plan, Datum * Description - SPI_execute_plan executes a plan prepared by - SPI_prepare. read_only and + SPI_execute_plan executes a statement prepared by + SPI_prepare or one of its siblings. + read_only and count have the same interpretation as in SPI_execute. @@ -1379,7 +1407,7 @@ int SPI_execute_plan(SPIPlanPtr plan, Datum * SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -1389,7 +1417,7 @@ int SPI_execute_plan(SPIPlanPtr plan, Datum * An array of actual parameter values. Must have same length as the - plan's number of arguments. + statement's number of arguments. @@ -1399,7 +1427,7 @@ int SPI_execute_plan(SPIPlanPtr plan, Datum * An array describing which parameters are null. Must have same length as - the plan's number of arguments. + the statement's number of arguments. n indicates a null value (entry in values will be ignored); a space indicates a nonnull value (entry in values is valid). @@ -1479,7 +1507,7 @@ int SPI_execute_plan(SPIPlanPtr plan, Datum * SPI_execute_plan_with_paramlist - execute a plan prepared by SPI_prepare + execute a statement prepared by SPI_prepare SPI_execute_plan_with_paramlist @@ -1497,7 +1525,7 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, Description - SPI_execute_plan_with_paramlist executes a plan + SPI_execute_plan_with_paramlist executes a statement prepared by SPI_prepare. This function is equivalent to SPI_execute_plan except that information about the parameter values to be passed to the @@ -1516,7 +1544,7 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -1573,7 +1601,7 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, SPI_execp - execute a plan in read/write mode + execute a statement in read/write mode SPI_execp @@ -1603,7 +1631,7 @@ int SPI_execp(SPIPlanPtr plan, Datum * values< SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -1613,7 +1641,7 @@ int SPI_execp(SPIPlanPtr plan, Datum * values< An array of actual parameter values. Must have same length as the - plan's number of arguments. + statement's number of arguments. @@ -1623,7 +1651,7 @@ int SPI_execp(SPIPlanPtr plan, Datum * values< An array describing which parameters are null. Must have same length as - the plan's number of arguments. + the statement's number of arguments. n indicates a null value (entry in values will be ignored); a space indicates a nonnull value (entry in values is valid). @@ -1673,7 +1701,7 @@ int SPI_execp(SPIPlanPtr plan, Datum * values< SPI_cursor_open - set up a cursor using a plan created with SPI_prepare + set up a cursor using a statement created with SPI_prepare SPI_cursor_open @@ -1691,14 +1719,14 @@ Portal SPI_cursor_open(const char * name, SPIPlanPtr SPI_cursor_open sets up a cursor (internally, - a portal) that will execute a plan prepared by + a portal) that will execute a statement prepared by SPI_prepare. The parameters have the same meanings as the corresponding parameters to SPI_execute_plan. - Using a cursor instead of executing the plan directly has two + Using a cursor instead of executing the statement directly has two benefits. First, the result rows can be retrieved a few at a time, avoiding memory overrun for queries that return many rows. Second, a portal can outlive the current procedure (it can, in fact, live @@ -1731,7 +1759,7 @@ Portal SPI_cursor_open(const char * name, SPIPlanPtr SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -1741,7 +1769,7 @@ Portal SPI_cursor_open(const char * name, SPIPlanPtr An array of actual parameter values. Must have same length as the - plan's number of arguments. + statement's number of arguments. @@ -1751,7 +1779,7 @@ Portal SPI_cursor_open(const char * name, SPIPlanPtr An array describing which parameters are null. Must have same length as - the plan's number of arguments. + the statement's number of arguments. n indicates a null value (entry in values will be ignored); a space indicates a nonnull value (entry in values is valid). @@ -1958,7 +1986,7 @@ Portal SPI_cursor_open_with_paramlist(const char *name, SPI_cursor_open_with_paramlist sets up a cursor - (internally, a portal) that will execute a plan prepared by + (internally, a portal) that will execute a statement prepared by SPI_prepare. This function is equivalent to SPI_cursor_open except that information about the parameter values to be passed to the @@ -1992,7 +2020,7 @@ Portal SPI_cursor_open_with_paramlist(const char *name, SPIPlanPtr plan - execution plan (returned by SPI_prepare) + prepared statement (returned by SPI_prepare) @@ -2495,6 +2523,75 @@ void SPI_cursor_close(Portal portal) + + + SPI_keepplan + 3 + + + + SPI_keepplan + save a prepared statement + + + SPI_keepplan + + + +int SPI_keepplan(SPIPlanPtr plan) + + + + + Description + + + SPI_keepplan saves a passed statement (prepared by + SPI_prepare) so that it will not be freed + by SPI_finish nor by the transaction manager. + This gives you the ability to reuse prepared statements in the subsequent + invocations of your procedure in the current session. + + + + + Arguments + + + + SPIPlanPtr plan + + + the prepared statement to be saved + + + + + + + + Return Value + + + 0 on success; + SPI_ERROR_ARGUMENT if plan + is NULL or invalid + + + + + Notes + + + The passed-in statement is relocated to permanent storage by means + of pointer adjustment (no data copying is required). If you later + wish to delete it, use SPI_freeplan on it. + + + + + + SPI_saveplan @@ -2503,7 +2600,7 @@ void SPI_cursor_close(Portal portal) SPI_saveplan - save a plan + save a prepared statement SPI_saveplan @@ -2518,11 +2615,11 @@ SPIPlanPtr SPI_saveplan(SPIPlanPtr plan) Description - SPI_saveplan saves a passed plan (prepared by - SPI_prepare) in memory that will not be freed + SPI_saveplan copies a passed statement (prepared by + SPI_prepare) into memory that will not be freed by SPI_finish nor by the transaction manager, - and returns a pointer to the saved plan. This gives you the - ability to reuse prepared plans in the subsequent invocations of + and returns a pointer to the copied statement. This gives you the + ability to reuse prepared statements in the subsequent invocations of your procedure in the current session. @@ -2535,7 +2632,7 @@ SPIPlanPtr SPI_saveplan(SPIPlanPtr plan) SPIPlanPtr plan - the plan to be saved + the prepared statement to be saved @@ -2546,7 +2643,7 @@ SPIPlanPtr SPI_saveplan(SPIPlanPtr plan) Return Value - Pointer to the saved plan; NULL if unsuccessful. + Pointer to the copied statement; or NULL if unsuccessful. On error, SPI_result is set thus: @@ -2575,16 +2672,15 @@ SPIPlanPtr SPI_saveplan(SPIPlanPtr plan) Notes - The passed-in plan is not freed, so you might wish to do + The originally passed-in statement is not freed, so you might wish to do SPI_freeplan on it to avoid leaking memory until SPI_finish. - If one of the objects (a table, function, etc.) referenced by the - prepared plan is dropped or redefined, then future executions of - SPI_execute_plan may fail or return different - results than the plan initially indicates. + In most cases, SPI_keepplan is preferred to this + function, since it accomplishes largely the same result without needing + to physically copy the prepared statement's data structures. @@ -3809,7 +3905,7 @@ void SPI_freetuptable(SPITupleTable * tuptable) SPI_freeplan - free a previously saved plan + free a previously saved prepared statement SPI_freeplan @@ -3824,9 +3920,9 @@ int SPI_freeplan(SPIPlanPtr plan) Description - SPI_freeplan releases a command execution plan + SPI_freeplan releases a prepared statement previously returned by SPI_prepare or saved by - SPI_saveplan. + SPI_keepplan or SPI_saveplan. @@ -3838,7 +3934,7 @@ int SPI_freeplan(SPIPlanPtr plan) SPIPlanPtr plan - pointer to plan to free + pointer to statement to free @@ -3849,6 +3945,7 @@ int SPI_freeplan(SPIPlanPtr plan) Return Value + 0 on success; SPI_ERROR_ARGUMENT if plan is NULL or invalid -- cgit v1.2.3