分享程序员开发的那些事...
更新时间：2022-09-22 23:03:00
http://www.postgresql.org/docs/9.2/static/sql-prepare.html
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.
在psql窗口A进行试验：
prepare 动作：
postgres=# prepare test(int) AS postgres-# select * from customers c where c.cust_id = $1; PREPARE postgres=#
execute 动作：
postgres=# execute test(3); cust_id | cust_name ---------+----------- 3 | Taylor (1 row) postgres=#
此时，看 pg_prepared_statements 的状况：
postgres=# select * from pg_prepared_statements; name | statement | prepare_time | parameter_types | from_sql ------+-------------------------------------------------+-------------------------------+-----------------+---------- test | prepare test(int) AS +| 2013-06-14 15:58:22.796369+08 | {integer} | t | select * from customers c where c.cust_id = $1; | | | (1 row) postgres=#
在另外的一个窗口B，再接着进行试验，我就无法看到 pg_prepared_statements中有值了！
postgres=# select * from pg_prepared_statements; name | statement | prepare_time | parameter_types | from_sql ------+-----------+--------------+-----------------+---------- (0 rows) postgres=#
甚至我在此窗口B中，再次执行 execute test(3) ，也会失败。
postgres=# execute test(3);
ERROR: prepared statement "test" does not exist
postgres=# 由此，可以得出结论：
在PostgreSQL中，prepared statement 也是session 单位的。在为各个客户端服务的各个子进程之间，无法共享已经分析好的执行计划！
如果我的应用程序里，拥有多个数据库连接，是无法共享prepared statement的。
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/archive/2013/06/14/3135805.html，如需转载请自行联系原作者