The logic behind the prepared statement in PostgreSQL
От | louis |
---|---|
Тема | The logic behind the prepared statement in PostgreSQL |
Дата | |
Msg-id | 1286366222.13691.36.camel@london обсуждение исходный текст |
Список | pgsql-jdbc |
Hi, I've got several questions regarding the behaviors of prepared statement in PostgreSQL. Environment: JDK version: 6 JDBC driver: 8.4-701.jdbc3 No connection pool is used prepareThreshold is set to 1 Code: for (int i = 0; i < 10; i++) { PreparedStatement statement = conn.prepareStatement("select * from gsd_36c_unique where tag_length = ?"); statement.setInt(1, 20); statement.executeQuery(); // statement is left un-closed on purpose; // check the contents of pg_prepared_statement; } Obvervation: The pg_prepared_statements contents of the 4th iteration is as follows: S_2 select * from gsd_36c_unique where tag_length = $1 2010-10-06 19:01:06.052824+08 {integer} f S_4 select * from gsd_36c_unique where tag_length = $1 2010-10-06 19:01:06.63442+08 {integer} f S_3 select * from gsd_36c_unique where tag_length = $1 2010-10-06 19:01:06.317623+08 {integer} f S_1 BEGIN 2010-10-06 19:01:06.052524+08 {} f S_5 select * from gsd_36c_unique where tag_length = $1 2010-10-06 19:01:06.811521+08 {integer} f Discussion: 1. Even though all the prepare statements have the identical SQL string, the postgresql server creates one execution planfor each statement. So I think the execution plan is fetched by name (S_1, S_2, etc.) instead of SQL query string from the server-side. On theother hand, a JDBC code tracing shows that the execution plan is fetched by SQL query String. Is the above assertion correct? 3. In the 5th iteration all the previously-prepared statements are dropped from the postgresql server, the pg_prepared_statementsshows the following: pg_prepared_statemnt S_6 select * from gsd_36c_unique where tag_length = $1 2010-10-06 19:01:06.99907+08 {integer} f pg_prepared_statemnt S_1 BEGIN 2010-10-06 19:01:06.052524+08 {} f I think it's because postgresql JDBC Driver has a default limit for the max number of preparedStatement, which is 4. Can anyone tell me where to adjuest this parameter? Cheers, Louis
В списке pgsql-jdbc по дате отправления: