What does it mean when a PostgreSQL process is "idle in transaction"?
PostgresqlPostgresql Problem Overview
What does it mean when a PostgreSQL process is "idle in transaction"?
On a server that I'm looking at, the output of "ps ax | grep postgres" I see 9 PostgreSQL processes that look like the following:
postgres: user db 127.0.0.1(55658) idle in transaction
Does this mean that some of the processes are hung, waiting for a transaction to be committed? Any pointers to relevant documentation are appreciated.
Postgresql Solutions
Solution 1 - Postgresql
The PostgreSQL manual indicates that this means the transaction is open (inside BEGIN) and idle. It's most likely a user connected using the monitor who is thinking or typing. I have plenty of those on my system, too.
If you're using Slony for replication, however, the Slony-I FAQ suggests idle in transaction may mean that the network connection was terminated abruptly. Check out the discussion in that FAQ for more details.
Solution 2 - Postgresql
As mentioned here: Re: BUG #4243: Idle in transaction it is probably best to check your pg_locks table to see what is being locked and that might give you a better clue where the problem lies.