Thread: The logic behind the prepared statement in PostgreSQL

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