pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Koczan <pjkoczan@gmail.com>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: troubleshooting "idle in transaction"
Date: Wed, 06 Jun 2007 19:51:15 -0500
Message-ID: <46675683.6080601@gmail.com> (raw)
In-Reply-To: <46672A21.7070802@drivefaster.net>
References: <46672A21.7070802@drivefaster.net>

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
>




view thread (2+ messages)

Message-ID: <46675683.6080601@gmail.com>
Permalink:  ../46675683.6080601@gmail.com/
Also on:    postgresql.org/message-id/46675683.6080601@gmail.com

 · 

reply

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-admin@postgresql.org
  Cc: pjkoczan@gmail.com, fbsd@drivefaster.net
  Subject: Re: troubleshooting "idle in transaction"
  In-Reply-To: <46675683.6080601@gmail.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