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