I'm cross posting to INTERFACES. Please follow up on INTERFACES and not on general. Cross posting is evil. --elein On Thu, Aug 18, 2005 at 05:02:18PM -0700, elein wrote: > 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@xxxxxxxxxxx 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
