Disabling prepared statements in dbcp+spring+hibernate+jdbc?

Question

I am currently enhancing an application that uses spring and hibernate.There are multiple instances where the application communicates with the db (postgres) via prepared statements. The application until now, communicated with postgres via dbcp.

Change: The application now communicated to postgres via pgbouncer.

i.e.: application -> dbcp -> pgbouncer -> postgres

I understand this wouldn't be the most ideal solution i.e: having 2 poolers. But due to the current architecture we require both of them.

Requirement: pgbouncer does not support prepared statements in transaction mode & therefore have to be eliminated.

Changes to eliminate prepared statement.

1) psql: VERSION = 9.2.6

no change

2) pgbouncer: In the config file set the following attribures

  ignore_startup_parameters=extra_float_digits
  pool_mode=transaction
  server_reset_query=

3) jdbc : The prepared threshold has been set accordingly. i.e. : jdbc:postgresql://localhost:6541/postgres?prepareThreshold=0

 JAVA VERSION = 1.7.0_51

 JDBC DRIVER  = postgresql-9.3-1102.jdbc41-3.jar

4) dbcp : poolPreparedStatements = false maxOpenPreparedStatements = 0

5) hibernate : no changes

6) spring : no changes

Issue:

Inspite of all these changes I still see prepared statements trying to be created & transactions failing due to that.

"ERROR: prepared statement "S_21" does not exist; nested exception is org.postgresql.util.PSQLException: ERROR: prepared statement "S_21 " does not exist"

I have removed all logical changes that used a prepared statement.

How can I prevent the other prepared statements from being created? Does spring or hibernate internally create prepared statements for their usage? If yes, how do I disable them?

Answer 1

I understand that this post is from a few years ago but I am still facing the same issues. Unfortunately the suggested changes are not working for my current use case.

Facing the following issue: - "Error: Prepared statement xxx does not exist" - "Error: prepared statement xxx already exists"

Tried following the proposed changed but still getting the same error:

Tech Stack:

• Spring Boot (2.1.7.RELEASE)
• Spring Data (JPA + Hibernate)
• The application is deployed on Heorku using the Heroku Postgre
• Client side PgBouncer.
• Modified the DB url with the following properties: "?sslmode=disable&prepareThreshold=0&preparedStatementCacheQueries=0"
• The following settings are added to Heroku config:
  • PGSSLMODE= disable
  • PGBOUNCER_POOL_MODE = transaction
  • PGBOUNCER_IGNORE_STARTUP_PARAMETERS = extra_float_digits
  • set PGBOUNCER_URLS config value to DB name Urls
• Spring Data is set up to use two Databases for (Read/Write & Read).
• Using the @Transactional(readOnly=true) with @Around("@annotation(transactional)") public Object proceed(ProceedingJoinPoint proceedingJoinPoint, Transactional transactional) throws Throwable { try { if(transactional.readOnly()) { RoutingDataSource.setReplicaRoute(); LOGGER.info("Routing database call to the read replica"); } return proceedingJoinPoint.proceed(); } finally { RoutingDataSource.clearReplicaRoute(); } }