The logic behind the prepared statement in PostgreSQL

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

In response to

Browse pgsql-jdbc by date

  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