I want to use pgbouncer in transaction pool mode. To do this you cannot use prepared statements. I set the JDBC PSQL connection params for my data source to not use prepared statements:
prepareThreshold=0&preparedStatementCacheQueries=0
I also disabled prepared statements and caching within hikaricp.
However, it seems hibernate still uses prepared statements from the raw sql logs leading to errors.
Is there some flag or toggle I am missing.
Thread with more infos:
Hibernate will always use prepared statements, there is no way to use a plain statement as that would require to inline all parameters as literals which has a significant impact on performance. From what I see in the comments of the GitHub issue, it is prepared statement caching that you are concerned with, not the use of prepared statements in general.
As far as I understand, the JDBC configuration parameters you used will disable server prepared statement caching, so this should work. What kind of errors do you get? I think you might have to first understand what the exact issue is before we can really help you. I personally doubt though, that pgbouncer requires to use the less secure statements which support no parameters.
@beikov Hey Christian,
Thanks for the reply. The errors I see are cannot find relations for queries like this:
{“timestamp”:“2022-04-10T14:17:27.751+00:00”,“version”:“1”,“message”:“select userrecord0_.id as id1_3_, userrecord0_.created_at as created_2_3_, userrecord0_.created_by as created_3_3_, userrecord0_.description as descript4_3_, userrecord0_.name as name5_3_, userrecord0_.updated_at as updated_6_3_, userrecord0_.updated_by as updated_7_3_, userrecord0_.ext_user_id as ext_user8_3_ from ext_user userrecord0_ where userrecord0_.ext_user_id=?”,“logger_name”:“org.hibernate.SQL”,“thread_name”:“http-nio-8080-exec-6”,“level”:“DEBUG”,“level_value”:10000,“tenant_id”:“development”,“caller”:“user-administration-ui-service”,“trace_id”:“b873e74a7f977af0”,“user_id”:“924d58f9-eee0-43d5-afc0-ea2ea593a153”,“request_path”:“/navigation-schema-service/users/924d58f9-eee0-43d5-afc0-ea2ea593a153/user-roles”,“user_agent”:“okhttp/4.2.2”}
{“timestamp”:“2022-04-10T14:17:27.751+00:00”,“version”:“1”,“message”:“binding parameter [1] as [VARCHAR] - [924d58f9-eee0-43d5-afc0-ea2ea593a153]”,“logger_name”:“org.hibernate.type.descriptor.sql.BasicBinder”,“thread_name”:“http-nio-8080-exec-6”,“level”:“TRACE”,“level_value”:5000,“tenant_id”:“development”,“caller”:“user-administration-ui-service”,“trace_id”:“b873e74a7f977af0”,“user_id”:“924d58f9-eee0-43d5-afc0-ea2ea593a153”,“request_path”:“/navigation-schema-service/users/924d58f9-eee0-43d5-afc0-ea2ea593a153/user-roles”,“user_agent”:“okhttp/4.2.2”}
{“timestamp”:“2022-04-10T14:17:27.753+00:00”,“version”:“1”,“message”:“ERROR: relation "ext_user" does not exist\n Position: 318”,“logger_name”:“org.hibernate.engine.jdbc.spi.SqlExceptionHelper”,“thread_name”:“http-nio-8080-exec-6”,“level”:“ERROR”,“level_value”:40000,“tenant_id”:“development”,“caller”:“user-administration-ui-service”,“trace_id”:“b873e74a7f977af0”,“user_id”:“924d58f9-eee0-43d5-afc0-ea2ea593a153”,“request_path”:“/navigation-schema-service/users/924d58f9-eee0-43d5-afc0-ea2ea593a153/user-roles”,“user_agent”:“okhttp/4.2.2”}
Before I set prepareThreshold=0 and preparedStatementCacheQueries=0, I used to see prepared statements errors like:
ERROR: prepared statement \"S_2\" does not exist
After changing it, I get the above log meesages about relation does not exist. This is the query hibernate tries to run:
select userrecord0_.id as id1_3_, userrecord0_.created_at as created_2_3_, userrecord0_.created_by as created_3_3_, userrecord0_.description as descript4_3_, userrecord0_.name as name5_3_, userrecord0_.updated_at as updated_6_3_, userrecord0_.updated_by as updated_7_3_, userrecord0_.ext_user_id as ext_user8_3_ from ext_user userrecord0_ where userrecord0_.ext_user_id=?"
The ? for ID indicates it is in fact a prepared statement from what I have read in the documentation.
This problem disappears when I move the pgbouncer back into session pooling mode.
From their FAQ:
How to use prepared statements with transaction pooling?
To make prepared statements work in this mode would need PgBouncer to keep track of them internally, which it does not do. So the only way to keep using PgBouncer in this mode is to disable prepared statements in the client.
You can get this working with golang apparently using binary parameters mode:
But I don’t think there is a similar feature available for Hibernate and JPA.
This feature and mode are both used by many people apparently, and I saw some stack overflow tickets where people got this working e.g.:
The most recent find from this ticket was:
<property name="hibernate.cache.use_query_cache">false</property>
I set this within my spring application yaml as such however I still face the same issue as above:
spring:
jpa:
show-sql: true
open-in-view: false
properties:
hibernate:
cache:
use_query_cache: false
format_sql: true
Any ideas or help would be greatly appreciated. I assume I am missing a tiny thing somewhere to get this working.
Are you sure that the user and default schema pgbouncer uses are correct?
I don’t know about this binary_parameters mode. You’d have to ask this question in the forum of the JDBC driver. Hibernate simply uses the PreparedStatement API of the JDBC driver and there is nothing you can do so that Hibernate uses the Statement API.
AFAIU, the way you configured the JDBC driver should work though, so I think this is a permission or configuration issue on the pgbouncer side.
@beikov Thanks for the reply. I did some more testing, and it appears the default schema was set for hikari, but hibernate did not get this set for some reason. I added this to my application properties and now it works!:
jpa: open-in-view: false properties: hibernate: cache: use_query_cache: false default_schema: navigation_schema_service
Thanks so much for your help!
Happy to hear it works for you now 
A belated reply with a possible solution for your application: we ran into the lack of server-side prepared statement caching problem as well. One of our engineers crafted a patch for pgbouncer that implements the prepared statement cache in pgbouncer, alleviating the issue altogether, coming with lots of benefits: less traffic between app server and database (almost no SQL text going across the wire), and therefore also less CPU usage on the database as the SQL doesn’t need to be parsed and prepared for each call.
You can find the prepared statement cache ticket and information here: Add Server-side Prepared Statements Cache by dashorst · Pull Request #757 · pgbouncer/pgbouncer · GitHub
Hope to help!
Martijn