Re: Backend timeout

On Sep 16, 2008, at 6:21 AM, Dragan Ciric wrote:

> Hi!
>
> We need some help.
> Sometimes we have broken connections with backend ( postgresql
> server ).
> When this occurs, we have "idle in transaction" indication on server
> side. Can you
> answer us, how long will server stay in this state and what happens
> with this
> broken connection ( client started transaction, but can't send
> commit or rollback )?

Are you absolutely positive that the client is gone? Postgres should
detect when client connections disappear and roll back any open
transactions. If you're *sure* the client connection is gone and the
transactions are stuck in idle in transaction then they may just sit
there like that until you kill them off manually. I'd make sure that
there really isn't a client process holding on to that connection by:

1. Get client_addr and client_port from pg_stat_activity
2. Go to client_addr and run (without the brackets): lsof -i
tcp:<client_port>

If there's still a client for that connection you should turn up a
process there. If that's the case then you should be tracking down
why your client connection are holding on to open transactions.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
(415) 963-4410 x 260
Location: US/Pacific
IRC: mage2k