Re: in transaction - Mailing list pgsql-general

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
>