Re: Stored Procedures woes - Mailing list pgsql-general

From Jochem van Dieten
Subject Re: Stored Procedures woes
Date
Msg-id 4124CF03.7000009@oli.tudelft.nl
Whole thread Raw
In response to Stored Procedures woes  ("Andrew Hall" <temp02@bluereef.com.au>)
List pgsql-general
"Andrew Hall" wrote:
>
> We are using a number of stored procedures that are called often from our
> client programs. I include one here as an example. The problem we are seeing
> is that when executing some of these that deal with a large number of
> records, they begin execution and never return. The process handling the
> request chews 97% of CPU resources and must be cancelled before it will
> release. We have tried examining where in the process it stops via the debug
> output, but this has proven to be fruitless as A. the problem doesn't happen
> with all the SP's (some other SP's deal with far more data but don't have
> the problem), and B. doesn't always happen consistently with the SP's that
> seem to cause problems. What we do know is that the machine doesn't seem to
> be low on memory, never returns any error, and happens regardless of machine
> or PG version (we've tried 7.4.1, and 7.4.2).

Wouldn't the following query be functionally the same as the
procedure you posted (if you fix the rate and the groupid)? If
so, does it perform better and how does the explain look?

UPDATE user_sessions
SET cost_bytes_in = a.costIn,
cost_bytes_out = a.costOut
FROM (
   SELECT
     session_id,
     CASE
       WHEN
(us_rec.hit_bytes_in+us_rec.miss_bytes_in)*$rate/1048576 < 0.0001
THEN 0
       ELSE
(us_rec.hit_bytes_in+us_rec.miss_bytes_in)*$rate/1048576 END
     AS costIn,
     CASE
       WHEN
(us_rec.hit_bytes_out+us_rec.miss_bytes_out)*$rate/1048576 <
0.0001 THEN 0
       ELSE
(us_rec.hit_bytes_out+us_rec.miss_bytes_out)*$rate/1048576 END
     AS costOut
   FROM user_session
   WHERE group_id = $groupId
) a
WHERE group_id = $groupId AND user_id = a.user_id;

Jochem


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: libpq: passwords WAS: scripting & psql issues
Next
From: "Scott Marlowe"
Date:
Subject: Re: shared_buffers Question