Re: JDBC prepared statement is not treated as prepared statement

Hello:

Thanks to Laurenz. Your information is very helpful for me.

I change my Java program by adding the following:

org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst;
pgt.setPrepareThreshold(1);

I can see an entry is in pg_prepared_statements now.

But the hyperlink's documentation made me a little confused. I also wonder
why the threshold option is designed .

The document said:

---------------------------------------------

The PostgreSQL™ server allows clients to compile sql statements that are
expected to be reused to avoid the overhead of parsing and planning the
statement for every execution. This functionality is available at the SQL
level via PREPARE and EXECUTE beginning with server version 7.3

…

An internal counter keeps track of how many times the statement has
been executed and when it reaches the threshold it will start to use
server side prepared statements.

…

-----------------------------------------------

What does < clients to compile sql statements > mean?

I think that maybe the document just want to say:

---------------------------------------------------------------------------------------------------------------------------------------------

Before PG import support for prepared statement,

PG server must parse and plan statement every time when the client send a
request.

Even when the same statement will be executed many times.

After PG import support for prepared statement,

When using those statement which is expected reused, by using prepared
statement mechanism,

PG server can avoid overhead of parsing and planning again and again.

But in order to use prepared statement, The client also must do something:

When using psql,

we need to use Prepare command

When using java,

we use java.sql.preparedstatement,

but it is not engouth: we also need to use org.postgresql.PGStatement 's
setthreshold method to let PG server know.

The client must do something to let PG server realize that client want PG
server to use prepared statement.

That is why the docmument say "clients to compile sql statements".

And for the threshold,

If the threshold has not reached, PG server will consider the sql statement
a common one, and will parse and plan for it every time.

Only when the threshold is reached, PG server will realize that client need
it to hold the statement as prepared ,then parsed it and hold the plan.

-----------------------------------------------------------------------------------------------------

Is my understanding right?

Thanks

2013/6/17 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> 高健 wrote:
> > I have one question about prepared statement.
> > I use Java via JDBC, then send prepared statement to execute.
> > I thought that the pg_prepared_statments view will have one record
> after my execution.
> > But I can't find.
> >
> > Is the JDBC's prepared statement differ from SQL execute by prepare
> command ?
> > http://www.postgresql.org/docs/current/static/sql-prepare.html
> >
> > My simple java program is the following:
> >
> > import java.sql.*;
> >
> > public class Test01 {
> > public static void main(String argsv[]){
> > try
> > {
> > Class.forName("org.postgresql.Driver").newInstance();
> > String url = "jdbc:postgresql://localhost:5432/postgres" ;
> > Connection con =
> DriverManager.getConnection(url,"postgres","postgres" );
> > ///Phase 1:-------------Select data from
> table-----------------------
> > System.out.println("Phase 1------------------------start");
> > String strsql = " select * from customers where cust_id = ?";
> > PreparedStatement pst=con.prepareStatement(strsql);
> > pst.setInt(1,3); //find the customer with cust_id of 3.
> > ResultSet rs = pst.executeQuery();
> > while (rs.next())
> > {
> > System.out.print("cust_id:"+rs.getInt( "cust_id"));
> > System.out.println("...cust_name:"+rs.getString(
> "cust_name" ));
> > }
> >
> > System.out.println("Phase 1------------------------end\n");
> >
> >
> >
> > ///Phase 2:-------------Use connection again,to select data
> from data dictionary-----------
> > ------------
> > System.out.println("Phase 2------------------------start");
> > strsql = "select * from pg_prepared_statements";
> > pst=con.prepareStatement(strsql);
> > rs = pst.executeQuery();
> > while (rs.next())
> > {
> > System.out.println("statement:"+rs.getString(
> "statement"));
> > }
> > System.out.println("Phase 2------------------------end\n");
> >
> >
> >
> > ///Phase 3:-------------Use connection again,to select data
> from table---------------------
> > --
> > System.out.println("Phase 3------------------------start");
> > strsql = "select * from customers";
> > pst=con.prepareStatement(strsql);
> > rs = pst.executeQuery();
> > while (rs.next())
> > {
> > System.out.print("cust_id:"+rs.getInt( "cust_id"));
> > System.out.println("...cust_name:"+rs.getString(
> "cust_name" ));
> > }
> > System.out.println("Phase 3------------------------end\n");
> > rs.close();
> > pst.close();
> > con.close();
> > }
> > catch (Exception ee)
> > {
> > System.out.print(ee.getMessage());
> > }
> > }
> > }
> >
> >
> >
> > The result of it's execution is:
> >
> > Phase 1------------------------start
> >
> > cust_id:3...cust_name:Taylor
> >
> > Phase 1------------------------end
> >
> >
> >
> > Phase 2------------------------start
> >
> > Phase 2------------------------end
> >
> >
> >
> > Phase 3------------------------start
> >
> > cust_id:1...cust_name:Smith
> >
> > cust_id:2...cust_name:Brown
> >
> > cust_id:3...cust_name:Taylor
> >
> > Phase 3------------------------end
> >
> >
> >
> > That is to say: my prepared statement is not cached by PG?
> >
> > Then how to write a java program to made it's prepared statement
> realized by PG to treat it as a
> > "prepared statement"?
> >
> > Thank you.
>
> See http://jdbc.postgresql.org/documentation/head/server-prepare.html
>
> Set the prepare threshold of a PreparedStatement and use the statement
> at least as many times. Then you should see an entry in
> pg_prepared_statements.
>
> In your example, no PreparedStatement is used more than once.
>
> Yours,
> Laurenz Albe
>