Re: Postgresq 8,1 hangs when running function

Hi Sean,
I've just tried doing an explain analyze on a select query without any joins
and CPU Usage is going between 50% and 59%. The wierd thing is that there
select statement is only taking data from 4 tables so i would have imagined
it would have been ok. Never the less, it's still hanging even on doing
explain analyze.

I've attached my postgresql.conf file for anyone that wants to have a look
at it.

Ben

On 8/21/06, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> wrote:
>
>
>
>
> 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
>
>