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
****************************************************************************
This email may contain confidential
material. If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
***************************************************************************