Re: troubleshooting "idle in transaction"

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
>