Re: pg_cancel_backend() does not work with buzz queries

2007/10/3, Erik Jones <erik(at)myemma(dot)com>:
> 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.
>

That is what I've got using strace with the buzzed process:

pgdb:~ # strace -dirfvx -p 19313
Process 19313 attached - interrupt to quit
[wait(0x137f) = 19313]
pid 19313 stopped, [SIGSTOP]
[wait(0x57f) = 19313]
pid 19313 stopped, [SIGTRAP]
0.000000 [ffffe410] send(8,
"\x00\x01\x74\xff\xff\xff\xff\x00\x00\x00\x01\x66\xff\xff"..., 8192, 0

[Output stoped here and after half hour I interupted strace]

cleanup: looking at pid 19313
<unfinished ...>
Process 19313 detached
pgdb:~ #

--
Regards,
Sergey Konoplev