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

we use a hashtable with connections as keys and timestamps as values...we do
that so that we can clean up connections that haven't been used in a
while...i know that some DBs invalidate their connections after a certain
amount of time, so we have a process that periodically walks through the
timestamps and cleans as necessary...

anyway, this could be handled just as easily with a linked list of wrapped
connections.  can either of you suggest why the hashtable approach might be
causing a problem?  or is it probably elsewhere in my code?

dave, if you have no trouble with donating your code, i would be more than
happy to take a look at it...i'd really appreciate that.

thanks for your help.
chris

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
Sent: Thursday, September 06, 2001 9:10 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


AFAIR,

I simply wrap the sql connection for ease of handling, and then put
available connections in the linked list. When I want one I remove it
from the linked list, and put it into a hashtable, keyed by the toString
method on the connection.

When it is returned, I remove it from the hash table and put it back
into the linked list
Since it's a linked list, it can grow and shrink at will.


Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of
T.R.Missner@level3.com
Sent: September 6, 2001 8:14 PM
To: Dave@micro-automation.net; cmarkiew@commnav.com;
pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


I also built a linked list of available connections today
and my problem has completly gone away.  I have an intensly threaded app
and it has been running now for several hours
doing 1000's of inserts without the problem I was having before.  I
don't know how dave implemented his list but I have an inner class with
a connection and a boolean marking whether it is in use.  each insert
gets a connection that is not in use from the linked list and marks it
in use until it is finished.  I also built in the ability for the list
to grow and shrink dynamically based on need.  So if all connections are
in use I will create more and after a specified period of time I delete
the ones that haven't been used.

This seems to be working quite nicely now.

t.r.

-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Thursday, September 06, 2001 6:05 PM
To: cmarkiew@commnav.com; Missner, T. R.; pgsql-jdbc@postgresql.org
Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


Chris,

I'm curious; why are you using a hashtable for available connections,
and how do you use it?

I have been using a connection pool that uses a linked list, in LIFO
mode to store available connections.

I can donate the pool code if need be, it has been running for over a
year with no problems.

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of chris markiewicz
Sent: September 6, 2001 2:58 PM
To: T.R.Missner@Level3.com; pgsql-jdbc@postgresql.org;
pgsql-jdbc-owner@postgresql.org
Cc: chris.markiewicz@commnav.com
Subject: Re: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


spoke too soon.  i see the error again (with 7.1) ...i no longer get the
transaction isolation level issue, but i still get the "NOTICE: current
trans..." error.  some improvement, i guess.

whether it's active?  that isn't ever really an issue with our approach,
unless i'm misunderstanding your question...this is an
oversimplification,
but: we have a connection pool that has a hashtable of "available"
connections...there is also a user-connection hashtable with users as
keys and connections as values.  if a user wants to hit the db, the
system first checks the user-connection hashtable.  if no conn is
associated with the user, it grabs a connection from "available"
connection hashtable and puts it in the user-connection hashtable.  the
user then uses that connection etc etc...when the transaction is closed
(by our system code) the connection is "committed," removed from the the
user-connection hashtable, and returned to the "available" hashtable. we
don't use any threading, so there would never be two threads trying to
use the same connection.

should i be checking for anything else?

thanks
chris

-----Original Message-----
From: T.R.Missner@Level3.com [mailto:T.R.Missner@Level3.com]
Sent: Thursday, September 06, 2001 2:29 PM
To: cmarkiew@commnav.com; pgsql-jdbc@postgresql.org;
pgsql-jdbc-owner@postgresql.org
Cc: chris.markiewicz@commnav.com
Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


I am in the process of rewriting my connection pooling object. I'll
share the results when i am finished later today I suspect. How are you
determining whether a transaction is active? getAutoCommit?

-----Original Message-----
From: chris markiewicz [mailto:cmarkiew@commnav.com]
Sent: Thursday, September 06, 2001 11:40 AM
To: Missner, T. R.; pgsql-jdbc@postgresql.org;
pgsql-jdbc-owner@postgresql.org
Cc: chris.markiewicz@commnav.com
Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


trm

i have been able to successfully avoid the problem for the last 20
minutes or so...my connection pooling system calls commit() on
connections before returning them to the "available" pool (even if they
were already 'committed')...i added a conditional to check whether or
not it was already committed - if it was, i don't run the commit.  this
seems to prevent the "transaction isolation level" exception that always
preceded my other exceptions.  might this apply to your pooling system
as well?

thanks
chris

-----Original Message-----
From: T.R.Missner@Level3.com [mailto:T.R.Missner@Level3.com]
Sent: Thursday, September 06, 2001 10:33 AM
To: cmarkiew@commnav.com; pgsql-jdbc@postgresql.org;
pgsql-jdbc-owner@postgresql.org
Subject: RE: [JDBC] error - NOTICE: current transaction...MORE DETAIL...


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(PersistenceObje
ct.j
ava:169)
    at
com.commnav.sbh.framework.documentmanager.SBHDocument.loadComplex(SBHDoc
umen
t.java:402)
    at
com.commnav.sbh.framework.documentmanager.SBHDocument.getSBHDocument(SBH
Docu
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.jav
a:79
7)
    at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
    at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(H
ttpC
onnectionHandler.java:210)
    at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416
)
    at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:49
8)
    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(PersistenceObje
ct.j
ava:169)
    at
com.commnav.sbh.framework.documentmanager.SBHDocument.loadComplex(SBHDoc
umen
t.java:402)
    at
com.commnav.sbh.framework.documentmanager.SBHDocument.getSBHDocument(SBH
Docu
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.jav
a:79
7)
    at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
    at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(H
ttpC
onnectionHandler.java:210)
    at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416
)
    at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:49
8)
    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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html