From: elein@varlena.com
To: Junaili Lie <junaili@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: <IDLE> in transaction
Date: Thu, 18 Aug 2005 17:02:18 -0700
Message-ID: <20050819000218.GS5365@varlena.com> (raw)
In-Reply-To: <8d04ce990508181616c93fbbc@mail.gmail.com>
References: <8d04ce990508181616c93fbbc@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: elein@varlena.com, junaili@gmail.com, pgsql-general@postgresql.org
Subject: Re: <IDLE> in transaction
In-Reply-To: <20050819000218.GS5365@varlena.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox