2013/12/16 Albe Laurenz <laurenz.albe@xxxxxxxxxx>
desmodemone wrote:TCP keepalive will also terminate a session that is currently
> 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.
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.
Yours,
Laurenz Albe
Hello Laurenz,
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
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 ?
what is wrong on the setup ? Could someone try the tcp keep alive or explain why is not working as expected ?
Have a nice day
Mat
