Thread: psql hanging

psql hanging

From
Steve Crawford
Date:
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


Re: psql hanging

From
Richard Huxton
Date:
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

Re: psql hanging

From
Steve Crawford
Date:
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

Re: psql hanging

From
"Trevor Talbot"
Date:
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.

Re: psql hanging

From
Martijn van Oosterhout
Date:
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.

Attachment

Re: psql hanging

From
"Scott Marlowe"
Date:
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.

Re: psql hanging

From
"Trevor Talbot"
Date:
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.

Re: psql hanging

From
Steve Crawford
Date:
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