PSQLException: This connection has been closed - Spring Boot + AWS RDS + Postgres
12,677
It looks like the connection is lost between DB and pool. Try to set up removeAbandoned and removeAbandonedTimeout options. For more datails about those options read JDBC Connection Pool docs.
Comments
-
Marsellus Wallace almost 2 years
I am getting
PSQLException: This connection has been closed
when trying to access my Postgres database in AWS RDS. The exception happens randomly (not all the times I run the code) and it's more probable to happen if it takes a while before the execution reaches the code below:@Transactional public void revokeChanges(ArrayList<Integer> changeIds) { jdbcTemplate.batchUpdate(sqlUpdate, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setStuff.. ... } @Override public int getBatchSize() { return changeIds.size(); } }); jdbcTemplate.batchUpdate(sqlInsert, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setStuff... } @Override public int getBatchSize() { return changeIds.size(); } });
Connection Stuff:
spring.datasource.url=jdbc:postgresql://my-url:port/dbName?tcpKeepAlive=true&autoReconnect=true
spring.datasource.driver-class-name=org.postgresql.Driver
Relevant POM:
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.1.9.RELEASE</version> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>9.3-1102-jdbc41</version> </dependency>
Stacktrace:
org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.postgresql.util.PSQLException: This connection has been closed. at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:296) at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320) at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:214) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:134) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.(SQLErrorCodeSQLExceptionTranslator.java:97) at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:673) at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:989) at my.package.DataServiceJdbc.revokeChanges(DataServiceJdbc.java:71) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy24.revokeChanges(Unknown Source) at my.package.DataExporterS3ToPostgre.exportData(DataExporterS3ToPostgre.java:59) at my.package.Application.main(Application.java:41) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134) Caused by: org.postgresql.util.PSQLException: This connection has been closed. at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:843) at org.postgresql.jdbc4.Jdbc4Connection.getMetaData(Jdbc4Connection.java:54) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80) at com.sun.proxy.$Proxy23.getMetaData(Unknown Source) at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:285) ... 29 common frames omitted