Обсуждение: psql hanging
What would cause psql to hang indefinitely when the backend disappears? We have a script that uses psql to insert a record (TCP connection to DB on different machine). The command is basically psql <connection_stuff> -c "insert into..." A while back I had to restart the server and today discovered that some of the client machines have psql processes dating back several months. Obviously no TCP connection on the server end but client-side shows the connection as "ESTABLISHED". -Steve
Steve Crawford wrote: > What would cause psql to hang indefinitely when the backend disappears? > > We have a script that uses psql to insert a record (TCP connection to DB > on different machine). The command is basically > psql <connection_stuff> -c "insert into..." > > A while back I had to restart the server and today discovered that some > of the client machines have psql processes dating back several months. > > Obviously no TCP connection on the server end but client-side shows the > connection as "ESTABLISHED". The OS should clear that connection down after a timeout - hours perhaps, but months doesn't sound right. When the connection gets closed down psql should exit. What OS is this? Someone here should be able to figure out what's going on. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Steve Crawford wrote: >> What would cause psql to hang indefinitely when the backend disappears? >> >> We have a script that uses psql to insert a record (TCP connection to DB >> on different machine). The command is basically >> psql <connection_stuff> -c "insert into..." >> >> A while back I had to restart the server and today discovered that some >> of the client machines have psql processes dating back several months. >> >> Obviously no TCP connection on the server end but client-side shows the >> connection as "ESTABLISHED". > > The OS should clear that connection down after a timeout - hours > perhaps, but months doesn't sound right. When the connection gets closed > down psql should exit. > > What OS is this? Someone here should be able to figure out what's going on. > RedHat 7.3 w/kernel update to 2.4.18-5 (grrrr - specific requirement to support hardware/drivers we have). Cheers, Steve
On 9/5/07, Richard Huxton <dev@archonet.com> wrote: > Steve Crawford wrote: > > A while back I had to restart the server and today discovered that some > > of the client machines have psql processes dating back several months. > > > > Obviously no TCP connection on the server end but client-side shows the > > connection as "ESTABLISHED". > > The OS should clear that connection down after a timeout - hours > perhaps, but months doesn't sound right. When the connection gets closed > down psql should exit. Unless psql is turning on keepalive or similar, or the OS is forcing it on by default, there are no timeouts for idle TCP connections. If the command was transported to the server successfully and psql was just waiting for a result, the connection is idle and nothing will happen if the server end suddenly goes away.
On Wed, Sep 05, 2007 at 10:44:20AM -0700, Trevor Talbot wrote: > Unless psql is turning on keepalive or similar, or the OS is forcing > it on by default, there are no timeouts for idle TCP connections. If > the command was transported to the server successfully and psql was > just waiting for a result, the connection is idle and nothing will > happen if the server end suddenly goes away. Well, any TCP implementation has to support keepalives and generally they are turned on by default, but to something like 2 days. Anything months old must be caused by something else (overzealous firewall dropping the RST packets in response to the keepalives?). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
On 9/5/07, Martijn van Oosterhout <kleptog@svana.org> wrote: > On Wed, Sep 05, 2007 at 10:44:20AM -0700, Trevor Talbot wrote: > > Unless psql is turning on keepalive or similar, or the OS is forcing > > it on by default, there are no timeouts for idle TCP connections. If > > the command was transported to the server successfully and psql was > > just waiting for a result, the connection is idle and nothing will > > happen if the server end suddenly goes away. > > Well, any TCP implementation has to support keepalives and generally > they are turned on by default, but to something like 2 days. Anything > months old must be caused by something else (overzealous firewall > dropping the RST packets in response to the keepalives?). The default for RHEL3 and 4 is 2 hours.
On 9/5/07, Martijn van Oosterhout <kleptog@svana.org> wrote: > On Wed, Sep 05, 2007 at 10:44:20AM -0700, Trevor Talbot wrote: > > Unless psql is turning on keepalive or similar, or the OS is forcing > > it on by default, there are no timeouts for idle TCP connections. If > > the command was transported to the server successfully and psql was > > just waiting for a result, the connection is idle and nothing will > > happen if the server end suddenly goes away. > > Well, any TCP implementation has to support keepalives and generally Er, has to? Unless there's a new RFC I haven't seen lately, keepalive is purely a vendor behavior extension. It's also known that some older stacks interpreted the TCP spec slightly differently and don't correctly respond to keepalive packets. Keepalive is definitely useful, but you can't assume anything about it being present, enabled, or working by default. It's only seen mainstream use in recent years.
Trevor Talbot wrote: > Unless psql is turning on keepalive or similar, or the OS is forcing > it on by default, there are no timeouts for idle TCP connections. If > the command was transported to the server successfully and psql was > just waiting for a result, the connection is idle and nothing will > happen if the server end suddenly goes away. This is the most likely explanation - fits the symptoms. Cheers, Steve