X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 23FEB52AD2
	for <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 22:25:54 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 21743-08
	for <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	Wed, 20 Jul 2005 01:25:52 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.203])
	by svr1.postgresql.org (Postfix) with ESMTP id 14D6B52AE3
	for <pgsql-jdbc@postgresql.org>; Tue, 19 Jul 2005 22:25:51 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id i24so1306711wra
	for <pgsql-jdbc@postgresql.org>; Tue, 19 Jul 2005 18:25:55 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=jVuyRv1auM3dgcn0iKA3VqMyzev6tyNNQGt7zixj5ZAHwS9Db5un+2NwWqp+o9dqyTimaO9mmGiRtIp5VBh354kf2Y/mMxyzEYrWlsxElWaCTCUFtBjcDSKJsWaIFFKvOEgyBlzjkDjpmr2HL/Bzynwlb8nKxpJ1/yfvRQpVGkM=
Received: by 10.54.68.4 with SMTP id q4mr951708wra;
	Tue, 19 Jul 2005 18:24:42 -0700 (PDT)
Received: by 10.54.52.9 with HTTP; Tue, 19 Jul 2005 18:24:42 -0700 (PDT)
Message-ID: <4ea55c3305071918245a2ef203@mail.gmail.com>
Date: Wed, 20 Jul 2005 07:24:42 +0600
From: Senthoorkumaran Punniamoorthy <senthoor@gmail.com>
Reply-To: Senthoorkumaran Punniamoorthy <senthoor@gmail.com>
To: pgsql-jdbc@postgresql.org
Subject: Re: org.postgresql.util.PSQLException: ERROR: current transaction is
	aborted, commands ignored until end of transaction block
In-Reply-To: <f739e48505071903476eacc1cb@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <4ea55c3305071901563ca33cb8@mail.gmail.com>
	<f739e48505071903476eacc1cb@mail.gmail.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200507/158
X-Sequence-Number: 13306

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,
>=20
> (Sorry my english.)
>=20
> 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:
>=20
> http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html
>=20
> When you catch exception then the transaction is corrupt alredy.
>=20
> try {
>     savepointName =3D this.getClass().getName() + "-" +
> this.getClass().hashCode();
>     savePoint =3D conn.setSavepoint(savepointName);
>     stmt.execute();
> } catch (SQLException sqle) {
>     conn.rollback(savePoint);
> }
>=20
> linimi
>=20
> 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 a=
s
> >
> > Executing SQL: SELECT t0_o.userId, t0_o.deviceId FROM rcp_user_device
> > t0_o WHERE (t0_o.userId =3D ?)
> >
> > This query works on the postgres command line interface fine.
> >
> > SELECT t0_o.userId, t0_o.deviceId BEING_VAULT=3D# SELECT t0_o.userId,
> > t0_o.deviceId FROM rcp_user_device t0_o WHERE (t0_o.userId =3D
> > '94773059650')
> > BEING_VAULT-# ;
> >    userid    |    deviceid
> > -------------+-----------------
> >  94773059650 | 351532002206818
> > (1 row)
> >
> > However JDBC driver is rasing an exception, and below is the stack trac=
e.
> >
> > 2005-07-19 12:14:54,506 DEBUG
> > [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.UserDeviceEntityBean#fin=
dAllByUser]
> > Executing SQL: SELECT t0_o.userId, t0_o.deviceId FROM rcp_user_device
> > t0_o WHERE (t0_o.userId =3D ?)
> > 2005-07-19 12:14:54,509 ERROR
> > [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.UserDeviceEntityBean#fin=
dAllByUser]
> > Find failed
> > org.postgresql.util.PSQLException: ERROR: current transaction is
> > aborted, commands ignored until end of transaction block
> >
> >         at org.postgresql.util.PSQLException.parseServerError(PSQLExcep=
tion.java:139)
> >         at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.ja=
va: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(Abstract=
Jdbc1Statement.java:517)
> >         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(Abstract=
Jdbc2Statement.java:50)
> >         at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(Abs=
tractJdbc1Statement.java:233)
> >         at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.exe=
cuteQuery(WrappedPreparedStatement.java:296)
> >         at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.exec=
ute(JDBCAbstractQueryCommand.java:228)
> >         at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.exec=
ute(JDBCAbstractQueryCommand.java:128)
> >         at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execu=
te(JDBCFindEntitiesCommand.java:40)
> >         at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities=
(JDBCStoreManager.java:598)
> >         at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMP=
PersistenceManager.java:322)
> >         at org.jboss.resource.connectionmanager.CachedConnectionInterce=
ptor.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(NativeMethodAcce=
ssorImpl.java:39)
> >         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe=
thodAccessorImpl.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.invokeHom=
e(EntityContainer.java:1113)
> >         at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(Abstrac=
tInterceptor.java:90)
> >         at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invok=
eHome(EntitySynchronizationInterceptor.java:192)
> >         at org.jboss.resource.connectionmanager.CachedConnectionInterce=
ptor.invokeHome(CachedConnectionInterceptor.java:212)
> >         at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(Abstrac=
tInterceptor.java:90)
> >         at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(E=
ntityInstanceInterceptor.java:117)
> >         at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(Entit=
yLockInterceptor.java:61)
> >         at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(E=
ntityCreationInterceptor.java:28)
> >         at org.jboss.ejb.plugins.CallValidationInterceptor.invokeHome(C=
allValidationInterceptor.java:41)
> >         at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(Abstr=
actTxInterceptor.java:109)
> >         at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(T=
xInterceptorCMT.java:335)
> >         at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxIntercep=
torCMT.java:146)
> >         at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(Securit=
yInterceptor.java:116)
> >         at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogIntercept=
or.java:121)
> >         at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHo=
me(ProxyFactoryFinderInterceptor.java:93)
> >         at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityConta=
iner.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(LocalHomeP=
roxy.java:118)
> >         at $Proxy279.findAllByUser(Unknown Source)
> >         at com.purpleace.ripple.contacts.ejb.session.UnsubscriptionSess=
ionBean.deleteUserDevices(UnsubscriptionSessionBean.java:176)
> >         at com.purpleace.ripple.contacts.ejb.session.UnsubscriptionSess=
ionBean.unsubscribe(UnsubscriptionSessionBean.java:147)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce=
ssorImpl.java:39)
> >         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe=
thodAccessorImpl.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.CachedConnectionInterce=
ptor.invoke(CachedConnectionInterceptor.java:185)
> >         at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.in=
voke(StatelessSessionInstanceInterceptor.java:130)
> >         at org.jboss.webservice.server.ServiceEndpointInterceptor.invok=
e(ServiceEndpointInterceptor.java:51)
> >         at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallV=
alidationInterceptor.java:48)
> >         at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(Abstr=
actTxInterceptor.java:105)
> >         at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(T=
xInterceptorCMT.java:335)
> >         at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorC=
MT.java:166)
> >         at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInt=
erceptor.java:139)
> >         at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.j=
ava:192)
> >         at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(P=
roxyFactoryFinderInterceptor.java:122)
> >         at org.jboss.ejb.SessionContainer.internalInvoke(SessionContain=
er.java:624)
> >         at org.jboss.ejb.Container.invoke(Container.java:873)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce=
ssorImpl.java:39)
> >         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe=
thodAccessorImpl.java:25)
> >         at java.lang.reflect.Method.invoke(Method.java:324)
> >         at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(Reflecte=
dDispatcher.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(AbstractMBea=
nInvoker.java:249)
> >         at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.j=
ava:644)
> >         at org.jboss.invocation.local.LocalInvoker$MBeanServerAction.in=
voke(LocalInvoker.java:155)
> >         at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.=
java:104)
> >         at org.jboss.invocation.InvokerInterceptor.invokeLocal(InvokerI=
nterceptor.java:179)
> >         at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterc=
eptor.java:165)
> >         at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInt=
erceptor.java:46)
> >         at org.jboss.proxy.SecurityInterceptor.invoke(SecurityIntercept=
or.java:55)
> >         at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(State=
lessSessionInterceptor.java:97)
> >         at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:=
86)
> >         at $Proxy379.unsubscribe(Unknown Source)
> >         at com.purpleace.ripple.contacts.ejb.session.MsisdnUpdaterSessi=
onBean.removeOldUser(MsisdnUpdaterSessionBean.java:248)
> >         at com.purpleace.ripple.contacts.ejb.session.MsisdnUpdaterSessi=
onBean.updateWhereUserIdEqualsMsisdn(MsisdnUpdaterSessionBean.java:231)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce=
ssorImpl.java:39)
> >         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe=
thodAccessorImpl.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.CachedConnectionInterce=
ptor.invoke(CachedConnectionInterceptor.java:185)
> >         at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.in=
voke(StatelessSessionInstanceInterceptor.java:130)
> >         at org.jboss.webservice.server.ServiceEndpointInterceptor.invok=
e(ServiceEndpointInterceptor.java:51)
> >         at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallV=
alidationInterceptor.java:48)
> >         at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(Abstr=
actTxInterceptor.java:105)
> >         at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(T=
xInterceptorCMT.java:335)
> >         at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorC=
MT.java:166)
> >         at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInt=
erceptor.java:139)
> >         at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.j=
ava:192)
> >         at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(P=
roxyFactoryFinderInterceptor.java:122)
> >         at org.jboss.ejb.SessionContainer.internalInvoke(SessionContain=
er.java:624)
> >         at org.jboss.ejb.Container.invoke(Container.java:873)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAcce=
ssorImpl.java:39)
> >         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMe=
thodAccessorImpl.java:25)
> >         at java.lang.reflect.Method.invoke(Method.java:324)
> >         at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(Reflecte=
dDispatcher.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(AbstractMBea=
nInvoker.java:249)
> >         at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.j=
ava:644)
> >         at org.jboss.invocation.local.LocalInvoker$MBeanServerAction.in=
voke(LocalInvoker.java:155)
> >         at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.=
java:104)
> >         at org.jboss.invocation.InvokerInterceptor.invokeLocal(InvokerI=
nterceptor.java:179)
> >         at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterc=
eptor.java:165)
> >         at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInt=
erceptor.java:46)
> >         at org.jboss.proxy.SecurityInterceptor.invoke(SecurityIntercept=
or.java:55)
> >         at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(State=
lessSessionInterceptor.java:97)
> >         at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:=
86)
> >         at $Proxy361.updateWhereUserIdEqualsMsisdn(Unknown Source)
> >         at com.purpleace.ripple.contacts.serviceinterface.facade.Change=
MsisdnFacade.changeMsisdn(ChangeMsisdnFacade.java:44)
> >         at com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServe=
let.controller(ChangeMsisdnServelet.java:166)
> >         at com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServe=
let.newHandleRequest(ChangeMsisdnServelet.java:78)
> >         at com.purpleace.ripple.contacts.web.servlets.ChangeMsisdnServe=
let.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.internalDoFi=
lter(ApplicationFilterChain.java:252)
> >         at org.apache.catalina.core.ApplicationFilterChain.doFilter(App=
licationFilterChain.java:173)
> >         at org.apache.catalina.core.ApplicationDispatcher.invoke(Applic=
ationDispatcher.java:672)
> >         at org.apache.catalina.core.ApplicationDispatcher.processReques=
t(ApplicationDispatcher.java:463)
> >         at org.apache.catalina.core.ApplicationDispatcher.doForward(App=
licationDispatcher.java:398)
> >         at org.apache.catalina.core.ApplicationDispatcher.forward(Appli=
cationDispatcher.java:301)
> >         at com.purpleace.ripple.contacts.web.servlets.RippleServlet.con=
troller(RippleServlet.java:271)
> >         at com.purpleace.ripple.contacts.web.servlets.RippleServlet.par=
seString(RippleServlet.java:425)
> >         at com.purpleace.ripple.contacts.web.servlets.RippleServlet.han=
dleRequest(RippleServlet.java:124)
> >         at com.purpleace.ripple.contacts.web.servlets.RippleServlet.doP=
ost(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.internalDoFi=
lter(ApplicationFilterChain.java:252)
> >         at org.apache.catalina.core.ApplicationFilterChain.doFilter(App=
licationFilterChain.java:173)
> >         at com.purpleace.ripple.contacts.web.filters.GlobalFilter.doFil=
ter(GlobalFilter.java:37)
> >         at org.apache.catalina.core.ApplicationFilterChain.internalDoFi=
lter(ApplicationFilterChain.java:202)
> >         at org.apache.catalina.core.ApplicationFilterChain.doFilter(App=
licationFilterChain.java:173)
> >         at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(Repl=
yHeaderFilter.java:81)
> >         at org.apache.catalina.core.ApplicationFilterChain.internalDoFi=
lter(ApplicationFilterChain.java:202)
> >         at org.apache.catalina.core.ApplicationFilterChain.doFilter(App=
licationFilterChain.java:173)
> >         at org.apache.catalina.core.StandardWrapperValve.invoke(Standar=
dWrapperValve.java:213)
> >         at org.apache.catalina.core.StandardContextValve.invoke(Standar=
dContextValve.java:178)
> >         at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(Cu=
stomPrincipalValve.java:39)
> >         at org.jboss.web.tomcat.security.SecurityAssociationValve.invok=
e(SecurityAssociationValve.java:153)
> >         at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccCo=
ntextValve.java:59)
> >         at org.apache.catalina.core.StandardHostValve.invoke(StandardHo=
stValve.java:126)
> >         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorRepo=
rtValve.java:105)
> >         at org.apache.catalina.core.StandardEngineValve.invoke(Standard=
EngineValve.java:107)
> >         at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAd=
apter.java:148)
> >         at org.apache.coyote.http11.Http11Processor.process(Http11Proce=
ssor.java:856)
> >         at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHand=
ler.processConnection(Http11Protocol.java:744)
> >         at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(Poo=
lTcpEndpoint.java:527)
> >         at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(Maste=
rSlaveWorkerThread.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
> >
>