Re: <IDLE> in transaction

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(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
> >
>
> ---------------------------(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
>