Re: Safely Killing Backends (Was: Applications that leak connections)

On Tue, Feb 08, 2005 at 07:31:13AM -0500, Jim Wilson wrote:
> That\'s unfortunate. I\'ve tried to explain my position off list to
> Marco, but it really isn\'t worth debating. FWIW I think this thread
> was started by someone with application issues. The fact is, such
> things happen.

Well, I read the thread on pg-hackers [1] about this being a bad idea
currently and the issue seems to be:

1. The SIGTERM is the same as a FATAL error and this code path has not
been very well tested. Are locks, etc all correctly removed? The only
cases that *are* well tested are cases where these things don't matter.

In other words, it will probably work fine, but it's not so well tested
that the pg hackers are willing to bless a backend function
implementing it.

2. If the backend is so stuck that SIGTERM isn't working, then I guess
that's a bug but not enough examples have been collected to work out
the problem. In this case you probably can't exit without considering
the shared memory corrupt.

3. In theory it would be nice to have a "cancel then exit" signal, but
we're clean out of signal numbers.

4. It appears the original person had a problem with not tracking used
resources properly in a language that neither garbage-collects nor
reference-counts. If you know you only ever want to open one connection
you can solve this problem by creating an open_connection function
which checks a global variable to see if a connection has already been
opened and returns the same one if it has.

> Unfortunately Marco choses speaks for "any list" and I\'ll just
> repeat that I find this instability issue the most significant
> drawback for Postgres installations. This doesn\'t mean that there
> aren\'t other areas of priority for other users. And no, I do not
> want to debate the meaning of the word "instability". :-)

I guess it appears on the list of anybody who regularly deals with this
problem. That list appears to be mutally exclusive with anyone who can
fix it...

I wonder how one would test the SIGTERM path anyway... To quote Tom
Lane on chances of corruption [2]:

> Not only wouldn't I give you those odds today, but I don't think we
> could ever get to the point of saying that session kill is that
> reliable, at least not from our ordinary methods of field testing.
> It'd require significant focused code review and testing to acquire
> such confidence, and continuing effort to make sure we didn't break
> it again in the future.
>
> If we had infinite manpower I'd be happy to delegate a developer or
> three to stay on top of this particular issue. But we don't :-(

I don't know if PostgreSQL has ever had the concept of bounties for
stuff. It's an interesting idea...

[1] http://archives.postgresql.org/pgsql-patches/2004-07/msg00457.php
[2] http://archives.postgresql.org/pgsql-patches/2004-07/msg00480.php

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.