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

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

On Nov 30, 2010, at 7:21 AM, Jonathan Vanasco 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

Are those transactions actually holding locks? (You can verify by checking pg_locks.) If so, that might give you a clue. If not, then they shouldn't be causing any 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

begin w/o commit or rollback?

and thanks. you've been very helpful!

On Nov 30, 2010, at 2:21 PM, Merlin Moncure wrote:

> Begin w/o commit is a grave application error and you should
> consider reworking your code base so that it doesn't happen (ever).

On Nov 30, 2010, at 7:21 AM, Jonathan Vanasco wrote:

> 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

With machine-readable logs, it shouldn't be *that* serious an issue.

1. Are you using connection pooling?
2. What's the application server environment?

--
-- Christophe Pettus
xof(at)thebuild(dot)com

On Fri, Dec 3, 2010 at 12:31 AM, Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:
> begin w/o commit or rollback?
>
> and thanks. you've been very helpful!
>
> On Nov 30, 2010, at 2:21 PM, Merlin Moncure wrote:
>
>> Begin w/o commit is a grave application error and you should
>> consider reworking your code base so that it doesn't happen (ever).

meaning, you opened a transaction (with 'begin') and didn't close it
with 'commit' (or 'start/end' etc). You opened a transaction but
didn't close it. Don't ever do this, and don't ever leave a
transaction open waiting on indeterminate events, like user input.

merlin