Re: pg_cancel_backend() does not work with buzz queries

On Oct 3, 2007, at 6:47 AM, Richard Huxton wrote:

> Sergey Konoplev wrote:
>>> 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.
>> Thanx for your advice. I'm just absolutely worned out. Sorry.
>
> Know that feeling - let's see if we can't sort this out.
>
>>>>> 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?
>> As far as I remember I do.
>
> Hmm - check Magnus' thoughts on pl/python. Can't comment on Python
> myself. Are you sure it's not always the same few function(s) that
> cause this problem?
>
>>>> 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?
>> Yes but the metter is that this procedures usualy use CPU just a
>> little so I can't find out if there is some oddity or not.
>
> OK, so it's not that it's stuck in a loop wasting a lot of CPU

In order to get at least some idea of what these processes are (or,
are not) doing, run an strace (or your OS's equivalent) on the
process before killing it. Let us know what you see there.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com