Re: Clients disconnect but query still runs

Csaba Nagy wrote:

> A simple ping to the client would have
> cleared the fact that the client is not there anymore.

Yep. It'd also stop PostgreSQL working for clients with software
firewalls, since most of them drop ICMP ECHO ("ping").

TCP keepalives are designed to do the same thing, but do it reliably and
properly. Why not configure your tcp keepalive intervals instead?

Note that tcp keepalives won't kill the backend if it's in the middle of
working on an I/O or CPU intensive query. It'll die when it tries to
send the client data, or tries to read something more from the client.

One trick you can use if you're really worried about this is `RAISE
NOTICE' periodically during a big complicated PL/PgSQL procedure. The
NOTICE should be sent to the client immediately, which will cause Pg to
notice the socket is dead. It won't help you with long running normal
SQL queries, though.

I do think it'd be kind of nice to occasionally poke the connection to
see if it's alive while executing a query. Doing it in a SIGALRM handler
so as not to require the query execution path to be scattered with
"checkConnStatus()" or whatever calls might be one approach. It'd have
_very_ minimal cost, would only cost anything while the backend was
working, and would help avoid wasted work when a client vanishes.

When the backend is idle (including "idle in transaction") it can
disable any timer. It'll be poll()ing or select()ing or read()ing or
whatever on the socket and will immediately notice if it breaks.

> I would also be
> happy to pay the cost of pinging the clients let's say once per a minute
> (or configurable interval). Considering that the connections are one to
> one with a client, it's enough to have a single timer which periodically
> signals each backend to ping it's client, but this is implementation
> details for which I have no clue how it would be best,

That applies in your situation, but for many others that's not the case.
Not that it matters a great deal.

> the main thing
> is: I would love to have this functionality. It's extremely hard to
> secure all clients against crash, and a crash of one of the clients in
> the middle of a transaction can have very bad consequences (think
> indefinitely stucked open transaction).

Nope. Just tune your keepalives if you have hopelessly flakey clients.

Even if the client _program_ crashes, though, you shouldn't have
anything left lying around. It's only if the client _OS_ crashes or the
machine is hard-reset that you should be left with a backend lying
around until tcp keepalives notice.

--
Craig Ringer