On Tue, Nov 30, 2010 at 10:21 AM, Jonathan Vanasco <postg...@2xlp.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
