Re: pg_cancel_backend() does not work with buzz queries - Mailing list pgsql-general

Hello again,

Sorry for the deal with my answer it was realy hectic week so I
couldn't even check my mail.

2007/10/3, Richard Huxton <dev@archonet.com>:
> 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?

Yes I'm shure. I've noticed about 10 queries and procedure calls
buzzing at least.

>
> >>> 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
>
> >> So - the symptoms are:
> [snip]
> > Exactly.
>
> So - we need to solve two mysteries
> 1. Why are these functions not returning?
> 2. Why does SIGINT not interrupt them?
>
> >> 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.
>
> Well, the places I'd look would be:
> 1. Hardware (you're happy that's fine, and it's not quite the problems
> I'd expect)
> 2. Drivers (same as #1)
> 3. Client connectivity (but you say the client is fine)
> 4. External interactions (see below)
> 5. Bug in PG extension (pl/python)

I think it's not only lp/python problem cos I saw pl/pgsql and simple
queries also. For example:
select * from  (
    select d.*, cto.full_name, cast(st.name || ', ' || r.name as
varchar) as cityname
    from
        drivers d
        join cars_trailers_owners cto on
            d.cars_trailers_owner_id = cto.id
        join settles st on
            d.settle_id = st.id
        join regions r on
            st.region_id = r.id
    order by sname, name, pname
) as sq

> 6. Bug in PG core code
>
> Do any of your functions interact with the outside world - fetch
> webpages or similar? It could be they're waiting for that. If you're
> using a library that could hang waiting for a response and also block
> SIGINT at the same time that would explain everything.

No, most of them don't.

--
Regards,
Sergey Konoplev