Home > mailing lists

Thread: JDBC prepared statement is not treated as prepared statement

JDBC prepared statement is not treated as prepared statement

From

高健

Date:

17 June 2013, 05:52:08

Hello:

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.

Re: JDBC prepared statement is not treated as prepared statement

From

Albe Laurenz

Date:

17 June 2013, 12:31:19

高健  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

Re: JDBC prepared statement is not treated as prepared statement

From

高健

Date:

17 June 2013, 17:06:12

Hi:

Please let me add some contents.

I have compared my Java program via JDBC and C program via libpq.

The result is different:

Prepared statement via Java is not recorded in pg_prepared_statements view.

Prepared statement via C is recorded in pg_prepared_statements view.

I don't know how to make PG realize that I am using a prepared statement when I communicate with PG using Java JDBC.

Can anybody give an idea?

I want this not only for execution effectiveness, but also for protecting DB from SQL injection.

It is said that with common JDBC statement, it is not safe because of SQL injection.

The following is my C code, which is a little long.

I get data using PQprepare and PQexecprepared.

Then I search for the pg_prepared_statements view.

The execution result is:

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

./testprepared

cust_id        cust_name

3              Taylor

name           statement      　　　　　　　　　　　　　　　　prepare_time   　　　　　　parameter_typesfrom_sql

test_stmt      select * from customers where cust_id=$   12013-06-17 16:28:31.70059+08{integer}      f

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

This is my code:

[root@lex tst]# cat testprepared.c

* testlibpq.c

* Test the C version of LIBPQ, the POSTGRES frontend library.

#include <stdio.h>

#include <stdlib.h>

#include "libpq-fe.h"

static void

exit_nicely(PGconn *conn)

{

PQfinish(conn);

exit(EXIT_SUCCESS);

}

int

main()

{

int nFields;

int i,

#ifdef DEBUG

FILE *debug;

#endif /* DEBUG */

///////////////////////////////////////////////////////////////////////////////

///Step1: making connection

PGconn *conn;

PGresult *res;

const char *conninfo="postgresql://postgres:postgres@localhost:5432/postgres";

/* make a connection to the database */

conn = PQconnectdb(conninfo);

/* check to see that the backend connection was successfully made */

if (PQstatus(conn) == CONNECTION_BAD)

{

fprintf(stderr, "Connection to database failed.\n");

fprintf(stderr, "%s", PQerrorMessage(conn));

exit_nicely(conn);

}

#ifdef DEBUG

debug = fopen("/tmp/trace.out", "w");

PQtrace(conn, debug);

#endif /* DEBUG */

////////////////////////////////////////////////////////////////////////////////////

///Step 2, activating prepared statement

/* start a transaction block */

res = PQexec(conn, "BEGIN");

if (PQresultStatus(res) != PGRES_COMMAND_OK)

{

fprintf(stderr, "BEGIN command failed\n");

PQclear(res);

exit_nicely(conn);

}

PQclear(res);

////////////////////////////////////////////////////////////////////////////////////

///Step 2, activating prepared statement

const char *stmt_name = "test_stmt";

const char *stmt = "select * from customers where cust_id=$1";

Oid param_types[1];

param_types[0] = 0; ///let db to judge it.

res = PQprepare(conn, stmt_name, stmt,1,param_types);

if (PQresultStatus(res) != PGRES_COMMAND_OK)

{

fprintf(stderr, "PQprepare failed\n");

PQclear(res);

exit_nicely(conn);

}

PQclear(res);

const char* custid = "3";

const char* param_values[1];

param_values[0] =custid;

int param_lengths[1];

param_lengths[0] = 1;

int param_formats[1];

param_formats[0] = 0;

res = PQexecPrepared(conn, stmt_name, 1, param_values, param_lengths,

param_formats, 0);

if (PQresultStatus(res) != PGRES_TUPLES_OK)

{

fprintf(stderr, "PQexecPrepared statement didn't return tuples properly\n");

PQclear(res);

exit_nicely(conn);

}

/* print out the attribute names */

nFields = PQnfields(res);

for (i = 0; i < nFields; i++)

printf("%-15s", PQfname(res, i));

printf("\n\n");

/* print out the instances */

for (i = 0; i < PQntuples(res); i++)

{

for (j = 0; j < nFields; j++)

printf("%-15s", PQgetvalue(res, i, j));

printf("\n");

}

PQclear(res);

/* end the transaction */

res = PQexec(conn, "END");

PQclear(res);

////////////////////////////////////////////////////////////////////////////////////

///Step 3, looking for cached status of prepared statements

/* start a transaction block */

res = PQexec(conn, "BEGIN");

if (PQresultStatus(res) != PGRES_COMMAND_OK)

{

fprintf(stderr, "BEGIN command failed\n");

PQclear(res);

exit_nicely(conn);

}

PQclear(res);

/* define cursor */

res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_prepared_statements");

if (PQresultStatus(res) != PGRES_COMMAND_OK)

{

fprintf(stderr, "DECLARE CURSOR command failed\n");

PQclear(res);

exit_nicely(conn);

}

PQclear(res);

/* fetch cursor */

res = PQexec(conn, "FETCH ALL in myportal");

if (PQresultStatus(res) != PGRES_TUPLES_OK)

{

fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");

PQclear(res);

exit_nicely(conn);

}

/* first, print out the attribute names */

nFields = PQnfields(res);

for (i = 0; i < nFields; i++)

printf("%-15s", PQfname(res, i));

printf("\n\n");

/* next, print out the instances */

for (i = 0; i < PQntuples(res); i++)

{

for (j = 0; j < nFields; j++)

printf("%-15s", PQgetvalue(res, i, j));

printf("\n");

}

PQclear(res);

/* close the portal */

res = PQexec(conn, "CLOSE myportal");

PQclear(res);

/* end the transaction */

res = PQexec(conn, "END");

PQclear(res);

////////////////////////////////////////////////////////////////////////////////////

///Step 4, close the connection

/* close the connection to the database and cleanup */

PQfinish(conn);

#ifdef DEBUG

fclose(debug);

#endif /* DEBUG */

return 0;

}

[root@lex tst]#

2013/6/17 高健 <luckyjackgao@gmail.com>

Hello:

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.

Re: JDBC prepared statement is not treated as prepared statement

From

高健

Date:

18 June 2013, 07:51:04

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.albe@wien.gv.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

Re: JDBC prepared statement is not treated as prepared statement

From

Albe Laurenz

Date:

18 June 2013, 10:37:57

高健 wrote:
> 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.

Good.

> 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".

I think that it is helpful to explain what the JDBC driver does internally.

If you do not set the threshold or the threshold is not yet exceeded,
the driver will execute the statement as a simple statement (which
corresponds to libpq's PQexec).

Once the threshold is exceeded, the next execution will prepare
the statement (corresponding to libpq's PQprepare) and then execute
it (like libpq's PQexecPrepared).

Subsequent executions will only execute the named prepared statement.

I think that "compile" in the text you quote stands for "prepare".

> 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?

Server prepared statements are kept in the private memory of the
PostgreSQL backend process.  If you need a statement only once or
twice, it would be wasteful to keep it around.
The idea is that it is worth the effort only if the statement is executed
more than a couple of times.

Yours,
Laurenz Albe

Re: JDBC prepared statement is not treated as prepared statement

From

高健

Date:

18 June 2013, 12:01:58

Hello :

>Server prepared statements are kept in the private memory of the
>PostgreSQL backend process. If you need a statement only once or
>twice, it would be wasteful to keep it around.
>The idea is that it is worth the effort only if the statement is executed
>more than a couple of times.

Thank you . I think it is an exciting point for PG.

This make it "clever" to choice those always executed sql.

Thanks!

2013/6/18 Albe Laurenz <laurenz.albe@wien.gv.at>

高健 wrote:
> 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.

Good.

> 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".

I think that it is helpful to explain what the JDBC driver does internally.

If you do not set the threshold or the threshold is not yet exceeded,
the driver will execute the statement as a simple statement (which
corresponds to libpq's PQexec).

Once the threshold is exceeded, the next execution will prepare
the statement (corresponding to libpq's PQprepare) and then execute
it (like libpq's PQexecPrepared).

Subsequent executions will only execute the named prepared statement.

I think that "compile" in the text you quote stands for "prepare".

> 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?

Server prepared statements are kept in the private memory of the
PostgreSQL backend process. If you need a statement only once or
twice, it would be wasteful to keep it around.
The idea is that it is worth the effort only if the statement is executed
more than a couple of times.

Yours,
Laurenz Albe