Thread: Postgresq 8,1 hangs when running function

On 8/21/06 5:38 AM, "ben sewell" <mosherben@gmail.com> wrote:

> Hi guys,
> I'm running postgresql 8.1 on a P4 3.0GHz,480 MB RAM, 160GB SCSI hard drive
> on Windows XP. Postgres has been crashing when I have tried testing my
> reports function, which i leads me to believe it is something in the
> function because doing normal select statements works fine. Can anyone check
> my code before I ask my boss if I could restart the server, as a restart
> means 20 mins work lost for employees? Just for reference, tblnewbusiness
> containts just under 8,000 records,

Hi, Ben.  What do you mean when you say postgres has been "crashing"?

As for "restarting" the server, you can simply do a restart of postgres,
which should mean just a second or so of downtime.  You don't need to
physically reboot the machine.

As a more important point, if you have employees using the database that you
are working on and you are feeling like you need to restart the server,
etc., you probably shouldn't be working on that machine.  Instead, buy a
$700 laptop (or a $350 PC) and install postgres.  Then, dump the database
from the production machine and load it into your "test" machine.

Sean

On 8/21/06 7:26 AM, "ben sewell" <mosherben@gmail.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

On 8/21/06 8:27 AM, "ben sewell" <mosherben@gmail.com> wrote:

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

Ben,

When you do a select on four tables without any joins, postgres is going to
do a FULL CROSS JOIN on ALL the tables!  If you use 4 tables with 100 rows
each, there will be 100^4 rows (that is 10^8 rows!)!!!  That is what I think
is happening with the SQL from your function.  Doing this will take a huge
amount of memory and computer resources.

If your SQL from your function has the same behavior when you execute it, it
is likely one of two problems:

1)  You are doing an unconstrained CROSS JOIN and getting a large result set

Or

2)  You do not have some critical indices on the tables.

It sounds like you might need to go back to basics and build your queries
carefully, placing indices as you go along and doing explain analyze as you
add tables to the results.  When you add a new table to the query and the
time increases drastically, look for indices that you can add and
triple-check that you constrained the join in the way you think you should.

Finally, if you have someone nearby that is experienced with SQL databases
and knows your database, it might be worthwhile to try to get him/her to sit
down with you for an hour or so to help out with your rather extensive SQL
statements.

Sean

On Mon, Aug 21, 2006 at 08:37:21AM -0400, Sean Davis wrote:
> If your SQL from your function has the same behavior when you execute it, it
> is likely one of two problems:
>
> 1)  You are doing an unconstrained CROSS JOIN and getting a large result set

Check the parentheses around the OR expressions in the queries'
WHERE clauses.  The queries have their join conditions separated
by AND and then an OR that looks like it needs an extra set of
parentheses.  That is, instead of this:

  AND cond1 AND cond2 AND (cond3 AND cond4) OR (cond5 AND cond6)

try this:

  AND cond1 AND cond2 AND ((cond3 AND cond4) OR (cond5 AND cond6))

or

  AND cond1 AND cond2 AND (cond3 AND cond4 OR cond5 AND cond6)

Otherwise, as Sean said, you're getting cross joins.

--
Michael Fuhr

On 8/21/06 9:31 AM, "ben sewell" <mosherben@gmail.com> wrote:

> Hi guys, thanks for the tips. Yup I ran the query with the joins in this
> time. Explain analyze returned 68 rows this time, in 570 MS. Is the MS time
> normal?

Explain analyze will return the time to complete the query, along with times
for sub-parts of the query.  There is no "normal", as it depends on the
complexity of the query, as well as tuning, etc.  But 570 ms is 1/2 of one
second, which is probably acceptible.

> ok now I seem to be getting somewhere, inputting stuff into my function and
> now it's saying returned record type does not match expected record type,
> even thorugh I give the types of the fields in the query.  Any ideas? I've
> checked the fields I am expecting to be returned against what they are
> stored as in the DB and there are nothing different.

When you actually run the SQL (outside of the function), what is returned?
How does THAT compare to what you asked for?

Sean

"ben sewell" <mosherben@gmail.com> writes:
> 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.

There are much less aggressive ways of canceling a long-running query
than restarting the whole server.  On Windows I think you need to do
"pg_ctl kill INT <processID>" to stop a query running in a particular
backend process.

            regards, tom lane

On Mon, Aug 21, 2006 at 02:31:37PM +0100, ben sewell wrote:
> ok now I seem to be getting somewhere, inputting stuff into my function and
> now it's saying returned record type does not match expected record type,
> even thorugh I give the types of the fields in the query.  Any ideas? I've
> checked the fields I am expecting to be returned against what they are
> stored as in the DB and there are nothing different.

The column list in the query you posted doesn't match the column
list being returned from the function: the query is missing
provider_company between plangroup and policy_number.

Comparing column lists might be easier and less error-prone if you
align them vertically, one column per line.  Then you could view
the function's query and the calling query side-by-side and quickly
observe whether they match or not.

--
Michael Fuhr