Re: <IDLE> in transaction

I am also seeing this situation using hibernate.

Some of the IDLE-in-transaction connections are sitting
there holding locks which is a BIG problem.

The query I use to see the processes and locks is this:

select procpid, usename , (now() - query_start) as age,
c.relname , l.mode, l.granted
from pg_stat_activity a LEFT OUTER JOIN pg_locks l ON (a.procpid = l.pid)
LEFT OUTER JOIN pg_class c ON (l.relation = c.oid)
where (current_query = '<IDLE> in transaction'
or current_query like '%vacuum%')
-- and query_start < now() - '1 hours'::interval
order by pid;

Note the commented out part. Change the interval to what you like.
You cannot see a query because there is none.

Some of these I-i-t connections come and go after a while.
Some stick around for DAYS.

If ANYONE has any brilliant ideas as to the source and
dare I say correction to this problem, many people, especially
myself would be very very happy.

--elein
--------------------------------------------------------------
elein(at)varlena(dot)com Varlena, LLC www.varlena.com
(510)655-2584(o) (510)543-6079(c)

PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
--------------------------------------------------------------
AIM: varlenallc Yahoo: AElein Skype: varlenallc
--------------------------------------------------------------
I have always depended on the [QA] of strangers.

On Thu, Aug 18, 2005 at 04:16:27PM -0700, Junaili Lie wrote:
> Hi,
> We have applications that sits on top of Java application server. Our
> code is written in Java, sitting on top of Jboss with Hibernate, and
> we use JDBC driver pg74.215.jdbc2ee.jar. We have observed a number of
> <IDLE> in transaction on pg_stat_activity.
> I am wondering if there's command/view/system tables/ tools out there
> that will allow us to look at what's the in transactions that are
> waiting to be committed.
> We have some "<IDLE> in transaction" and would like to see which part
> of the code that causes it.
>
>
> Thanks in advance.
>
> J
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>