Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A0CA39FB408 for ; Wed, 6 Jun 2007 21:51:23 -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 63794-01 for ; Wed, 6 Jun 2007 21:51:14 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id 374799FB3E8 for ; Wed, 6 Jun 2007 21:51:19 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id p76so711753pyb for ; Wed, 06 Jun 2007 17:51:17 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=BB0jCVDOwhXct3Y7xlBUM7213RA7pXlLifUubppIsshGN9FhG0+hpAYRQMOzVtPsK8g0bWp3lfrl55GvBdCPPAK08VXv6KBt/+UhZkwudRQPSOgv1LkrirE+wtA4kxNXJpufSkjUvpZiTFkdBVGDgvgVTDpnZyLPPRlfQC6cEmU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=VpWUgta31r1eu+u1NFtFoWpw0Jc/sHJodB6g9dNCghXm7ouTHPjWHFu/+R3z0eU740v3Ekr6V6f8zySXF37rpz7i/+b3+AUV0gdQvv8u9DXKmhnQ2YwRX42zxzv4oK1JNy3ce5Xf/F9O7St+5ox43vKrHx+MWQtdeMdOfEHKdgY= Received: by 10.35.105.18 with SMTP id h18mr1951960pym.1181177477700; Wed, 06 Jun 2007 17:51:17 -0700 (PDT) Received: from ?192.168.0.102? ( [66.168.24.69]) by mx.google.com with ESMTP id x72sm147668pyg.2007.06.06.17.51.16 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 06 Jun 2007 17:51:17 -0700 (PDT) Message-ID: <46675683.6080601@gmail.com> Date: Wed, 06 Jun 2007 19:51:15 -0500 From: Peter Koczan User-Agent: Thunderbird 1.5.0.12 (X11/20070604) MIME-Version: 1.0 To: Dan Harris CC: pgsql-admin@postgresql.org Subject: Re: troubleshooting "idle in transaction" References: <46672A21.7070802@drivefaster.net> In-Reply-To: <46672A21.7070802@drivefaster.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/53 X-Sequence-Number: 25721 Check the pg_locks system view in the pg_catalog schema. It will tell you a wealth of information. Peter Dan Harris wrote: > Greetings.. > > I'm running 8.0.12 and the system has been very stable for years now > with no significant application changes. I am using > Apache::Session::Postgres in a web application to store session > state. This has really been flawless for us so far, but lately I've > caught a few occurrences where I will see in GNU top, the following: > > 9136 postgres 16 0 546m 9.8m 8080 S 0 0.0 0:00.00 1 > postgres: postgres sessions harvard(49197) idle in transaction > 10892 postgres 16 0 546m 9180 7356 S 0 0.0 0:00.01 3 > postgres: postgres sessions harvard(49649) SELECT waiting > 12174 postgres 16 0 546m 9172 7348 S 0 0.0 0:00.00 3 > postgres: postgres sessions harvard(51158) SELECT waiting > 12175 postgres 16 0 546m 9152 7328 S 0 0.0 0:00.01 1 > postgres: postgres sessions harvard(51159) SELECT waiting > 12176 postgres 16 0 546m 9112 7288 S 0 0.0 0:00.01 1 > postgres: postgres sessions harvard(51160) SELECT waiting > > I can connect to the database fine and select from it when this > occurs, but I'm guessing that the owner of that particular session row > is refreshing their browser and seeing it 'hang', causing the lock > jam. I know this could potentially be a problem with Apache::Session > logic, but that module has not been updated for as long as I can > remember, so I'm wondering if this could be a database issue somehow? > > Previously, I have just killed the process that's idle in transaction, > then things clean up.. However, this doesn't feel very clean. > > Can anyone recommend a good process for learning why exactly that > transaction is not completing? Or, is there a postgresql.conf setting > that can automatically kill these errant locks? > > -Dan > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >