Re: Statement timeout not working on broken connections with active queries

Brian Wipf writes:

> On 12-Dec-06, at 4:30 PM, Tom Lane wrote:
>> "Brendan O'Shea" <brendanoshea(at)comcast(dot)net> writes:
>>> We have discovered a situation where the statement_timeout is not =
>>> honored for broken connections. If a connection is in the process of =
>>> returning results to the client and the connection is severed (for =
>>> example, network cable on client is unplugged) then the query continues
>>> =
>>> to run on the server even after the statement_timeout is exceeded.
>>
>> Well, the backend is blocked on a write() to the socket and cannot abort
>> that without bollixing the connection completely (i.e., loss of message
>> synchronization). So I think waiting until the TCP stack declares
>> failure is an appropriate response. If you want faster TCP failure,
>> see whether your TCP stack allows timeout adjustments.
>>
>> Note that the query is not "running" in the sense of consuming any
>> meaningful CPU or I/O resources in this state ...
> Of course, the query may have locks that block other meaningful
> operations. When a hung connection like this occurs on our server, I have
> resorted to using gdb to return from the write() method. Is this an
> acceptable way to kill the connection on the server side?
>

In our case locked resources is actually the problem that we are running
into. When the query is hung the vacuum daemon will not free up unused
space on the table that the query is accessing. The table happens to be
very heavy on update transactions, so it grows in size from only around 10
MB to sometimes over 100 MB. We were running into this problem due to the
fact that we have users running our client application from remote offices
that have poor network connectivity.

Is there no way to specify a timeout for the write() to the socket or some
other way to abort? Do we still need to be concerned with message
synchronization if the connection is essentially a lost cause anyhow since
it was severed? It would be great if this could be made into a configurable
parameter in the postgresql.conf file.

Thanks for the suggestion about the TCP stack, I'll take a look at what can
be done there.

Brendan