Re: Stored Procedures woes

On Thu, 2004-08-19 at 10:53, Andrew Hall wrote:
> Hello,
>
> 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.
...
>
> CREATE OR REPLACE FUNCTION updateUserSessionCost(INTEGER,DOUBLE precision)
> RETURNS INTEGER AS '
> DECLARE
...
> BEGIN
> -- get all user belong to that group
> FOR user_rec IN SELECT distinct user_id FROM user_session where
> group_id=groupId LOOP
> userId = user_rec.user_id;
> -- loop all session record for that user
> FOR us_rec IN SELECT
> session_id,hit_bytes_in,miss_bytes_in,hit_bytes_out,miss_bytes_out FROM
> user_session where user_id=userId and group_id=groupId LOOP
...
> update user_session set cost_bytes_in=costIn,
> cost_bytes_out=costOut WHERE user_id=userId AND
> session_id=us_rec.session_id;

Are you somehow setting off an infinite recursion? How is this being
called?

Try putting RAISE NOTICE statements in to track the progress of the
code.

Oliver Elphick