On Sun, Aug 11, 2013 at 9:25 PM, Josh Berkus <josh@agliodbs.com> wrote:
> No, it's been a chronic issue since we got SMP support, pretty much
> forever. Why do you think we have pg_terminate_backend()?
>
> The problem, as explored downthread, is that there's no clear way to fix
> this. It's a problem which goes pretty far beyond PostgreSQL; you can
> experience the same issue on Apache with stuck downloads.
No. There are multiple problems that can cause a stuck orphaned server
process and I think you're conflating different kinds of problems.
a) If the client dies due to C-c or kill or any other normal exit
path. There's really no excuse for not detecting that situation
*immediately*. As suggested in the original post the client could
notify the server before it dies that it's about to die.
b) If the client dies in some abnormal path such as kill -9. In that
case we could easily detect the situation as quickly as we want but
the more often we probe the more time we waste and cpu wakeups we
waste sending probes. We would only need to react to errors on that
connection (RST packets which will cause a SIGIO or eof depending on
what we ask for), not a lack of response so it doesn't need to make
things more fragile.
c) If the client goes away either because it's turned off or the
network is disconnected. This is the problem Apache faces because it's
exposed to the internet at large. We're not entirely immune to it but
we have much less of a problem with it. The problem here is that
there's really no easy solution at all. If you send keep-alives and
time them out then transient network connections become spurious fatal
errors.
> Our advantage over MySQL is that the idle process isn't likely to crash
> anything ...
--
greg