Date: Fri, 19 Aug 2005 11:55:19 -0700
To: pgsql-interfaces@postgresql.org
Cc: Junaili Lie <junaili@gmail.com>, pgsql-general@postgresql.org,
	elein <elein@varlena.com>
Subject: Re: <IDLE> in transaction
Message-ID: <20050819185519.GY5365@varlena.com>
Mail-Followup-To: pgsql-interfaces@postgresql.org,
	Junaili Lie <junaili@gmail.com>, pgsql-general@postgresql.org
References: <8d04ce990508181616c93fbbc@mail.gmail.com>
	<20050819000218.GS5365@varlena.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050819000218.GS5365@varlena.com>
User-Agent: Mutt/1.5.6+20040907i
From: elein@varlena.com (elein)

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@varlena.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
> > 
> 
> ---------------------------(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
>