I have a query on a database that has been running for nearly 24 hours
at the moment. The query itself doesn't seem like it should take very
long to run, so it seems like there's something else going on here.
The output from pg_stat_activity looks like this:
SELECT procpid, waiting, current_query, query_start, backend_start FROM
pg_stat_activity WHERE current_query <> '<IDLE>' AND usename <> 'postgres';
procpid | waiting | current_query |
query_start | backend_start
---------+---------+-------------------------------------------------+-------------------------------+-------------------------------
17504 | f | SELECT cam.netboxid, |
2008-09-09 13:44:01.035885+02 | 2008-09-09 13:43:58.613948+02
: ifindex,
: arp.ip,
: REPLACE(mac::text, ':', '') AS portname,
: cam.start_time,
: cam.end_time,
: vlan
: FROM cam
: JOIN netbox USING (netboxid)
: JOIN arp USING (mac)
: JOIN prefix ON (arp.prefixid = prefix.prefixid)
: JOIN vlan USING (vlanid)
: WHERE cam.end_time='infinity'
: AND arp.end_time='infinity'
: AND vlan IS NOT NULL
:
SELECT pg_cancel_backend(17504) has no effect, neither does kill 17504
from the shell.
I tried "strace -p17504", and this gave me just the following output:
sendto(7, "\7\0\0\0\003771\0\0\0\00224\0\0\0\017127.120.213.18"..., 968,
0, NULL, 0 <unfinished ...>
Does anyone have any further troubleshooting suggestions that I can do,
to figure out why this query have "crashed"?
The pg version is 8.2.9 on RHEL4
--
Tommy Gildseth