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

Question

In (say) Python, I can issue:

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

which on the server results in 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'");

results in:

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 searching shows 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 any way to circumvent server prepared statements? If it makes a difference, I'm asking regarding PG 8.4 and 9.0.

Answer 1

You need to add

prepareThreshold=0

parameter to the JDBC driver connection URL something like this:

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

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

Answer 2

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

Anyway, the log output you show isn't indicative of a problem, because any query will be parsed, bound, and executed. The JDBC driver just chooses to execute those steps as separate protocol steps rather than one step like the Python driver does. (I suppose you could argue network overhead, though.) The issue most people are concerned about with respect to prepared statements is that parameters are substituted after planning, but that's not what is happening here, because <unnamed> prepared statements are planned after the bind step (unlike named prepared statements, which are planned after the parse step). You can read up on those details in the protocol documentation.