Re: 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