Postgresql 中文操作指南

PREPARE

PREPARE — 准备一个待执行的语句

PREPARE — prepare a statement for execution

Synopsis

PREPARE name [ ( data_type [, ...] ) ] AS statement

Description

PREPARE 创建一个已准备好的语句。已准备好的语句是一个服务器端对象，可用于优化性能。当执行 PREPARE 语句时，将对指定的语句进行解析、分析和重写。随后发出 EXECUTE 命令时，将会计划和执行已准备好的语句。这种分工避免了重复的解析分析工作，同时允许执行计划取决于所提供的特定参数值。

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, 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.

已准备好的语句可以采用参数：在执行语句时替换到语句中的值。在创建已准备好的语句时，通过使用 $1 、 $2 等按位置引用参数。在这些参数之后还可以选择指定一个相应的数据类型列表。当未指定参数的数据类型或将其声明为 unknown 时，系统将根据参数首次被引用的上下文推断出类型（如果可能的话）。在执行语句时，在 EXECUTE 语句中为这些参数指定实际值。请参阅 EXECUTE 以了解更多相关信息。

Prepared statements can take parameters: values that are substituted into the statement when it is executed. When creating the prepared statement, refer to parameters by position, using $1, $2, etc. A corresponding list of parameter data types can optionally be specified. When a parameter’s data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is first referenced (if possible). When executing the statement, specify the actual values for these parameters in the EXECUTE statement. Refer to EXECUTE for more information about that.

已准备好的语句仅在当前数据库会话期间存在。当会话结束时，将忘记已准备好的语句，因此它必须在再次使用之前重新创建。这也意味着多个同时进行的数据库客户端不能使用同一个已准备好的语句；但是，每个客户端都可以创建自己的已准备好的语句来使用。可以使用 DEALLOCATE 命令手动清理已准备好的语句。

Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. This also means that a single prepared statement cannot be used by multiple simultaneous database clients; however, each client can create their own prepared statement to use. Prepared statements can be manually cleaned up using the DEALLOCATE command.

当单个会话用来执行大量相似的语句时，已准备好的语句可能具有最大的性能优势。如果语句规划或重写很复杂（例如，如果查询涉及多个表的连接或需要应用若干规则），那么性能差异将特别明显。如果语句规划和重写相对简单，但执行成本相对昂贵，那么已准备好的语句所带来的性能优势将不太明显。

Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g., if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.

Parameters

  • name

    • An arbitrary name given to this particular prepared statement. It must be unique within a single session and is subsequently used to execute or deallocate a previously prepared statement.

  • data_type

    • The data type of a parameter to the prepared statement. If the data type of a particular parameter is unspecified or is specified as unknown, it will be inferred from the context in which the parameter is first referenced. To refer to the parameters in the prepared statement itself, use $1, $2, etc.

  • statement

    • Any SELECT, INSERT, UPDATE, DELETE, MERGE, or VALUES statement.

Notes

可以使用 generic plan 或 custom plan 执行已准备好的语句。通用计划在所有执行中都是相同的，而自定义计划是使用特定调用中给出的参数值针对特定执行生成的。使用通用计划可以避免规划开销，但在某些情况下，执行自定义计划将会有效得多，因为计划器可以使用参数值方面的知识。（当然，如果已准备好的语句没有参数，那么这是无关紧要的，而且始终使用通用计划。）

A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)

默认情况下（即，当 plan_cache_mode 设置为 auto 时），服务器将自动选择是为有参数的已准备好的语句使用通用计划还是自定义计划。这方面当前的规则是，前五次执行使用自定义计划，并计算出这些计划的平均估计成本。然后创建一个通用计划，并将它的估计成本与平均自定义计划成本进行比较。如果通用计划的成本不是比平均自定义计划成本高很多，致使反复重新规划似乎更可取，则后续执行将使用通用计划。

By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.

可以通过将 plan_cache_mode 分别设置为 force_generic_plan 或 force_custom_plan 来覆盖这个启发式方法，强制服务器使用通用计划或自定义计划。此设置主要在通用计划的成本估算由于某种原因而严重偏差时很有用，让它即使实际成本远远高于自定义计划的成本也能被选择。

This heuristic can be overridden, forcing the server to use either generic or custom plans, by setting plan_cache_mode to force_generic_plan or force_custom_plan respectively. This setting is primarily useful if the generic plan’s cost estimate is badly off for some reason, allowing it to be chosen even though its actual cost is much more than that of a custom plan.

要检查 PostgreSQL 用于已准备好的语句的查询计划，请使用 EXPLAIN ，例如

To examine the query plan PostgreSQL is using for a prepared statement, use EXPLAIN, for example

EXPLAIN EXECUTE name(parameter_values);

如果使用了通用计划，它将包含参数符号 $_n_ ，而自定义计划将把提供的参数值替换进去。

If a generic plan is in use, it will contain parameter symbols $_n_, while a custom plan will have the supplied parameter values substituted into it.

有关查询规划和 PostgreSQL 为此目的收集的统计信息的详细信息，请参阅 ANALYZE 文档。

For more information on query planning and the statistics collected by PostgreSQL for that purpose, see the ANALYZE documentation.

虽然准备好的语句的主要目的是避免重复对语句进行解析和规划，但 PostgreSQL 在使用在语句中使用的数据库对象经历过定义（DDL）更改或它们的规划器统计信息自上次使用准备好的语句以来被更新之前，强制对该语句重新进行分析和重新规划。此外，如果 search_path 的值从一次使用更改为下一次使用，则将使用新的 search_path 重新解析该语句。（后者行为自 PostgreSQL 9.3 起是新的。）这些规则利用准备好的语句在语义上几乎等同于反复提交相同的查询文本，但如果对象定义没有更改，则有性能优势，尤其是在最佳计划在各种使用中保持不变时。语义等效性不太完美的一个案例是，如果语句通过不限定的名称引用表，然后在 search_path 中较早出现的架构中创建了相同名称的新表，则不会发生自动重新解析，因为语句中使用的对象没有发生更改。但是，如果其他一些更改强制重新解析，则新表将在后续使用中被引用。

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 or their planner statistics have been updated since the previous use of the prepared statement. Also, if the value of search_path 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.

你可以通过查询 pg_prepared_statements 系统视图查看在会话中可用的所有准备好的语句。

You can see all prepared statements available in the session by querying the pg_prepared_statements system view.

Examples

针对 INSERT 语句创建准备好的语句，然后执行该语句：

Create a prepared statement for an INSERT statement, and then execute it:

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

针对 SELECT 语句创建准备好的语句，然后执行该语句：

Create a prepared statement for a SELECT statement, and then execute it:

PREPARE usrrptplan (int) AS
    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
    AND l.date = $2;
EXECUTE usrrptplan(1, current_date);

在这个示例中，未指定第二个参数的数据类型，因此是从 $2 使用的上下文中推断出来的。

In this example, the data type of the second parameter is not specified, so it is inferred from the context in which $2 is used.

Compatibility

SQL 标准包含 PREPARE 语句，但它仅用于嵌入式 SQL。此版本的 PREPARE 语句也使用一些不同的语法。

The SQL standard includes a PREPARE statement, but it is only for use in embedded SQL. This version of the PREPARE statement also uses a somewhat different syntax.

See Also

DEALLOCATE ， EXECUTE

DEALLOCATE, EXECUTE