Re: Long running query - connection and keepalives enabled but querynot canceled

On 6/4/19 6:24 PM, Laurenz Albe wrote:

> Tomasz Ostrowski wrote:
> 
>> The TCP keepalives functionality on the database server's operating
>> system would figure out that the connection is not working anymore and
>> close it.
>>
>> You'd assume that the database would get the info and cancel the query.
>> Except - it does not.
>>
>> The database would get an error on the connection socket only after it
>> tries to read or write to it next time. But it does not try to do this -
>> it's busy counting those quarks until statement_timeout is reached.
> 
> By default "tcp_keepalives_idle" is quite large: 2 hours.
> 
> Are you sure that the queries keep running for longer than that?
> 
> Try to experiment with lower settings.  It will cause marginally more
> network traffic, but dead connections will be detected more quickly.

The keepalive settings we use is idle/interval/count=60/10/60. It means 
that the the dead connections should be cleared after 60s+10*60s=11m. I 
started to investigate when I found a query running for over 11 days 
(there was no query_timeout set on this server).

But the problem is not that the dead connections aren't detected - they 
are, and the operating system clears them. They disappear from "netstat 
--tcp" output after expected time.

The problem is that the database does not get the info and does not kill 
the query.

I've reproduced this with running the SQL below on a local Postgresql 11 
connected with TCP, configured with idle/interval/count=15/15/15:

   create or replace function pg_temp.fib(n int) returns int language 
plpgsql as $$
     begin if n<=1 then return n; end if; return 
pg_temp.fib(n-1)+pg_temp.fib(n-2); end;
   $$;
   select pg_temp.fib(50);

And then filtering out the connection with iptables.

-- 
Tomasz "Tometzky" Ostrowski