Re: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

Somewhere before the query that threw the exception an error occured.

Fix that and this exception will go away.

Dave
On 19-Jul-05, at 9:24 PM, Senthoorkumaran Punniamoorthy wrote:

> Thanks for responding. However the problem with using this approach
> would be since we are using container managed transaction and
> persistance (CMP) the application doesnt have control over how the
> SQLs are issued and executed. All managed by Jboss. In situations like
> this what could be a viable alternative?
>
> Regards,
> Senthoor
>
> On 7/19/05, im i <linimi(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> (Sorry my english.)
>>
>> I think, the problem is not in query. There is other statement what
>> corrupt the transaction before query, example insert or update. The
>> postgres 8. solve the problem with savepoint, you can create
>> savepoint
>> before critical statement and when error occured you need rollback to
>> savepoint:
>>
>> http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html
>>
>> When you catch exception then the transaction is corrupt alredy.
>>
>> try {
>> savepointName = this.getClass().getName() + "-" +
>> this.getClass().hashCode();
>> savePoint = conn.setSavepoint(savepointName);
>> stmt.execute();
>> } catch (SQLException sqle) {
>> conn.rollback(savePoint);
>> }
>>
>> linimi
>>
>> On 7/19/05, Senthoorkumaran Punniamoorthy <senthoor(at)gmail(dot)com> wrote:
>>
>>> We are using postgres as our DB for our application based on EJB.
>>> I am
>>> using the pg74.216.jdbc3.jar driver.
>>>
>>> one of the EJB-QL in the finder method translates to a query in
>>> JBoss as
>>>
>>> Executing SQL: SELECT t0_o.userId, t0_o.deviceId FROM
>>> rcp_user_device
>>> t0_o WHERE (t0_o.userId = ?)
>>>
>>> This query works on the postgres command line interface fine.
>>>
>>> SELECT t0_o.userId, t0_o.deviceId BEING_VAULT=# SELECT t0_o.userId,
>>> t0_o.deviceId FROM rcp_user_device t0_o WHERE (t0_o.userId =
>>> '94773059650')
>>> BEING_VAULT-# ;
>>> userid | deviceid
>>> -------------+-----------------
>>> 94773059650 | 351532002206818
>>> (1 row)
>>>
>>> However JDBC driver is rasing an exception, and below is the
>>> stack trace.
>>>
>>> 2005-07-19 12:14:54,506 DEBUG
>>> [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.UserDeviceEntityBean#
>>> findAllByUser]
>>> Executing SQL: SELECT t0_o.userId, t0_o.deviceId FROM
>>> rcp_user_device
>>> t0_o WHERE (t0_o.userId = ?)
>>> 2005-07-19 12:14:54,509 ERROR
>>> [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.UserDeviceEntityBean#
>>> findAllByUser]
>>> Find failed
>>> org.postgresql.util.PSQLException: ERROR: current transaction is
>>> aborted, commands ignored until end of transaction block
>>>
>>> at org.postgresql.util.PSQLException.parseServerError
>>> (PSQLException.java:139)
>>> at org.postgresql.core.QueryExecutor.executeV3
>>> (QueryExecutor.java:152)
>>> at org.postgresql.core.QueryExecutor.execute
>>> (QueryExecutor.java:100)
>>> at org.postgresql.core.QueryExecutor.execute
>>> (QueryExecutor.java:43)
>>> at org.postgresql.jdbc1.AbstractJdbc1Statement.execute
>>> (AbstractJdbc1Statement.java:517)
>>> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
>>> (AbstractJdbc2Statement.java:50)
>>> at
>>> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery
>>> (AbstractJdbc1Statement.java:233)
>>> at
>>> org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuer
>>> y(WrappedPreparedStatement.java:296)
>>> at
>>> org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute
>>> (JDBCAbstractQueryCommand.java:228)
>>> at
>>> org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute
>>> (JDBCAbstractQueryCommand.java:128)
>>> at
>>> org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute
>>> (JDBCFindEntitiesCommand.java:40)
>>> at
>>> org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities
>>> (JDBCStoreManager.java:598)
>>> at
>>> org.jboss.ejb.plugins.CMPPersistenceManager.findEntities
>>> (CMPPersistenceManager.java:322)
>>> at
>>> org.jboss.resource.connectionmanager.CachedConnectionInterceptor.fin
>>> dEntities(CachedConnectionInterceptor.java:321)
>>> at org.jboss.ejb.EntityContainer.findLocal
>>> (EntityContainer.java:649)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
>>> Method)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke
>>> (NativeMethodAccessorImpl.java:39)
>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke
>>> (DelegatingMethodAccessorImpl.java:25)
>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>> at org.jboss.invocation.Invocation.performCall
>>> (Invocation.java:345)
>>> at org.jboss.ejb.EntityContainer
>>> $ContainerInterceptor.invokeHome(EntityContainer.java:1113)
>>> at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome
>>> (AbstractInterceptor.java:90)
>>> at
>>> org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome
>>> (EntitySynchronizationInterceptor.java:192)
>>> at
>>> org.jboss.resource.connectionmanager.CachedConnectionInterceptor.inv
>>> okeHome(CachedConnectionInterceptor.java:212)
>>> at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome
>>> (AbstractInterceptor.java:90)
>>> at
>>> org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome
>>> (EntityInstanceInterceptor.java:117)
>>> at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome
>>> (EntityLockInterceptor.java:61)
>>> at
>>> org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome
>>> (EntityCreationInterceptor.java:28)
>>> at
>>> org.jboss.ejb.plugins.CallValidationInterceptor.invokeHome
>>> (CallValidationInterceptor.java:41)
>>> at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
>>> (AbstractTxInterceptor.java:109)
>>> at
>>> org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions
>>> (TxInterceptorCMT.java:335)
>>> at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome
>>> (TxInterceptorCMT.java:146)
>>> at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome
>>> (SecurityInterceptor.java:116)
>>> at org.jboss.ejb.plugins.LogInterceptor.invokeHome
>>> (LogInterceptor.java:121)
>>> at
>>> org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome
>>> (ProxyFactoryFinderInterceptor.java:93)
>>> at org.jboss.ejb.EntityContainer.internalInvokeHome
>>> (EntityContainer.java:508)
>>> at org.jboss.ejb.Container.invoke(Container.java:894)
>>> at
>>> org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome
>>> (BaseLocalProxyFactory.java:342)
>>> at org.jboss.ejb.plugins.local.LocalHomeProxy.invoke
>>> (LocalHomeProxy.java:118)
>>> at $Proxy279.findAllByUser(Unknown Source)
>>> at
>>> com.purpleace.ripple.contacts.ejb.session.UnsubscriptionSessionBean.
>>> deleteUserDevices(UnsubscriptionSessionBean.java:176)
>>> at
>>> com.purpleace.ripple.contacts.ejb.session.UnsubscriptionSessionBean.
>>> unsubscribe(UnsubscriptionSessionBean.java:147)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
>>> Method)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke
>>> (NativeMethodAccessorImpl.java:39)
>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke
>>> (DelegatingMethodAccessorImpl.java:25)
>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>> at org.jboss.invocation.Invocation.performCall
>>> (Invocation.java:345)
>>> at org.jboss.ejb.StatelessSessionContainer
>>> $ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
>>> at
>>> org.jboss.resource.connectionmanager.CachedConnectionInterceptor.inv
>>> oke(CachedConnectionInterceptor.java:185)
>>> at
>>> org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke
>>> (StatelessSessionInstanceInterceptor.java:130)
>>> at
>>> org.jboss.webservice.server.ServiceEndpointInterceptor.invoke
>>> (ServiceEndpointInterceptor.java:51)
>>> at org.jboss.ejb.plugins.CallValidationInterceptor.invoke
>>> (CallValidationInterceptor.java:48)
>>> at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
>>> (AbstractTxInterceptor.java:105)
>>> at
>>> org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions
>>> (TxInterceptorCMT.java:335)
>>> at org.jboss.ejb.plugins.TxInterceptorCMT.invoke
>>> (TxInterceptorCMT.java:166)
>>> at org.jboss.ejb.plugins.SecurityInterceptor.invoke
>>> (SecurityInterceptor.java:139)
>>> at org.jboss.ejb.plugins.LogInterceptor.invoke
>>> (LogInterceptor.java:192)
>>> at
>>> org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke
>>> (ProxyFactoryFinderInterceptor.java:122)
>>> at org.jboss.ejb.SessionContainer.internalInvoke
>>> (SessionContainer.java:624)
>>> at org.jboss.ejb.Container.invoke(Container.java:873)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
>>> Method)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke
>>> (NativeMethodAccessorImpl.java:39)
>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke
>>> (DelegatingMethodAccessorImpl.java:25)
>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>> at org.jboss.mx.interceptor.ReflectedDispatcher.invoke
>>> (ReflectedDispatcher.java:141)
>>> at org.jboss.mx.server.Invocation.dispatch
>>> (Invocation.java:80)
>>> at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
>>> at org.jboss.mx.server.AbstractMBeanInvoker.invoke
>>> (AbstractMBeanInvoker.java:249)
>>> at org.jboss.mx.server.MBeanServerImpl.invoke
>>> (MBeanServerImpl.java:644)
>>> at org.jboss.invocation.local.LocalInvoker
>>> $MBeanServerAction.invoke(LocalInvoker.java:155)
>>> at org.jboss.invocation.local.LocalInvoker.invoke
>>> (LocalInvoker.java:104)
>>> at org.jboss.invocation.InvokerInterceptor.invokeLocal
>>> (InvokerInterceptor.java:179)
>>> at org.jboss.invocation.InvokerInterceptor.invoke
>>> (InvokerInterceptor.java:165)
>>> at org.jboss.proxy.TransactionInterceptor.invoke
>>> (TransactionInterceptor.java:46)
>>> at org.jboss.proxy.SecurityInterceptor.invoke
>>> (SecurityInterceptor.java:55)
>>> at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke
>>> (StatelessSessionInterceptor.java:97)
>>> at org.jboss.proxy.ClientContainer.invoke
>>> (ClientContainer.java:86)
>>> at $Proxy379.unsubscribe(Unknown Source)
>>> at
>>> com.purpleace.ripple.contacts.ejb.session.MsisdnUpdaterSessionBean.r
>>> emoveOldUser(MsisdnUpdaterSessionBean.java:248)
>>> at
>>> com.purpleace.ripple.contacts.ejb.session.MsisdnUpdaterSessionBean.u
>>> pdateWhereUserIdEqualsMsisdn(MsisdnUpdaterSessionBean.java:231)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
>>> Method)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke
>>> (NativeMethodAccessorImpl.java:39)
>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke
>>> (DelegatingMethodAccessorImpl.java:25)
>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>> at org.jboss.invocation.Invocation.performCall
>>> (Invocation.java:345)
>>> at org.jboss.ejb.StatelessSessionContainer
>>> $ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
>>> at
>>> org.jboss.resource.connectionmanager.CachedConnectionInterceptor.inv
>>> oke(CachedConnectionInterceptor.java:185)
>>> at
>>> org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke
>>> (StatelessSessionInstanceInterceptor.java:130)
>>> at
>>> org.jboss.webservice.server.ServiceEndpointInterceptor.invoke
>>> (ServiceEndpointInterceptor.java:51)
>>> at org.jboss.ejb.plugins.CallValidationInterceptor.invoke
>>> (CallValidationInterceptor.java:48)
>>> at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
>>> (AbstractTxInterceptor.java:105)
>>> at
>>> org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions
>>> (TxInterceptorCMT.java:335)
>>> at org.jboss.ejb.plugins.TxInterceptorCMT.invoke
>>> (TxInterceptorCMT.java:166)
>>> at org.jboss.ejb.plugins.SecurityInterceptor.invoke
>>> (SecurityInterceptor.java:139)
>>> at org.jboss.ejb.plugins.LogInterceptor.invoke
>>> (LogInterceptor.java:192)
>>> at
>>> org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke
>>> (ProxyFactoryFinderInterceptor.java:122)
>>> at org.jboss.ejb.SessionContainer.internalInvoke
>>> (SessionContainer.java:624)
>>> at org.jboss.ejb.Container.invoke(Container.java:873)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native
>>> Method)
>>> at sun.reflect.NativeMethodAccessorImpl.invoke
>>> (NativeMethodAccessorImpl.java:39)
>>> at sun.reflect.DelegatingMethodAccessorImpl.invoke
>>> (DelegatingMethodAccessorImpl.java:25)
>>> at java.lang.reflect.Method.invoke(Method.java:324)
>>> at org.jboss.mx.interceptor.ReflectedDispatcher.invoke
>>> (ReflectedDispatcher.java:141)
>>> at org.jboss.mx.server.Invocation.dispatch
>>> (Invocation.java:80)
>>> at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
>>> at org.jboss.mx.server.AbstractMBeanInvoker.invoke
>>> (AbstractMBeanInvoker.java:249)
>>> at org.jboss.mx.server.MBeanServerImpl.invoke
>>> (MBeanServerImpl.java:644)
>>> at org.jboss.invocation.local.LocalInvoker
>>> $MBeanServerAction.invoke(LocalInvoker.java:155)
>>> at org.jboss.invocation.local.LocalInvoker.invoke
>>> (LocalInvoker.java:104)
>>> at org.jboss.invocation.InvokerInterceptor.invokeLocal
>>> (InvokerInterceptor.java:179)
>>> at org.jboss.invocation.InvokerInterceptor.invoke
>>> (InvokerInterceptor.java:165)
>>> at org.jboss.proxy.TransactionInterceptor.invoke
>>> (TransactionInterceptor.java:46)
>>> at org.jboss.proxy.SecurityInterceptor.invoke
>>> (SecurityInterceptor.java:55)
>>> at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke
>>> (StatelessSessionInterceptor.java:97)
>>> at org.jboss.proxy.ClientContainer.invoke
>>> (ClientContainer.java:86)
>>> at $Proxy361.updateWhereUserIdEqualsMsisdn(Unknown Source)
>>> at
>>> com.purpleace.ripple.contacts.serviceinterface.facade.ChangeMsisdnFa
>>> cade.changeMsisdn(ChangeMsisdnFacade.java:44)
>>> at
>>> com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServelet.cont
>>> roller(ChangeMsisdnServelet.java:166)
>>> at
>>> com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServelet.newH
>>> andleRequest(ChangeMsisdnServelet.java:78)
>>> at
>>> com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServelet.doPo
>>> st(ChangeMsisdnServelet.java:61)
>>> at javax.servlet.http.HttpServlet.service
>>> (HttpServlet.java:717)
>>> at javax.servlet.http.HttpServlet.service
>>> (HttpServlet.java:810)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
>>> (ApplicationFilterChain.java:252)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.doFilter
>>> (ApplicationFilterChain.java:173)
>>> at org.apache.catalina.core.ApplicationDispatcher.invoke
>>> (ApplicationDispatcher.java:672)
>>> at
>>> org.apache.catalina.core.ApplicationDispatcher.processRequest
>>> (ApplicationDispatcher.java:463)
>>> at
>>> org.apache.catalina.core.ApplicationDispatcher.doForward
>>> (ApplicationDispatcher.java:398)
>>> at org.apache.catalina.core.ApplicationDispatcher.forward
>>> (ApplicationDispatcher.java:301)
>>> at
>>> com.purpleace.ripple.contacts.web.servlets.RippleServlet.controller(
>>> RippleServlet.java:271)
>>> at
>>> com.purpleace.ripple.contacts.web.servlets.RippleServlet.parseString
>>> (RippleServlet.java:425)
>>> at
>>> com.purpleace.ripple.contacts.web.servlets.RippleServlet.handleReque
>>> st(RippleServlet.java:124)
>>> at
>>> com.purpleace.ripple.contacts.web.servlets.RippleServlet.doPost
>>> (RippleServlet.java:70)
>>> at javax.servlet.http.HttpServlet.service
>>> (HttpServlet.java:717)
>>> at javax.servlet.http.HttpServlet.service
>>> (HttpServlet.java:810)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
>>> (ApplicationFilterChain.java:252)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.doFilter
>>> (ApplicationFilterChain.java:173)
>>> at
>>> com.purpleace.ripple.contacts.web.filters.GlobalFilter.doFilter
>>> (GlobalFilter.java:37)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
>>> (ApplicationFilterChain.java:202)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.doFilter
>>> (ApplicationFilterChain.java:173)
>>> at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter
>>> (ReplyHeaderFilter.java:81)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
>>> (ApplicationFilterChain.java:202)
>>> at
>>> org.apache.catalina.core.ApplicationFilterChain.doFilter
>>> (ApplicationFilterChain.java:173)
>>> at org.apache.catalina.core.StandardWrapperValve.invoke
>>> (StandardWrapperValve.java:213)
>>> at org.apache.catalina.core.StandardContextValve.invoke
>>> (StandardContextValve.java:178)
>>> at
>>> org.jboss.web.tomcat.security.CustomPrincipalValve.invoke
>>> (CustomPrincipalValve.java:39)
>>> at
>>> org.jboss.web.tomcat.security.SecurityAssociationValve.invoke
>>> (SecurityAssociationValve.java:153)
>>> at org.jboss.web.tomcat.security.JaccContextValve.invoke
>>> (JaccContextValve.java:59)
>>> at org.apache.catalina.core.StandardHostValve.invoke
>>> (StandardHostValve.java:126)
>>> at org.apache.catalina.valves.ErrorReportValve.invoke
>>> (ErrorReportValve.java:105)
>>> at org.apache.catalina.core.StandardEngineValve.invoke
>>> (StandardEngineValve.java:107)
>>> at org.apache.catalina.connector.CoyoteAdapter.service
>>> (CoyoteAdapter.java:148)
>>> at org.apache.coyote.http11.Http11Processor.process
>>> (Http11Processor.java:856)
>>> at org.apache.coyote.http11.Http11Protocol
>>> $Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
>>> at
>>> org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket
>>> (PoolTcpEndpoint.java:527)
>>> at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run
>>> (MasterSlaveWorkerThread.java:112)
>>> at java.lang.Thread.run(Thread.java:534)
>>>
>>> Can you help me identify this issue?
>>>
>>> Regards,
>>> Senthoor
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 5: don't forget to increase your free space map settings
>>>
>>>
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>