On Jan 2, 2014, at 10:40 AM, John Kida <[email protected]> wrote: > Thanks for replying so fast. > > I understand using the event system to track the stack trace, however how > would you recommend implementing a timeout for "checked out" connections, to > find connections stuck in a checked out state? I dont think I would actually > use this in production, but on a beta/lab version of the app to find problems,
well it depends on how you want to locate these connections. if you have some system that is either a background thread, or some kind of “debug console” that is showing you info, then your event system would essentially keep track of these connections in some data structure, including the time it was checked out, and this structure would be queried by this system. if OTOH you want to have it raise an exception when a very stale connection is actually used, you could also implement the “connection execute” event and check the time there as well. if it’s a web app, I’d probably have some kind of web page that just shows me the current list of checked out connections, the stack trace where they were checked out, and the time they were checked out. this view can also filter by connections that are older than 5 minutes or something like that too. > Is it easier or even possible to discover these frozen checked out > connections using simply postgres queries, like pg_stat_activity? absolutely, usually I just do a “ps” listing which on Postgresql will show you a process per connection and information like “idle in transaction”. pg_stat_activity is a more comprehensive way to find these, and on a production system, the first thing I usually will do is one of these, just to see that connections aren’t staying idle. It will also show transactions that are hung on a lock and in that case you can often figure out what is exactly hung by looking at the SQL statement. However, this information won’t always give you a clue as to where in the application this connection was originated, which is the advantage you get from application-side tracking. So it depends on what you’re looking for. > If a query completes but is never returned back to the pool, would > pg_stat_activity see that differently then a connection that is checked in, > could this be used to find what query(s) ran on this connection and track it > back to a method that never closed its session? yes because the psycopg2 connection is normally in a transaction after statements have been invoked upon it, you’ll see “idle in transaction” for that connection as opposed to “idle”. this assumes you’re not using psycopg2 in “autocommit” mode which you probably are not. > > I just need a reliable way to discover connections that are never closed and > stuck in a checked out state. “ps” listing or pg_stat_activity is the quickest way to see that.
signature.asc
Description: Message signed with OpenPGP using GPGMail
