Any way * not to use server prepared statements in Postgresql?

Question

In (say) Python, I can release:

psycopg2.connect(...).cursor().execute("select * from account where id='00100000006ONCrAAO'") 

which on the server leads to the following log entries:

 2011-07-18 18:56:08 PDT LOG: duration: 6.112 ms statement: select * from account where id='00100000006ONCrAAO' 

However, in Java, issuing:

 conn.createStatement().executeQuery("select * from account where id = '00100000006ONCrAAO'"); 

leads to:

 2011-07-18 18:44:59 PDT LOG: duration: 4.353 ms parse <unnamed>: select * from account where id = '00100000006ONCrAAO' 2011-07-18 18:44:59 PDT LOG: duration: 0.230 ms bind <unnamed>: select * from account where id = '00100000006ONCrAAO' 2011-07-18 18:44:59 PDT LOG: duration: 0.246 ms execute <unnamed>: select * from account where id = '00100000006ONCrAAO' 

Some searches show that the PG JDBC driver always uses prepared statements: http://postgresql.1045698.n5.nabble.com/JDBC-prepared-statements-amp-server-side-prepared-statements-td1919506.html

Is there a way to get around reports prepared by the server? If that matters, I ask about PG 8.4 and 9.0.

Answer 1

Got a response from the Postgresql JDBC driver mailing list:

You can use the v2 protocol (which inserts the parameter values ​​as text, and not send them out of order), but you will lose various other bits of driver functionality that depend on the v3 protocol.

Using the V2 protocol, which works to force the driver to use a simple request protocol instead of the extended request protocol, and brought performance back to the levels we saw in Python.

Answer 2

The JDBC driver documentation contains gory details about when and how prepared statements are used on the server side.

In any case, the output of the log that you show does not indicate a problem, because any request will be analyzed, linked and executed. The JDBC driver simply decides to complete these steps as separate protocol steps, not just one step, as the Python driver does. (I suppose you could argue that the overhead is online.) The problem with most people regarding trained operators is that the parameters are replaced after planning, but this is not what happens here because the <unnamed> ready-made statements are planned after (in contrast to the named prepared statements, which are planned after the analysis step). You can read these details in the documentation.

Answer 3

Firstly, under the hood, each db uses prepared instructions, even if they are created on the fly and immediately thrown away.

Secondly, you should not be afraid of prepared statements. They offer a huge performance boost: after creation, they can be reused with various parameters, but all parsing, user authorization verification, plan and query optimization are calculated once and saved using a prepared statement.

If you are going to execute the same sql again and again, create a prepared statement, keep a reference to it and reuse it - providing different parameters for each call.

Here is an example of code that will give you an idea of ​​how to use them:

 private PreparedStatement preparedStatement; public ResultSet getAccount(String id) throws SQLException { // Do this once if (preparedStatement == null) preparedStatement = conn.prepareStatement("select * from account where id = ?"); // Do this many times preparedStatement.setString(1, id); return preparedStatement.executeQuery(); } public static void main(String[] args) throws Exception { ResultSet rs = new MyClass().getAccount("00100000006ONCrAAO"); } 

Answer 4

You need to add

 prepareThreshold=0 

for the JDBC driver connection url is something like this:

 jdbc:postgresql://db.address/dbName?prepareThreshold=0 

see also https://github.com/pgjdbc/pgjdbc/issues/130 this helped me a lot to solve strange driver behavior