Anthony Presley <anthony@xxxxxxxxxxxxxx> writes: > Every so often (usually in the early morning), we are seeing an "<IDLE> > in transaction" show up. This appears to lock / block other statements > from going through, though I'm not sure why. If left unchecked, we end > up with all of our connections being overrun. Well, the idle transaction is evidently sitting on some lock that the UPDATE needs. You didn't show the pg_locks columns that would tell exactly what lock it is though ... > Would a connection pooler cause (or amplify) any issues relating to > this? It shouldn't. Any decent pooler will ensure that no transaction remains open when it transfers the connection to another client. > How can I track down the issue here? I've been looking through > web app and database logs without much luck. Can you track the session connection (the port number) back to a client process? If there's a pooler in the way you'll probably need to crank up its logging level to be able to make that association. Once you've got that, you could attach to the client with a debugger and see what it thinks it's doing. The other line of attack I can think of is to turn on log_connections and log_statements and make sure log_line_prefix includes the PID. Then you can find the series of statements that were issued before the idle transaction went to sleep, and that hopefully is enough information to track down the client code. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
