Re: Postgresq 8,1 hangs when running function

On 8/21/06 7:26 AM, "ben sewell" <mosherben(at)gmail(dot)com> wrote:

> Hi Sean,
> what I meant was that when I tried executing my function, postgres wouldn't
> return any results and not do anything. CPU Usage would increase to 50% when
> I tried executing the function. The funny thing is that CPU usage was at
> 100% and the sys admin called me up and asked about it. Apparently there
> were 10 instances of postgres running so we ended up having to restart
> anyways. I have tried to run the function after the restart, but everytime I
> do, CPU usage raises to 50% and I have to stop postgres as a serivce to get
> CPU usage to 1%.
>
> For the database employees are working with, its an access database running
> on the server not my postgres database. The postgres database is supposed to
> replace the existing access one once we've got report generation speeded up
> which I thought I had done until it was consuming all the resources.
>
> As for a new pc to run postgres on, it's not even worth it this is my last
> week of my job. I have learnt alot thoughout my time at the place through. I
> just don't get why postgres is hanging considering the hardware that is on
> the server, and the Access database is only being used by 2 users so that
> wouldn't slow it down much.
>
> I forgot to tell what code I was typing into the command prompt via terminal
> services:
>
> select * from reports(18,2,null,null,null.null,null,null,null,null) as
> (employee_first_name varchar,employee_surname varchar, data_issued date,
> client_first_name varchar,client_middle_names varchar,client_surname
> varchar, plantype varchar,plangroup varchar,policy_number
> varchar,sum_assured numeric,benefit varchar,premium numeric,brokerage
> numeric,comments varchar);
>
> Ben

I would try using only the SQL parts of the function outside of the
function. Use EXPLAIN ANALYZE to run them with typical parameters and see
what you get. It is possible that you are doing some large cross-join or
something like that that is causing postgres to have to return a larger
result set than you had anticipated. I didn't read through your code to try
to sort this out--sorry.

Sean