Re: how can i bugfix "idle in transaction" lockups ?

On Tue, Nov 30, 2010 at 10:21 AM, Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:
> on a project, i find myself continually finding the database locked up with "idle in transaction" connections
>
> are there any commands that will allow me to check exactly what was going on in that transaction ?
>
> i couldn't find anything in the docs, and the project has decent traffic, so its going to be an issue to log all statements so I can sift through the data by PID

You can match the procpid on pg_stat_activity vs pid on pg_locks.
This will give you relation, which you can cross reference against
pg_database and pg_class system tables -- that should give a few
clues.

You can also break down various things interacting with the database
by role. For example, have the website auth into the database with a
'website' role, backend reporting scripts with 'reports', etc. That
way pg_stat_activity might tell you the specific trouble maker that is
doing this.

After that, it's about finding the bug -- are you using connection
pooling? Begin w/o commit is a grave application error and you should
consider reworking your code base so that it doesn't happen (ever).

merlin