Re: "Stuck" query

On Sep 10, 2008, at 5:57 AM, Tommy Gildseth wrote:

> Tom Lane wrote:
>> Tommy Gildseth <tommy.gildseth@usit.uio.no> writes:
>>> Richard Huxton wrote:
>>>> Looks like part of your query results being sent. Is it hung in
>>>> that one
>>>> system-call?
>>> Yes, I left it there for about ~1 hour, and that was all that ever
>>> came.
>> Seems like you have got a network issue.  What does netstat show
>> for the
>> status of that connection?
>> I don't think that a query cancel will blow PG off the send; you'd
>> probably have to resort to kill -9 on that process (with a consequent
>> restart of other sessions).  It's odd that the kernel hasn't given up
>> on the connection yet ...
>
>
> Netstat showed:
>
> netstat -a --tcp -p | grep 49004
> tcp  0  44660 dbserver:postgres clientserver:49004  ESTABLISHED
> 17504/postgres: nav
>
> I went back to the server the client was running on to double check,
> and it seems the client process hadn't been killed off when the
> application was restarted.
>
> We've got some scheduled downtime tomorrow, so I think I'll just
> leave it till then, since it's not causing any problems as far as I
> can tell.

For what it's worth, I've run into a situation similar to this with a
client a couple time in the last week or two (I can't say identical as
I don't know all of the details about the client end of your
connection).  Using the client port # you can use lsof in addition to
netstat (lsof -i tcp:49004) to track down the client process.  In our
case, the client process was a connection made via an ssh tunnel and
was sitting in FIN_WAIT2 status.  Killing the client process
individually made everything go away nicely without any kind of extra
downtime necessary.

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