Periodic freezing of backend processes - Mailing list pgsql-bugs

PostgreSQL 7.0.2
Debian 2.3 (woody)
Linux Kernel 2.2.15

Hi,

I am finding that I periodically have a backend process just 'freeze' on
me.  It's not like it's doing something that all of a sudden get's
wildly inefficient because this can be when I'm repetitively processing
in a loop, and where responsiveness is normally sub-second I have waited
hours to see if these terminate and they don't.

What can I do to tickle the backend processes to get it to tell me where
it is at?

I have found that if I kill the backend process that is locked up, then
do exactly the same query, it locks up again.  BUT if I shut down and
restart the postmaster and then do exactly the same query (reload my web
page, in fact) the response is immediate again.

A couple of other points to note:
 - the webserver, database server and all are on my laptop - I am the
only user and there is only one request active at the time this happens
(although I have seen it happen on our production machine, running 6.5.3
as well).
 - I have seen the freeze happening from PHP scripts as well as from
Perl scripts.
 - A 'ps' does not show the process as 'waiting':
$ps flaxww | grep postgres | grep -v grep
000    31 15629     1   0   0  5824 1152 select S    pts/2      0:00
/usr/lib/postgresql/bin/postmaster -b /usr/lib/postgresql/bin/postgres
-B 256 -N 16 -D /var/lib/postgres/data -d 0 -o  -F -S 4096
040    31 15634 15629   0   0  6432 4380 select S    pts/2     11:14  \_
/usr/lib/postgresql/bin/postgres localhost andrew newsfeed
UPDATE

That's right now, so it's frozen on an 'UPDATE' and looking at my (perl)
program the only 'UPDATE' is this one:
    UPDATE story SET wcount=$count WHERE story_id=$story_id;
With story_id being the table's primary key, of course.  Up until it
froze it was processing one of these UPDATE's every second or two
(amongst many other SQL statements).  Earlier in the evening a similar
thing happened except the statement was an 'INSERT' and the program had
been running fine for about three hours before it locked up.

If I kill the backend process that has locked up I may get messages when
I next do a VACUUM that say I need to recreate the indexes on some table
too.

Sorry if this is a bit vague, but if there's some signal I can send to
the locked process to try and tell where it is or what it's trying to
do, perhaps I can find out more next time it happens.  I've tried kill
-3 and kill -5 but don't seem to get any core files.  I'd crank up the
logging except that whenever I do that I tend to run out of disk space
:-(

Thanks,
                    Andrew.
--
_____________________________________________________________________
            Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267