I use an HQL query with a projection in a JPA repository.
The following request was executed in a SpringBatch context. It’s a request that is executed in a processor phase (meaning not in an ItemReader component).
This is a simplified version of the query (less columns and less where clauses):
@Query("SELECT new result.MovementResult("
+ " m.quantity, m.instrument, m.account, custodian"
+ ")"
+ " FROM MovementEntity AS m"
+ " LEFT JOIN m.custodianAccount as custodian"
+ " WHERE m.account = :account"
+ " AND m.transaction.tradeDate < :tradeDate ")
Stream<MovementResult> getMovements(AccountEntity account, ZonedDateTime tradeDate);
We had and OOM exception when running this query, after investigate the issue I found that we have a huge ResultSet in memory with all the rows loaded.
The queries returns 98644 rows and that the number of rows the ResultSet retains:
Is hibernate supposed to use server cursor on such queries and to not materialize the whole result set in memory ?
I think it’s enough to declare the result type as a Stream.
My memory pb is increased by another huge read request in the same transaction. It seams the previous resultset is retained in memory by the JDBC connection, even if there is no more local variable referencing it. Any idea of the cause ?
More information on the stack:
SpringBoot 3.5.8
Java25
Hibernate: 6.6.36
I don’t know what Spring Data does, but if you use Query.getResultStream() it will use a “scrollable” ResultSet on the JDBC side. Now, I don’t know what the PostgreSQL driver does exactly, but chances are, it will store all the tuples anyway.
If you know that Query.getResultStream() is called, you can dig into the PGJDBC driver details about what happens.
The scroll Mode is FORWARD_ONLY, I will open a ticket om postgres driver.
This is the full stack trace when the OOM occurs.
at java.util.Arrays.copyOf(Arrays.java:3509)
at java.util.Arrays.copyOf(Arrays.java:3478)
at java.util.ArrayList.grow(ArrayList.java:238)
at java.util.ArrayList.grow(ArrayList.java:245)
at java.util.ArrayList.add(ArrayList.java:484)
at java.util.ArrayList.add(ArrayList.java:497)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2403)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:525)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:435)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:196)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:139)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java:-1)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:251)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:172)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.<init>(JdbcValuesResultSetImpl.java:74)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:355)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:137)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:102)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.scroll(JdbcSelectExecutor.java:195)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$2(ConcreteSqmSelectQueryPlan.java:184)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan$$Lambda/0x0000008001e11b68.interpret(Unknown Source:-1)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:439)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performScroll(ConcreteSqmSelectQueryPlan.java:367)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doScroll(QuerySqmImpl.java:456)
at org.hibernate.query.spi.AbstractSelectionQuery.scroll(AbstractSelectionQuery.java:242)
at org.hibernate.query.spi.AbstractSelectionQuery.stream(AbstractSelectionQuery.java:259)
at org.hibernate.query.spi.AbstractSelectionQuery.getResultStream(AbstractSelectionQuery.java:253)
at java.lang.invoke.LambdaForm$DMH/0x0000008001128000.invokeInterface(LambdaForm$DMH:-1)
at java.lang.invoke.LambdaForm$MH/0x0000008001043800.invoke(LambdaForm$MH:-1)
at java.lang.invoke.Invokers$Holder.invokeExact_MT(Invokers$Holder:-1)
at jdk.internal.reflect.DirectMethodHandleAccessor.invokeImpl(DirectMethodHandleAccessor.java:154)
at jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
at java.lang.reflect.Method.invoke(Method.java:565)
at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:281)
at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:265)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$StreamExecution.doExecute(JpaQueryExecution.java:415)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:95)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:160)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:148)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryQueryMethodInvoker$$Lambda/0x0000008001e62000.invoke(Unknown Source:-1)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:170)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:149)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:69)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.transaction.interceptor.TransactionInterceptor$$Lambda/0x0000008001e04968.proceedWithInvocation(Unknown Source:-1)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
at jdk.proxy2.$Proxy297.getMovementsForFrozenAccountingView(Unknown Source:-1)
at ourproject.core.cbs.domain.accounting.operation.MovementService.getMovementsForFrozenAccountingView(MovementService.java:76)
at java.lang.invoke.LambdaForm$DMH/0x0000008001700000.invokeVirtual(LambdaForm$DMH:-1)
at java.lang.invoke.LambdaForm$MH/0x00000080017a2000.invoke(LambdaForm$MH:-1)
at java.lang.invoke.LambdaForm$MH/0x0000008001371000.invokeExact_MT(LambdaForm$MH:-1)
at jdk.internal.reflect.DirectMethodHandleAccessor.invokeImpl(DirectMethodHandleAccessor.java:157)
at jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
at java.lang.reflect.Method.invoke(Method.java:565)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)
at ourproject.core.cbs.domain.accounting.operation.MovementService$$SpringCGLIB$$0.getMovementsForFrozenAccountingView(<generated>:-1)
at ourproject.core.cbs.domain.position.frozenaccounting.FrozenAccountingPositionCreatorService.getMovements(FrozenAccountingPositionCreatorService.java:100)
at ourproject.core.cbs.domain.position.AbstractSnapshotPositionCreatorService.createPositions(AbstractSnapshotPositionCreatorService.java:77)
at ourproject.core.cbs.domain.position.PositionService.createFrozenPositions(PositionService.java:266)
at ourproject.core.cbs.jobs.cob.snapshotposition.CobSnapshotAccountPositionProcessor.process(CobSnapshotAccountPositionProcessor.java:88)
at java.lang.invoke.LambdaForm$DMH/0x00000080011da800.invokeVirtual(LambdaForm$DMH:-1)
at java.lang.invoke.LambdaForm$MH/0x00000080011d8400.invoke(LambdaForm$MH:-1)
at java.lang.invoke.Invokers$Holder.invokeExact_MT(Invokers$Holder:-1)
at jdk.internal.reflect.DirectMethodHandleAccessor.invokeImpl(DirectMethodHandleAccessor.java:155)
at jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
at java.lang.reflect.Method.invoke(Method.java:565)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:137)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)
at ourproject.core.cbs.jobs.cob.snapshotposition.CobSnapshotAccountPositionProcessor$$SpringCGLIB$$0.process(<generated>:-1)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.doProcess(SimpleChunkProcessor.java:146)
at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.lambda$transform$0(FaultTolerantChunkProcessor.java:228)
at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor$$Lambda/0x0000008002435200.doWithRetry(Unknown Source:-1)
at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:357)
at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:276)
at org.springframework.batch.core.step.item.BatchRetryTemplate.execute(BatchRetryTemplate.java:216)
at org.springframework.batch.core.step.item.FaultTolerantChunkProcessor.transform(FaultTolerantChunkProcessor.java:291)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:220)
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:383)
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:307)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:250)
at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:82)
at org.springframework.batch.repeat.support.TaskExecutorRepeatTemplate$ExecutingRunnable.run(TaskExecutorRepeatTemplate.java:261)
at ourproject.shared.task.TracerTaskDecorator.lambda$decorate$0(TracerTaskDecorator.java:22)
at ourproject.shared.task.TracerTaskDecorator$$Lambda/0x0000008001f84eb8.run(Unknown Source:-1)
at ourproject.shared.task.MemoizerTaskDecorator.lambda$decorate$0(MemoizerTaskDecorator.java:15)
at ourproject.shared.task.MemoizerTaskDecorator$$Lambda/0x0000008001f850e8.run(Unknown Source:-1)
at ourproject.core.common.job.JobAuthenticationTaskDecorator.lambda$decorate$0(JobAuthenticationTaskDecorator.java:30)
at ourproject.core.common.job.JobAuthenticationTaskDecorator$$Lambda/0x0000008002408bf0.run(Unknown Source:-1)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1090)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:614)
at java.lang.Thread.runWith(Thread.java:1487)
at java.lang.Thread.run(Thread.java:1474)
