Re: Backends created by ODBC live forever ...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Hans-Jürgen Schönig <hs(at)cybertec(dot)at>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Backends created by ODBC live forever ...
Date: 2003-03-10 18:34:18
Message-ID: 18864.1047321258@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <hs(at)cybertec(dot)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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-03-10 18:51:19 Roadmap for FE/BE protocol redesign
Previous Message Justin Clift 2003-03-10 18:33:34 Re: Who puts the Windows binaries on the FTP server?