Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 1D4AC638352
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 27 Mar 2009 04:36:56 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 80664-05
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Fri, 27 Mar 2009 04:36:51 -0300 (ADT)
Received: from mx3.hub.org (mx3.hub.org [206.223.169.73])
	by mail.postgresql.org (Postfix) with ESMTP id 58E74635E0E
	for <pgsql-hackers@postgresql.org>;
	Fri, 27 Mar 2009 04:36:47 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outmail136058.authsmtp.co.uk (outmail136058.authsmtp.co.uk
	[62.13.136.58]) by mx3.hub.org (Postfix) with ESMTP id 6C9B737BD6B
	for <pgsql-hackers@postgresql.org>;
	Fri, 27 Mar 2009 04:36:46 -0300 (ADT)
Received: from mail-c189.authsmtp.com (mail-c189.authsmtp.com [62.13.128.71])
	by punt7.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id
	n2R7aKGZ002804; Fri, 27 Mar 2009 07:36:20 GMT
Received: from [192.168.0.4] (85-211-223-92.dyn.gotadsl.co.uk [85.211.223.92])
	(authenticated bits=0)
	by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id n2R7aJro063441;
	Fri, 27 Mar 2009 07:36:20 GMT
Subject: Re: display previous query string of idle-in-transaction
From: Simon Riggs <simon@2ndQuadrant.com>
To: Tatsuhito Kasahara <kasahara.tatsuhito@oss.ntt.co.jp>
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <49CC75E3.7010101@oss.ntt.co.jp>
References: <49C9F49B.8020003@oss.ntt.co.jp>
	<1238091273.16568.530.camel@ebony.2ndQuadrant>
	<49CC75E3.7010101@oss.ntt.co.jp>
Content-Type: text/plain
Date: Fri, 27 Mar 2009 07:36:58 +0000
Message-Id: <1238139418.16568.555.camel@ebony.2ndQuadrant>
Mime-Version: 1.0
X-Mailer: Evolution 2.12.0 
Content-Transfer-Encoding: 7bit
X-Server-Quench: f6fb4dbc-1aa1-11de-bc7b-001f29070be2
X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ
	NksadBtFagJbYFhD HGQLW11EUFp7WmV/ aQofZQBDYEtPQQdo
	U1ZAQE1QEQdtHhxP WBx8VUgINVpGfn10 YUcsX3dYWgoscxAv
	RhoGRnAHNDZhdWEe BBVFJVBTIh5Kfh4T aFl/U3NfNGdJBC9q
	VzwTFhsSEA9kHWxZ QxoVKVsDCV4REyA9 QA0ZVSwyEFcUDz4v
	NR0gJRYbEg4QPkE/ dFonHE4COhkJAwZC HkZLaF9z
X-Authentic-SMTP: 61633235383639.kestrel.dmpriest.net.uk:1378/Kp
X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse
X-Virus-Status: No virus detected - but ensure you scan with your own
	anti-virus system.
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200903/1112
X-Sequence-Number: 135790


On Fri, 2009-03-27 at 15:44 +0900, Tatsuhito Kasahara wrote:
> Simon Riggs wrote:
> >> If we can also check previous query_string of idle-in-transaction,
> >> it is useful for analysis of long transaction problem.
> > 
> > I'm more interested in the problem itself. Why do you think there is a
> > problem and why does knowing this help you? I had similar problems
> > recently, so I'm interested in thoughts around this.
> In many case, applications don't know what queries they are doing.
> So, if a long transaction problem (unworking defragmnetation by HOT and
> VACUUM) occured, I don't understand which application is the cause with
> only "<IDLE> in transaction" message.
> 
> But if I can also check last query string, I guess which apllication
> do that and point out the problem point.
> # As I said, I think a long transaction is not a problem itself.
> 
> Does that answer your question ?

Not really. I want to understand the actual problem with
idle-in-transaction so we can consider all ways to solve it, rather than
just focus on one method.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support