Re: error - NOTICE: current transaction...MORE DETAIL... - Mailing list pgsql-jdbc

that's a good point...when this error occurs, i suppose the connection is
defunct and any transaction is already lost.

chris

-----Original Message-----
From: admin [mailto:admin]On Behalf Of Antonio Fiol Bonnin
Sent: Thursday, September 06, 2001 11:47 AM
To: cmarkiew@commnav.com
Cc: T.R.Missner@Level3.com; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


I understand.

However, doesn't the exception automatically do some kind of rollback? I
can't
remember where I read such a thing...

Antonio

chris markiewicz wrote:

> antonio
>
> thanks.  it actually does apply to some degree.  i have noticed that
rolling
> back the connection will sometimes prevent subsequent errors, but for some
> reason it is not completely predictable...
>
> the bigger issue is that the db is throwing errors that i don't
understand.
> i might have just discovered a pattern - i'll post it as soon as i am more
> certain of it.  but rolling back whenever i get an unpredictable exception
> will kill any transaction.  this is a problem.   it will also require some
> localized but non-trivial rework in the persistence layer.  i really don't
> want to do that unless i have no other avenues.
>
> thanks for your response.
>
> chris
>
> -----Original Message-----
> From: admin [mailto:admin]On Behalf Of Antonio Fiol Bonnin
> Sent: Thursday, September 06, 2001 11:18 AM
> To: T.R.Missner@Level3.com
> Cc: cmarkiew@commnav.com; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
>
> I'm no expert at all, but, --forgive me!!-- have you tried to roll back
upon
> the first exception you get?
>
> Or maybe try to get the warnings in your ResultSet to see if something is
> there, though I do not remember having done so.
>
> I remember having something similar when I tried to read from a table that
> did
> not exist (mangled name). That request throwed an exception, and if I
> ignored
> the exception, all subsequent requests did show this kind of behaviour, or
a
> similar one.
>
> Forget my message if it doesn't apply to your problem, please.
>
> Antonio Fiol
>
> T.R.Missner@Level3.com wrote:
>
> > I really don't understand why this is happening either.
> > my current guess is that there is something going on
> > in the way we are reusing connections.  To answer your
> > question I don't think there is any relationship between the
> > 2 connections just that after the first one is corrupted
> > I use the second one until it gets corrupted, then
> > create new ones and start again.  I have to assume
> > that we are doing something wrong in our use of
> > these connections and am focusing my effort in that
> > direction right now.
> >
> > Sure wish one of the experts would chime in on this.
> >
> > t.r.
> >
> > -----Original Message-----
> > From: chris markiewicz [mailto:cmarkiew@commnav.com]
> > Sent: Thursday, September 06, 2001 7:17 AM
> > To: Missner, T. R.; pgsql-jdbc@postgresql.org;
> > pgsql-jdbc-owner@postgresql.org
> > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
> >
> > tr
> >
> > things have gotten a little worse...i recently upgraded to postgresql
7.1
> > and now i am seeing this behavior even WITHOUT large objects.  it
happens
> > much less frequently without LOs, but it still happens.  i never saw
this
> > behavior in 7.0.  are you seeing the same behavior?
> >
> > i guess i don't understand the following:  right, each connection is a
> > different process.  so if you use the second connection, why should it
> fail
> > too?  it should have no relationship to the first connection, since it
is
> a
> > different process.
> > i was discussing this with a co-worker and he suggested that the first
> > connection might be "spilling" into the second (when you grab the first
> two
> > connections, they may inhabit contiguous memory spaces...the first one
> gets
> > corrupted and corrupts the second...i admit that i am no unix expert so
> > while i can picture such a thing, i have no idea whether or not it is
> > possible.)  anyway, you grab the third connection and it's not corrupt.
> > does that make any sense?
> >
> > chris
> >
> > -----Original Message-----
> > From: T.R.Missner@Level3.com [mailto:T.R.Missner@Level3.com]
> > Sent: Wednesday, September 05, 2001 12:45 PM
> > To: cmarkiew@commnav.com; pgsql-jdbc@postgresql.org;
> > pgsql-jdbc-owner@postgresql.org
> > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
> >
> > What I have is a connection pool.
> > I initially grab 2 connections, one primary one secondary.
> > If the insert fails on the primary connection I try the
> > secondary, if it fails I simply delete the 2 I have and grab
> > 2 more from the connection pool.  After creating the new ones
> > I have never seen it fail the first few times which is what
> > leads me to believe that something in the connection itself is
> > getting corrupted over time.  Note that each connection is a separate
> > process in the unix environment.  I believe it is this process that
> > is getting corrupted note necessarily the java connection wrapper.
> >
> > Of course this is all guess work right now.  I'll let you know if I
> > come up with a better solution or are able to determine why
> > these connections act like they are getting corrupt.
> >
> > -----Original Message-----
> > From: chris markiewicz [mailto:cmarkiew@commnav.com]
> > Sent: Wednesday, September 05, 2001 5:42 AM
> > To: Missner, T. R.; pgsql-jdbc-owner@postgresql.org
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
> >
> > follow-up question - you say that if you get one exception, you try the
> > insert with another connection...if that doesn't work you delete the
> > existing connections and get new ones...
> >
> > i must be missing something - why wouldn't using the second connection
> > always work?  if you get rid of the offending connection (the first
one),
> > why does the second one have a problem?  or am i confusing your
connection
> > object with the actual connection?
> >
> > thanks
> > chris
> >
> > -----Original Message-----
> > From: T.R.Missner@Level3.com [mailto:T.R.Missner@Level3.com]
> > Sent: Tuesday, September 04, 2001 3:35 PM
> > To: cmarkiew@commnav.com; pgsql-jdbc-owner@postgresql.org
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
> >
> > Chris,
> >
> > I had the same problem on the insert side and thought it went away but
> > it is back and I am convinced it is related to more than one thread
using
> > the same connection at the same time.  What I have done as a work around
> > in the interim ( until I write my own connection manager class ) is
catch
> > the exception and try the insert again with another connection.  If it
> still
> >
> > fails I delete the connections I have and create new ones and the insert
> > ALWAYS works after that.  So it appears to me that something is
happening
> > to my connection object that is causing it to get corrupted to some
> extent.
> > Once a connection throws this exception it seems much more likely to do
so
> > again which is why I am deleting connections and creating new ones if I
> have
> > 2 failures back to back.  This isn't the answer you are looking for but
> > I hoped it might help you think of other ways to troubleshoot your
> problem.
> >
> > Since I put this work around in I have been able to run a very high rate
> > and haven't lost any data on the insert side.  My guess is that these
> > Fastpath
> > transactions are very slow compared to non fastpath transactions and it
> > is easy to use a connection that hasn't finished what it is doing.
> >
> > I even used a synchronized method to hopefully block on the connection
> > but this didn't fix the problem either.  Must be some threading going on
> > in the connection class.  I haven't had the time to go through the code
> > but I will at some point.
> >
> > I know someone out there has some insight that could help.
> >
> > t.r. missner
> > level(3) communications
> >
> > -----Original Message-----
> > From: chris markiewicz [mailto:cmarkiew@commnav.com]
> > Sent: Tuesday, September 04, 2001 11:40 AM
> > To: cmarkiew@commnav.com; 'Postgres Jdbc (E-mail)'
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...
> >
> > hello.  i have some more details on this problem...i found the reference
> to
> > calling rollback when you get this sort of error.
> > (http://fts.postgresql.org/db/mw/msg.html?mid=1030879)... i have started
> > doing that, but i am still getting the problem.
> >
> > i have the setup shown below...i've learned that i can usually get
around
> > the fastpath error if i try the query a few times...so i make it call up
> to
> > 10 times if there is an error...the po.load() call runs the (SELECT
> > versionid, versionid, versioncomment, versionlabel, creatorid,
documentid,
> > versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034272) query from the last message
i
> > sent (see below)...if that throws an exception, i catch it, rollback,
and
> > contine...i am still seeing the same behavior though...
> >
> >     public void loadComplex(User user) throws SBHException {
> >         int iterations = 10;
> >         try {
> >             //## TODO:
> >             //## occasionally get a fastpath exception here...try to
> >             //## call load() as many as 10 times if there is an
> exception...
> >             //## this is here only as a test...
> >             //## chris markiewicz
> >             int i = 0;
> >             boolean exceptionfound = false;
> >             boolean goodResult = false;
> >             while (!goodResult && i < iterations) {
> >                 try {
> >                     i++;
> >                     dv = (DocumentVersion)po.load(versionid, user);
> >                     goodResult = true;
> >                 } catch (Exception e) {
> >                     System.out.println("SBHDocument.loadComplex
exception
> > attempt:"+i);
> >                     try {po.rollback(user);} catch (Exception e3)
{...log
> > here...}
> >                     exceptionfound = true;
> >                     Log.log("SBHDocument.loadComplex attempt:"+i);
> >                     Log.log("SBHDocument.loadComplex e:"+e);
> >                     if (i == (iterations - 1)) {
> >                         throw e;
> >                     }
> >                 }
> >             }
> >             if (exceptionfound) {
> >                 ...log here...
> >             }
> >         } catch (TransactionException te) {
> >             throw te;
> >         } catch (SBHSecurityException sse) {
> >             throw sse;
> >         } catch (SBHException sbe) {
> >             throw sbe;
> >         } catch (Exception e) {
> >             throw new SBHException(e);
> >         }
> >     }
> >
> > here is the log calling that query many times:
> >
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT versionid, versionid, versioncomment,
versionlabel,
> > creatorid, documentid, versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034277
> > NOTICE:  current transaction is aborted, queries ignored until end of
> > transaction block
> > DEBUG:  CommitTransactionCommand
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT versionid, versionid, versioncomment,
versionlabel,
> > creatorid, documentid, versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034277
> > NOTICE:  current transaction is aborted, queries ignored until end of
> > transaction block
> > DEBUG:  CommitTransactionCommand
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT versionid, versionid, versioncomment,
versionlabel,
> > creatorid, documentid, versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034277
> > NOTICE:  current transaction is aborted, queries ignored until end of
> > transaction block
> > DEBUG:  CommitTransactionCommand
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT versionid, versionid, versioncomment,
versionlabel,
> > creatorid, documentid, versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034277
> > NOTICE:  current transaction is aborted, queries ignored until end of
> > transaction block
> > DEBUG:  CommitTransactionCommand
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT versionid, versionid, versioncomment,
versionlabel,
> > creatorid, documentid, versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034277
> > NOTICE:  current transaction is aborted, queries ignored until end of
> > transaction block
> > DEBUG:  CommitTransactionCommand
> >
> > chris
> >
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of chris markiewicz
> > Sent: Tuesday, September 04, 2001 11:42 AM
> > To: Postgres Jdbc (E-mail)
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: [JDBC] error - NOTICE: current transaction is aborted, queries
> > ignored until end of transaction block
> >
> > Hello.
> >
> > I have been having problems with postgresql large objects...i have been
> > searching the archives of this group and i have posted a question or two
> but
> > i have so far be unable to resolve the issues.  right now, i have a
screen
> > that loads a few LOs...they occasionally don't make it to the screen.  i
> see
> > a couple of different errors in my log files (which i included below).
i
> > turned on debugging (d2) and i see the following on many of my queries
> (see
> > below).
> >
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT DISTINCT sbhd.timestamp AS "modifydate",
> > sbhd.versionid AS "versionid", sbhd.documentdescription AS
> > "sbhdocumentdescription", sbhd.maxversions AS "maxversions",
> sbhd.documentid
> > AS "sbhdocumentid", sbhd.hidden AS "hidden", sbhd.documentpath AS
> > "sbhdocumentpath", sbhd.parentid AS "parentid", sbhd.ownerid AS
"ownerid",
> > sbhd.documentname AS "sbhdocumentname", sbhd.createtimestamp AS
> "createdate"
> > FROM document sbhd  WHERE  sbhd.documentpath =
> > '/cmarkiew/porsche/porsche_speedster.jpg'
> > DEBUG:  ProcessQuery
> > DEBUG:  CommitTransactionCommand
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT versionid, versionid, versioncomment,
versionlabel,
> > creatorid, documentid, versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034272
> > NOTICE:  current transaction is aborted, queries ignored until end of
> > transaction block
> > DEBUG:  CommitTransactionCommand
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: SELECT versionid, versionid, versioncomment,
versionlabel,
> > creatorid, documentid, versionorder, datestamp, versioncontent FROM
> > document_version WHERE versionid = 1034272
> > NOTICE:  current transaction is aborted, queries ignored until end of
> > transaction block
> > DEBUG:  CommitTransactionCommand
> >
> > what causes the "current transaction is aborted, queries ignored until
end
> > of transaction block?"  the first query in the above debug block does
not
> > load an LO, but the second one does.  after the second query, seems like
> all
> > subsequent have that message...my log file starts getting a bunch of the
> > following:
> >
> > No results were returned by the query.
> >         at
org.postgresql.jdbc2.Statement.executeQuery(Statement.java:63)
> >         at
> > com.commnav.sbh.framework.persist.JDBCEngine.load(JDBCEngine.java:274)
> >         at
> >
>
com.commnav.sbh.framework.persist.PersistenceObject.load(PersistenceObject.j
> > ava:169)
> >         at
> >
>
com.commnav.sbh.framework.documentmanager.SBHDocument.loadComplex(SBHDocumen
> > t.java:402)
> >         at
> >
>
com.commnav.sbh.framework.documentmanager.SBHDocument.getSBHDocument(SBHDocu
> > ment.java:363)
> >         at
> >
com.commnav.sbh.servlets.DocumentManager.doPost(DocumentManager.java:201)
> >         at
> > com.commnav.sbh.servlets.CommnavServlet.doGet(CommnavServlet.java:170)
> >         at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> >         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> >         at
> > org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
> >         at org.apache.tomcat.core.Handler.service(Handler.java:286)
> >         at
> > org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
> >         at
> >
>
org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:79
> > 7)
> >         at
> > org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
> >         at
> >
>
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpC
> > onnectionHandler.java:210)
> >         at
> >
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
> >         at
> >
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
> >         at java.lang.Thread.run(Thread.java:484)
> > No results were returned by the query.
> >         at
org.postgresql.jdbc2.Statement.executeQuery(Statement.java:63)
> >         at
> > com.commnav.sbh.framework.persist.JDBCEngine.load(JDBCEngine.java:274)
> >         at
> >
>
com.commnav.sbh.framework.persist.PersistenceObject.load(PersistenceObject.j
> > ava:169)
> >         at
> >
>
com.commnav.sbh.framework.documentmanager.SBHDocument.loadComplex(SBHDocumen
> > t.java:402)
> >         at
> >
>
com.commnav.sbh.framework.documentmanager.SBHDocument.getSBHDocument(SBHDocu
> > ment.java:363)
> >         at
> >
com.commnav.sbh.servlets.DocumentManager.doPost(DocumentManager.java:201)
> >         at
> > com.commnav.sbh.servlets.CommnavServlet.doGet(CommnavServlet.java:170)
> >         at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> >         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> >         at
> > org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
> >         at org.apache.tomcat.core.Handler.service(Handler.java:286)
> >         at
> > org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
> >         at
> >
>
org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:79
> > 7)
> >         at
> > org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
> >         at
> >
>
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpC
> > onnectionHandler.java:210)
> >         at
> >
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
> >         at
> >
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
> >         at java.lang.Thread.run(Thread.java:484)
> >
> > what should i look for?  can i get other information from logs (i think
i
> > can raise the log level to 3 if it would help).  i can send pieces of
> code,
> > but our data layer is pretty abstracted, so i don't know how much that
> would
> > help.
> >
> > finally, an apology.  i started a thread about this issue a few weeks
ago
> > but got pulled onto some other stuff.  i am determined to follow this
> until
> > i fix it this time.
> >
> > i appreciate your help.
> > chris
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly