Thread: hanging select processes

Hi,
I am using the odbc driver to provide MS Acess with a view on our
postgres db, securing the connection using stunnel.

Everthing works fine, and it seems to be a fantastic solution to give
our clients a user friendly look at the data.
the only problem we have discovered when testing this out is that if the
client runs a query in access and then kills the access process on their
computer. we end up with a postgres SELECT thread running at 99% cpu
time on our backend. I have found a thread possible hinting at this in
the archives - regarding 'zombie' processes, but these are not zombie
processes - we can kill them though a restart of postgres is required to
get it all working again.

Now we cant stop our clients being idiots - i guess we'd all be a lot
happier if we could! Is there a way to mitigate or even stop this errant
behaviour? surely postgres should stop the select if the client
dissapears in a puff of smoke?
cheers
John

John Rogers <John@delosis.com> writes:
> surely postgres should stop the select if the client
> dissapears in a puff of smoke?

The current design intention is that the backend will notice loss of
connection when it next tries to receive a command from the client
--- and not before.  You could argue that a SELECT is side-effect
free and can be aborted arbitrarily, but I wouldn't agree, because
the SELECT could be invoking a user-defined function that does have
side effects.  If we were to abort upon noticing that the output data
didn't seem to be going anywhere, then the behavior would be both
timing-dependent and platform-dependent.

> ... we end up with a postgres SELECT thread running at 99% cpu
> time on our backend.

If the SELECT is taking more CPU than it otherwise would, then that
might represent a bug we oughta fix.  But I don't think that finishing
out a requested SELECT is in and of itself a bug.

            regards, tom lane

John Rogers <John@delosis.com> writes:
> As for the last comment you made though, I've had a look at the load on
> the server during this simulated Access crash, and it DOES seem to me
> that the thread goes out of control afterwards. Setting up complex query
> at the access end which will repeadedly select the same view from
> postgres results in a relatively low load on the backend; but killing
> access during this same query causes the SELECT process to ramp itself
> up to 100% over the time course of a couple of seconds.

Hmm.  With no network delays for delivery of output data, you could
expect the SELECT to become CPU-bound; it would just be sitting there
running the query as fast as it could.  So I'm not sure that the above-
described behavior is a problem.  However, if what is happening is that
it's getting stuck in an infinite loop trying and failing to output
data, then that's definitely bad.  So I guess the question is: does the
backend terminate after about the same number of CPU seconds that it
would ordinarily take to process the query?  Or does it seem to run
forever until killed?  Also, can you stop it with "kill -INT" when it's
in this state?

            regards, tom lane