Anthony Presley <anthony@resolution.com> 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