Re: Idle in transaction help

We have a java web page that will give us the stack trace of all the
running JDBC connections inside our system. The problem is that we
currently have no way of relating those stack traces back to a PID so
the programmers can get the stack trace of the hung database connection.
We use the JDBC connection pooling so there's no way to be sure what
stack trace goes to what PID. I gave the developers the postgres call
to get that backend PID through the JDBC connection a few days ago, but
they don't have the resources to get the additional call built into
their programs for up to 1-2 months.

I'm working on the business side to get priorities changed, but it
hasn't happened yet. Mostly because I've got Xymon watching for those
conditions so I can correct them before we get calls into the helpdesk.
Sorry, I'm rambling.

Anyway, I'm trying to attack it from the database side out since I am
not a programmer and can't help with that part. I can do simple CGIs
with bash, but I don't know Java or C or even Perl yet for that matter.
Since you guys are the experts, I'm looking for any way to attack this
problem from the database side. The tips I've gotten about the JDBC
driver and commits are helpful in that it gives our programmers things
to watch out for that we didn't realize, and I'm open to any suggestions
from the list about how I can help attack this.

Since I'm ultimately responsible for database performance and I don't
like being reduced to sitting on the sidelines I'm trying to see what if
anything else my skills can contribute.

As for patting you on the head, I was being sincere. And trying not to
come off sounding like a cocky SOB. :-)

Thanks,

Scot Kreienkamp

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Friday, July 10, 2009 7:02 PM
To: Scot Kreienkamp
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Idle in transaction help

On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp<SKreien(at)la-z-boy(dot)com>
wrote:
> Thanks scott, but I wrote a cgi to combine all of the process info and
allow
> me to kill errant queries. So I know how to track down the pid. Thanks
for
> trying to help though. :-)

So, what are you looking for, a stack trace dump from java to look
through maybe? (the one that kill -1 or whatever generates? It's been
a few years.) That'll usually give you the context to find out which
thread is where. P.s. no need to pat me on the head like the doggie.
:)