Hi, We are currently having a problem that our Postgres DB is throwing an SQL error which states that it's 'out of memory'. What we have is a DB with 1 table that has 3.9 million records. We need to find certain records that are to be processed by a Java App so we do a "select id from table where type=a and condition in (1, 2) order by id limit 2000". When this query gets executed we see the memory on the DB Server increasing and after it has finishes it drops a bit but we see it growing a few MB per few minutes. This has caused an out of memory after the system has been processing for a day or 2. The query is heavy because of the order by but that does not explain why the memory is increasing. We use a DAO which extends HibernateDaoSupport and the method (findIds) has been marked as propagation_required. So we assume Spring manages the transaction and thus closing of the resultset. Here's part of the DAO method: Query q = getSession(false).createQuery(query); q.setMaxResults(RESULT_SIZE); List list = q.list(); No rocketscience but somehow this causes a problem on the DB Server. Does anyone have an idea where to look? Thanks, Maarten ps: Java stacktrace: *** [WARN 2006-03-11 20:04:26,288 main] org.hibernate.util.JDBCExceptionReporter.logExcept ions(JDBCExceptionReporter.java:71) SQL Error: 0, SQLState: null *** [ERROR 2006-03-11 20:04:26,292 main] org.hibernate.util.JDBCExceptionReporter.logExcept ions(JDBCExceptionReporter.java:72) Batch entry 0 update activiteit set activiteit_type=102, activiteit_subtype=1305, start_dt=2006-03-11 20:03:40.940000+0100, moeder_id=NULL, toestand=8022, laatste_actie_dt=2006-03-11 20:03:41.012000+0100, uiterlijke_actie_dt=2006-03-11 20:03:40.940000+0100, pl_id=NULL, communicatie_partner=1905, nr_1=NULL, nr_2=NULL, nr_3=NULL, nr_4=NULL, nr_5=NULL, tekst_1=NULL, tekst_2=NULL, tekst_3=NULL, tekst_4=NULL, tekst_5=NULL where activiteit_id=11079994 was aborted. Call getNextException to see the cause. *** [WARN 2006-03-11 20:04:26,293 main] org.hibernate.util.JDBCExceptionReporter.logExcept ions(JDBCExceptionReporter.java:71) SQL Error: 0, SQLState: 53200 *** [ERROR 2006-03-11 20:04:26,293 main] org.hibernate.util.JDBCExceptionReporter.logExcept ions(JDBCExceptionReporter.java:72) ERROR: out of memory *** [ERROR 2006-03-11 20:04:26,318 main] org.hibernate.event.def.AbstractFlushingEventListe ner.performExecutions(AbstractFlushingEventListene r.java:277) Could not synchronize database state with session org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update at org.hibernate.exception.SQLStateConverter.handledN onSpecificException(SQLStateConverter.java:82) at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:43) at org.hibernate.jdbc.AbstractBatcher.executeBatch(Ab stractBatcher.java:181) at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:226) at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:137) at org.hibernate.event.def.AbstractFlushingEventListe ner.performExecutions(AbstractFlushingEventListene r.java:274) at org.hibernate.event.def.DefaultFlushEventListener. onFlush(DefaultFlushEventListener.java:27) at org.hibernate.impl.SessionImpl.flush(SessionImpl.j ava:730) at org.hibernate.impl.SessionImpl.managedFlush(Sessio nImpl.java:324) at org.hibernate.transaction.JDBCTransaction.commit(J DBCTransaction.java:86) at org.springframework.orm.hibernate3.HibernateTransa ctionManager.doCommit(HibernateTransactionManager. java:490) at org.springframework.transaction.support.AbstractPl atformTransactionManager.processCommit(AbstractPla tformTransactionManager.java:495) at org.springframework.transaction.support.AbstractPl atformTransactionManager.commit(AbstractPlatformTr ansactionManager.java:468) at org.springframework.transaction.interceptor.Transa ctionAspectSupport.doCommitTransactionAfterReturni ng(TransactionAspectSupport.java:258) at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:106) at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :144) at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:174) at $Proxy3.invokeWithinNewTx(Unknown Source) at nl.ictu.spg.service.workflow.ActiviteitCMP.persist NowWithNewGebeurtenis(ActiviteitCMP.java:546) at nl.ictu.spg.service.workflow.WorkflowActiviteit.pe rsistNowWithNewGebeurtenis(WorkflowActiviteit.java :320) at nl.ictu.spg.service.workflow.berichten.LO3.LO3Beri chtDispatcher.executeCycle(LO3BerichtDispatcher.ja va:224) at nl.ictu.spg.service.workflow.berichten.LO3.LO3Beri chtDispatcher.dispatch(LO3BerichtDispatcher.java:1 77) at nl.ictu.spg.service.workflow.berichten.LO3.LO3Beri chtDispatcher.dispatch(LO3BerichtDispatcher.java:2 56) at nl.ictu.spg.service.request.lo3.LO3RequestProcesso r.runOnce(LO3RequestProcessor.java:194) at nl.ictu.spg.service.plupdate.PlUpdateBackgroundSer vice.main(PlUpdateBackgroundService.java:69) Caused by: java.sql.BatchUpdateException: Batch entry 0 update activiteit set activiteit_type=102, activiteit_subtype=1305, start_dt=2006-03-11 20:03:40.940000+0100, moeder_id=NULL, toestand=8022, laatste_actie_dt=2006-03-11 20:03:41.012000+0100, uiterlijke_actie_dt=2006-03-11 20:03:40.940000+0100, pl_id=NULL, communicatie_partner=1905, nr_1=NULL, nr_2=NULL, nr_3=NULL, nr_4=NULL, nr_5=NULL, tekst_1=NULL, tekst_2=NULL, tekst_3=NULL, tekst_4=NULL, tekst_5=NULL where activiteit_id=11079994 was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchR esultHandler.handleError(AbstractJdbc2Statement.ja va:2392) at org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1257) at org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:334) at org.postgresql.jdbc2.AbstractJdbc2Statement.execut eBatch(AbstractJdbc2Statement.java:2451) at org.apache.commons.dbcp.DelegatingStatement.execut eBatch(DelegatingStatement.java:294) at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch( BatchingBatcher.java:57) at org.hibernate.jdbc.AbstractBatcher.executeBatch(Ab stractBatcher.java:174) ... 22 more __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
