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

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@gmail.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.executeQuery(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.findEntities(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.invokeHome(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.invoke(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.removeOldUser(MsisdnUpdaterSessionBean.java:248)
>         at
com.purpleace.ripple.contacts.ejb.session.MsisdnUpdaterSessionBean.updateWhereUserIdEqualsMsisdn(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.invoke(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.ChangeMsisdnFacade.changeMsisdn(ChangeMsisdnFacade.java:44)
>         at com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServelet.controller(ChangeMsisdnServelet.java:166)
>         at
com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServelet.newHandleRequest(ChangeMsisdnServelet.java:78)
>         at com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServelet.doPost(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.handleRequest(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
>