Re: keep alive and running query

desmodemone wrote:
> 2013/12/16 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
>> desmodemone wrote:
>> As I tested and saw until now, the keep alive functions as follow [if I understand correctly and it's
>> not a bug] :
>>
>> When a connection it's in idle state or in idle in transaction, if the connection with client it's
>> broken for a number of keep alive, the backend will be terminated.
>>
>>
>> By the way if this could be ok in an OLTP enviroment, because the average time of a query is << the
>> time of keep alive, in a DWH enviroment could be a problem.
>>
>> Imagine your application server, where there is an ETL, will go down for 1 minute and your
>> transactions are still running on the DWH database, that transactions could run for hours before the
>>
>> keep alive will terminate them, because they are in transaction state and not idle or idle in
>> transaction.

> TCP keepalive will also terminate a session that is currently
> stuck in a long running SQL query if the client end dies.
>
> I think that your problem is that you mix up different meanings of "idle".
>
> In PostgreSQL, a connection is idle (or idle in transaction) if processing
> of the last command is finished and the server is waiting for the next
> command from the client.
>
> In TCP, a connection is idle if there is no network traffic.

> so I have a strange behavior on some test servers.
>
> I am using Centos 6.4 and I set up the tcp keep alive kernel parameter very low to see the effects
> [the postgresql.conf parameter have 0 value so use the OS value] :
>
> net.ipv4.tcp_keepalive_intvl = 2
> net.ipv4.tcp_keepalive_probes = 2
> net.ipv4.tcp_keepalive_time = 1
>
>
> then I run a long query from a remote client and I kill that "psql client" . By the way, even even if
> after long time, the query remains alive,
>
> until it finishes and it returns error because could not return the row to the client.
>
>
> If I do the same with an update, it's the same except become idle in transaction and only after then
> it's killed .
>
>
> So, if " TCP keepalive will also terminate a session that is currently stuck in a long running SQL
> query if the client end dies." ,
> what is wrong on the setup ? Could someone try the tcp keep alive or explain why is not working as
> expected ?

That's as expected; the problem is that I was imprecise and misleading.
Not only does "idle" stand for two things, but also "connection".

While TCP keepalive will terminate a TCP connection after a while
if it detects that the remote end does no longer respond, that does
not imply that the PostgreSQL session is immediately terminated.
That only happens when PostgreSQL tries to read or write on the socket
belonging to the dead TCP connection.

So a long running query will happily continue until it is ready to
write to the socket and then detect that the socket is no longer there.
In the case of an idle session, the server keeps trying to read from
the socket and will notice when it goes away.

So in effect setting low keepalive limits will only make TCP connections
get removed quickly, which will have an effect on idle database sessions,
but not on sessions where the server does not try to communicate with
the client.

Yours,
Laurenz Albe