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

On Tue, Sep 16, 2008 at 7:21 AM, Dragan Ciric <dragan.ciric@a-asoft.com> 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 )?

If the client socket on the other end has simply disappeared, then the
connection will be harvested approximately net.ipv4.tcp_keepalive_time
+ net.ipv4.tcp_keepalive_probes * net.ipv4.tcp_keepalive_intvl seconds
later. On default setups, this is something like 7200 + 90 * 9 for a
total of 8010 seconds. i.e. just over an hour.

On later model postgresql's you can change these settings for just the
pgsql server to something more sane, like

net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 30

which get it down to 6.5 minutes or so before stale connections are harvested.

The advantage to using tcp_keepalive is it won't kill living but idle
connections.

Scott Marlowe wrote:

On Tue, Sep 16, 2008 at 7:21 AM, Dragan Ciric <dragan.ciric@a-asoft.com> 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 )?

If the client socket on the other end has simply disappeared, then the
connection will be harvested approximately net.ipv4.tcp_keepalive_time
+ net.ipv4.tcp_keepalive_probes * net.ipv4.tcp_keepalive_intvl seconds
later. On default setups, this is something like 7200 + 90 * 9 for a
total of 8010 seconds. i.e. just over an hour.

Not to be picky but 60 sec * 60 min = 3600 sec = 1 hour so
the above timeout would be just over 2 hours.