From: | louis <louis(at)ibms(dot)sinica(dot)edu(dot)tw> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | The logic behind the prepared statement in PostgreSQL |
Date: | 2010-10-06 11:57:02 |
Message-ID: | 1286366222.13691.36.camel@london |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Jowett | 2010-10-06 12:19:59 | Re: The logic behind the prepared statement in PostgreSQL |
Previous Message | Oliver Jowett | 2010-10-06 11:51:48 | Re: Duplicate primary key when primary key is varchar |