Re: Clients disconnect but query still runs

On Thu, Jul 30, 2009 at 10:59 AM, Csaba Nagy<nagy(at)ecircle-ag(dot)com> wrote:
> But if I get bad memory or bad wire I'll get much worse problems
> already, and don't tell me it will work more reliably if you don't kill
> the connection. It's a lot better to find out sooner that you have those
> problems and fix them than having spurious errors which you'll get even
> if you don't kill the connection in case of such problems.

Are you sure? Do you know how many times you haven't even found out
you had a problem because TCP just silently kept working despite the
problem?

Having had to use protocols which imposed their own timeouts on lame
hotel networks, buggy wireless drivers, and bad DSL connections and
found my connections dying every few minutes I can say it's
maddeningly frustrating. Especially knowing that TCP was *supposed* to
work in this scenario and they had broken it by trying to be clever.

>> Well it ought to have eventually died. Your patience may have ran out
>> before the keep-alive timeouts fired though.
>
> Well it lived for at least one hour (could be more, I don't remember for
> sure) keeping vacuum from doing it's job on a heavily updated DB. It was
> not so much about my patience as about starting to have abysmal
> performance, AFTER we fixed the initial cause of the crash, and without
> any warning, except of course I did find out immediately that bloat
> happens and found the idle transactions and killed them, but I imagine
> the hair-pulling for a less experienced postgres DBA. I would have also
> preferred that postgres solves this issue on it's own - the network
> stack is clearly not fast enough in resolving it.

Indeed, properly set TCP keepalives don't time out for over 2 hours.
But that's configurable in postgresql.conf.

--
greg
http://mit.edu/~gsstark/resume.pdf