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 foo where value = ?");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 plan for 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 the other 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_statements shows 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