Re: [GENERAL] 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.

Thanx for your advice. I'm just absolutely worned out. Sorry.

> >> 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.

> > 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.

> > 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>"

Exactly.

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

Yes I'm quite happy. My hardware is: 2 double-core Xeon, 8Gb RAM,
RAID5. What about other software... it's dedicated PG server so I have
no problem with it.

--
Regards,
Sergey Konoplev