Hans-Jürgen Schönig <hs@cybertec.at> writes:
> As far as I have seen it did not time out - at least it seems that it
> doesn't. Also, if we were running a system with many unreliable clients
> we'd run out of memory soon. It is also a potential security problem.
I do not see a security issue. There should be a TCP timeout, but it
may well be long enough (order of hours) to not be very useful to you.
> If we had something like: "max_connections_from IP" or "backend_timeout
> TIME" we could solve the problem more easily. How about that?
max_connections_from_IP would not solve your problem: it would amount to
denying service to individual clients until whenever the TCP stack did
finally time out.
We've considered and rejected a backend-side timeout before. I'd be
willing to consider a timeout that only runs while the backend is idle
and not inside a transaction block, but I'm not sure that solves your
problem either. Clients that send BEGIN and then go to sleep would
defeat such a timeout. Clients that crash mid-transaction would be a
problem too --- although I believe the kernel TCP stack will time out much
more quickly if it is trying to push unsent data than when it thinks the
connection is idle, so clients that crash while receiving data may not
be too much of a problem.
The ideal solution would really be to get your kernel to issue TCP
KEEPALIVE queries at some shorter interval than what the TCP RFCs call
for, like after a few minutes of inactivity instead of an hour or more.
I dunno whether this is adjustable in the Linux kernel, but I'd suggest
looking there first. I don't feel very comfortable with having Postgres
second-guess the transport mechanism about whether a connection is still
alive.
regards, tom lane
