Re: keep alive and running query

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.
>
> 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

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 ?

Have a nice day

Mat