pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
troubleshooting "idle in transaction"
2+ messages / 2 participants
[nested] [flat]

* troubleshooting "idle in transaction"
@ 2007-06-06 21:41 Dan Harris <fbsd@drivefaster.net>
  2007-06-07 00:51 ` Re: troubleshooting "idle in transaction" Peter Koczan <pjkoczan@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Dan Harris @ 2007-06-06 21:41 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: troubleshooting "idle in transaction"
  2007-06-06 21:41 troubleshooting "idle in transaction" Dan Harris <fbsd@drivefaster.net>
@ 2007-06-07 00:51 ` Peter Koczan <pjkoczan@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Peter Koczan @ 2007-06-07 00:51 UTC (permalink / raw)
  To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-admin

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
>




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-06-07 00:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-06 21:41 troubleshooting "idle in transaction" Dan Harris <fbsd@drivefaster.net>
2007-06-07 00:51 ` Peter Koczan <pjkoczan@gmail.com>

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