Re: pg_cancel_backend() does not work with buzz queries

Don't forget to cc: the list.
Try not to top-post replies, it's easier to read if you reply below the
text you're replying to.

Sergey Konoplev wrote:
>> 1. Is it always the same query?
>> 2. Does the client still think it's connected?
>> 3. Is that query using up CPU, or just idling?
>> 4. Anything odd in pg_locks for the problem pid?

> 1. No it isn't. I have few functions (plpgsql, plpython) that cause
> such situations more often than another but they are called more often
> also.

OK, so there's no real pattern. That would suggest it's not a particular
query-plan that's got something wrong.

Do you always get this problem inside a function?

> 2. The client just waits for query and buzz.
> 3. They are using CPU in usual way and their pg_lock activity seems normal.

So the backend that appears "stuck" is still using CPU?

> 4. No I haven't noticed anything odd.

So - the symptoms are:
1. Client hangs, waiting for the result of a query
2. You notice this
3. You issue pg_cancel_backend() which sends a SIGINT which doesn't do
anything
4. You have to cancel the query from the command-line using "kill -9
<backend-pid>"

Are you happy that your hardware and drivers are OK? There aren't
problems with any other servers on this machine?

--
Richard Huxton
Archonet Ltd