Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 01157632968
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Wed, 13 May 2009 05:24:10 -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 84453-07
	for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
	Wed, 13 May 2009 05:24:02 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.243])
	by mail.postgresql.org (Postfix) with ESMTP id 5B55463221F
	for <pgsql-hackers@postgresql.org>;
	Wed, 13 May 2009 05:24:06 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c2so85551anc.28
	for <pgsql-hackers@postgresql.org>;
	Wed, 13 May 2009 01:24:05 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=daMv1bfkJ+5uxF9r8xaBBC1W+TxzvcZwUAKz9kYqkhY=;
	b=IlKZRBSyUwmWJ22PBEQVB/aJ8ABNGV4BUxM6Qa9Gyf/MEYVSrOhwTOEPnu/bO1hwQj
	h623npvjDk5RKODXJLicjSWLjQy3I5Xd0uSpU5ZtmNl7uo4WEy+iLJ0UzAs2cfIFtoXi
	zbSthLFy6nqwmKtt4QP6LQINLAq+Cj48/LDM8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=mJCyGWkIInkc/I7u11y6z9jl9THCcl24CCPN4x4BfBayn0GbSPnzk3wbm54BrnFBgq
	2pTQ/6aVrpdY8PsvKs3jKHYsmdWZo/4pQAkUyH1DNjugZqCUkrAn0gjkKOm8mrNrHrFe
	Qszx3QATvRgejknqCyGPxlpvFjtI0SUarNrxg=
MIME-Version: 1.0
Received: by 10.231.34.134 with SMTP id l6mr431678ibd.22.1242190584971; Tue,
	12 May 2009 21:56:24 -0700 (PDT)
In-Reply-To: <A90F3088-EC12-4802-BFC0-BE7BFEBA08F9@decibel.org>
References: <49C9F49B.8020003@oss.ntt.co.jp>
	<1238091273.16568.530.camel@ebony.2ndQuadrant>
	<49CC75E3.7010101@oss.ntt.co.jp>
	<1238139418.16568.555.camel@ebony.2ndQuadrant>
	<A90F3088-EC12-4802-BFC0-BE7BFEBA08F9@decibel.org>
Date: Wed, 13 May 2009 07:56:24 +0300
Message-ID: <ecd779860905122156o48b3d07al75d2d21751c2a5bd@mail.gmail.com>
Subject: Re: display previous query string of idle-in-transaction
From: Asko Oja <ascoja@gmail.com>
To: decibel <decibel@decibel.org>
Cc: Simon Riggs <simon@2ndquadrant.com>,
	Tatsuhito Kasahara <kasahara.tatsuhito@oss.ntt.co.jp>,
	pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary=000325573af28f841f0469c40852
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.647 tagged_above=0 required=5 tests=AWL=0.252,
	DATE_IN_PAST_03_06=1.394, HTML_MESSAGE=0.001
X-Spam-Level: *
X-Archive-Number: 200905/560
X-Sequence-Number: 138102

--000325573af28f841f0469c40852
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

After taking look at our monitoring system i think some hint about previous
SQL might be useful.

dba    db70    db_name    WARNING    1    long transactions, duration >
2690min user=postgres pid=7887 waiting=False query=<IDLE> in transaction

Currently i have no idea what exactly did i kill without digging in logs
which might have rotated anyway by now.

regards,
Asko

On Tue, May 12, 2009 at 6:37 PM, decibel <decibel@decibel.org> wrote:

> On Mar 27, 2009, at 2:36 AM, Simon Riggs wrote:
>
>> 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.
>>
>
>
> I have to distinct problems with idle in transaction. One is reporting
> users / the tools they're using. I'll often find transactions that have been
> open for minutes or hours. But, that's not a big deal for me, because that's
> only impacting londiste slaves, and I have no problem just killing those
> backends.
>
> What does concern me is seeing idle in transaction from our web servers
> that lasts anything more than a few fractions of a second. Those cases worry
> me because I have to wonder if that's happening due to bad code. Right now I
> can't think of any way to figure out if that's the case other than a lot of
> complex logfile processing (assuming that would even work). But if I knew
> what the previous query was, I'd at least have half a chance to know what
> portion of the code was responsible, and could then look at the code to see
> if the idle state was expected or not.
> --
> Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--000325573af28f841f0469c40852
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

After taking look at our monitoring system i think some hint about previous=
 SQL might be useful.<br><br>dba=A0=A0=A0 db70=A0=A0=A0 db_name =A0=A0 WARN=
ING=A0=A0=A0 1=A0=A0=A0 long transactions, duration &gt; 2690min user=3Dpos=
tgres pid=3D7887 waiting=3DFalse query=3D&lt;IDLE&gt; in transaction<br>
<br>Currently i have no idea what exactly did i kill without digging in log=
s which might have rotated anyway by now.<br><br>regards,<br>Asko<br><br><d=
iv class=3D"gmail_quote">On Tue, May 12, 2009 at 6:37 PM, decibel <span dir=
=3D"ltr">&lt;<a href=3D"mailto:decibel@decibel.org">decibel@decibel.org</a>=
&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class=3D"im"=
>On Mar 27, 2009, at 2:36 AM, Simon Riggs wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, =
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Not really. I want to understand the actual problem with<br>
idle-in-transaction so we can consider all ways to solve it, rather than<br=
>
just focus on one method.<br>
</blockquote>
<br>
<br></div>
I have to distinct problems with idle in transaction. One is reporting user=
s / the tools they&#39;re using. I&#39;ll often find transactions that have=
 been open for minutes or hours. But, that&#39;s not a big deal for me, bec=
ause that&#39;s only impacting londiste slaves, and I have no problem just =
killing those backends.<br>

<br>
What does concern me is seeing idle in transaction from our web servers tha=
t lasts anything more than a few fractions of a second. Those cases worry m=
e because I have to wonder if that&#39;s happening due to bad code. Right n=
ow I can&#39;t think of any way to figure out if that&#39;s the case other =
than a lot of complex logfile processing (assuming that would even work). B=
ut if I knew what the previous query was, I&#39;d at least have half a chan=
ce to know what portion of the code was responsible, and could then look at=
 the code to see if the idle state was expected or not.<br>
<font color=3D"#888888">
-- <br>
Decibel!, aka Jim C. Nasby, Database Architect =A0<a href=3D"mailto:decibel=
@decibel.org" target=3D"_blank">decibel@decibel.org</a><br>
Give your computer some brain candy! <a href=3D"http://www.distributed.net"=
 target=3D"_blank">www.distributed.net</a> Team #1828</font><div><div></div=
><div class=3D"h5"><br>
<br>
<br>
<br>
-- <br>
Sent via pgsql-hackers mailing list (<a href=3D"mailto:pgsql-hackers@postgr=
esql.org" target=3D"_blank">pgsql-hackers@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-hackers" target=3D"_bla=
nk">http://www.postgresql.org/mailpref/pgsql-hackers</a><br>
</div></div></blockquote></div><br>

--000325573af28f841f0469c40852--