0

I'm writing a client side tool that is querying a Postgres DB using PreparedStatements. I'm running into some issues. This tool has to support a wide variety of Postgres setups. Some of these setups use pgBouncer in transaction pooling mode, which doesn't support prepared statements. It's not easy to determine this before the fact, so a workaround here is to retry with the prepareThreshold=0 jdbc param set.

I'm trying to figure out what the effects of setting the jdbc param prepareThreshold=0 by default would be.

As I understand the effects of setting this parameter are:

  • Client side benefits of prepared statements (allocating fewer Java objects, preventing SQL injection) will not be affected.

  • PreparedStatements are no longer cached. Are only beneficial server side if you are making the same queries over and over again. I believe the default value is 5 (that is one sees performance gains only after 5 repeated queries)

The tool I'm working only issues a couple of queries, so I'm trying to understand whether it's safe for me to add this JDBC param or not, and what the effects of this could be.

Dev K
  • 41
  • 2

1 Answers1

0

It is safe not to use prepared statements. The only effect can be somewhat reduced performance, particularly if you have many short statements, where the overhead of planning the statement is notable.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks! As a follow-up question, are there any other situations where I'd get this error : ```org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" already exists``` According to this thread : https://stackoverflow.com/questions/7611926/postgres-error-prepared-statement-s-1-already-exists?rq=1 it seems it manifests itself in transaction/statement pooling mode but I was wondering if I was missing other cases – Dev K Apr 27 '23 at 16:05
  • You get that message only if you use prepared statements and you try to prepare a statement that already exists. – Laurenz Albe Apr 27 '23 at 16:09