...
So the objective is to get rid of these "idle in transaction" connections. There are 2 solutions, the fist first of which is just a workaround:
...
2) Find and fix the places in the code where transactions are not safely completed. This is surprisingly hard, because although the application framework initialises and closes transactions at start and finish, there are a variety of things that can happen causing the transaction to fail. Principally, exceptions that occur at the database level can have unfortunate consequences for the state of the transaction, so this need needs to be looked at. In addition, any situation which causes a Context object to go out of scope (which can happen if additional context objects are created in the course of the processing) can leave a hanging transaction.
...
Code Block |
---|
/usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | \ /usr/bin/wc \-l ` \-gt 20 && /usr/bin/pkill \-o \-f 'idle in transaction' |
In English: every minute, if there are more than 20 "idle in
transaction" Postgres processes, it kills the oldest one.
...
Code Block |
---|
/bin/test `/usr/local/bin/pgrep -f '127.0.0.1' | \ /usr/bin/wc \-l ` \-gt 20 && /usr/local/bin/pkill \-n \-f '127.0.0.1' |