Postgresql12 database connection pool issue

[kanchangpr]

I have created PostgreSQL 12 database cluster in digital ocean, and using connection pool. in my java code Application.properties i have configured database properties as below:

`spring.datasource.url=jdbc:postgresql://private-db-postgresql--.b.db.ondigitalocean.com:25061/*********DBName

spring.datasource.username=*********

spring.datasource.password=*********

spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.properties.hibernate.default_schema=*********`

when i am trying to run the application getting below error:

Caused by: org.postgresql.util.PSQLException: ERROR: prepared statement "S_3" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:857) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:900) ~[postgresql-42.2.16.jar:42.2.16]
at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:396) ~[HikariCP-3.4.5.jar:na]
at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java) ~[HikariCP-3.4.5.jar:na]
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:121) ~[hibernate-core-5.4.21.Final.jar:5.4.21.Final]
... 27 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~[postgresql-42.2.16.jar:42.2.16]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) ~[postgresql-42.2.16.jar:42.2.16]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.5.jar:na]

[barrywoolgar]

In case it helps anyone else who ends up here from a web search, pgBouncer doesn't support prepared statements (or, I believe, advisory locks) so you need to disable these features in your application.

Edit: Unfortunately I can't help you with this directly, as I was debugging a Rails app when I came across this issue, so don't know how you'd configure this.

[letrthang]

add prepareThreshold=0 to URL string could be like:

url: jdbc:postgresql://url....?sslmode=require&prepareThreshold=0

jpa:
    open-in-view: false
    properties:
      hibernate:
        cache:
          use_query_cache: false
        default_schema: schema_name