Skip site navigation (1) Skip section navigation (2)

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:41:42
Message-ID: 1286365302.13691.35.camel@london (view raw or flat)
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 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

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2010-10-06 11:51:48
Subject: Re: Duplicate primary key when primary key is varchar
Previous:From: Dave CramerDate: 2010-10-06 11:06:06
Subject: Re: Duplicate primary key when primary key is varchar

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group