Re: Zombie processes

Richard-
The prepare/execute problem would make sense to me, except that all of the
parameters in the query are integers and are being appended to the actual
query string (for various reasons) prior to calling prepareStatement()
rather than being set as parameters. The planner should have everything
right from the start. Does this seem to you to be a java caveat, or is it
an area where the query planner could be improved?

Thanks-
Bjorn

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Peterson, Bjorn
Sent: Wednesday, March 30, 2005 8:25 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Zombie processes

My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this
query and determined that the query planner seemed to be causing problems
with this query. By calling "set enable_nestloop = off" before executing
our query we were able to get the execution time down to under 2 minutes,
which is acceptable for our purposes.

Thanks for your response-
Bjorn

-----Original Message-----
From: Richard Huxton [mailto:dev(at)archonet(dot)com]
Sent: Wednesday, March 30, 2005 1:31 AM
To: Peterson, Bjorn
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Zombie processes

Peterson, Bjorn wrote:
>
> 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.

Two things you should start with:
1. Turn query-logging on in your postgresql.conf so we can see exactly
what is happening
2. See if you can simulate this using PREPARE/EXECUTE for the query.

I'm guessing that the Java side of things is producing a prepared query
and substituting parameters in. That might produce a different plan.

First thing is to turn query logging on though, that way you can check
exactly what is being executed.
--
Richard Huxton
Archonet Ltd

****************************************************************************

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.
****************************************************************************

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

****************************************************************************
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.
****************************************************************************