Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id DABE49F9D3F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 22 Aug 2007 18:41:32 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 51407-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 22 Aug 2007 18:41:25 -0300 (ADT)
X-Greylist: delayed 00:32:27.287224 by SQLgrey-1.7.5
Received: from lynet.oppetid.no (oppetid.no [80.65.51.2])
	by postgresql.org (Postfix) with ESMTP id 41FDB9F9764
	for <pgsql-performance@postgresql.org>;
	Wed, 22 Aug 2007 18:41:29 -0300 (ADT)
Received: by lynet.oppetid.no (Postfix, from userid 1019)
	id 894AD119EA8; Wed, 22 Aug 2007 23:08:58 +0200 (CEST)
Date: Wed, 22 Aug 2007 23:08:58 +0200
From: Tobias Brox <tobias@nordicbet.com>
To: Sachchida Ojha <sojha@secure-elements.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Long running transaction in pg_activity_log
Message-ID: <20070822210858.GH21893@oppetid.no>
References: <E099DB9EF394534CBA0F62ED03CF3BBF0607772D@EXVS01.IceMAIL.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <E099DB9EF394534CBA0F62ED03CF3BBF0607772D@EXVS01.IceMAIL.com>
Organization: Group Nordicbet
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/253
X-Sequence-Number: 26383

[Sachchida Ojha - Wed at 04:40:09PM -0400]
> I see some long running transaction in my pg_activity_log table. My app
> becomes almost unusable. My question is 
> How can I query the database to see what sql these transactions are
> running. 

"<IDLE> in transaction" means that no sql query is running at the
moment.

Most probably it's a problem with the application, it starts a
transaction, but does not close it (through a commit or rollback).  This
is very harmful for the performance, as the hours, days and weeks pass
by any database supporting transactions will get problems.

Restarting the application and vacuuming is a one-time-shot which should
solve the problem for a short while.  For a permanent fix, the
application needs to be fixed, or you'll have to ensure that the
autocommit feature is used.