Zombie processes

I’m running out of ideas for the following problem:

I have a moderately complex query as follows-

SELECT t.term_id, a.user_id, a.time_slot, a.status,

SUM(CASE WHEN a.date>=t.start_date THEN 1 ELSE 0 END), COUNT(a. date)

FROM "Table1" a, "Table2" t, "Table2" ytd, "Table3" cu, "Table4" c, "Table5" co, "Table6" s

WHERE a.type=1 AND a.status IN(1,2,3,4) AND a.date>=ytd.start_date AND a.date<=t.end_date AND

a.date<=now() AND a.user_id=cu.user_id AND a.time_slot=cu.course_id AND cu.course_id=c.course_id AND co.course_offered_id=c.course_offered_id

AND co.school_id=s.school_id AND s.district_id=2 AND ytd.term_id=t.top_term_id GROUP BY a.user_id, a.time_slot, t.term_id, a.status

ORDER BY a.user_id, a.time_slot, t.term_id, a.status

I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the query returns appropriate values after about 30 seconds when executed from a psql console.  However, when running this query from a Java application on the same machine through the postgres JDBC driver, the Java app hangs on st.executeQuery().  The query shows up in pg_stat_activity for about 3 minutes then goes away, but the process referenced by the pg_stat_activity remains active and consumes 50% of the CPU resources indefinitely until I kill it off.  I let it run for over an hour yesterday.  I can reproduce this every time I run this query.

Any ideas?

Bjorn Peterson

Software Engineer

Pearson School Technologies

Bloomington, MN

Bjorn.Peterson@pearson.com