Re: Stored Procedures woes

"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