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
See http://jdbc.postgresql.org/documentation/head/server-prepare.html高健 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 = "" ;
> 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.
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
