Re: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block - Mailing list pgsql-jdbc

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@gmail.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@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
> >
>