Stored Procedures woes - Mailing list pgsql-general

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

We must be doing something fundamentally wrong , so if anyone can shed any
light on why this might be happening and how I can track it down, would be
great. We don't have a whole lot of experience with PG, so there may very
likely be something we're doing wrong.

Thanks,

Andrew.

CREATE OR REPLACE FUNCTION updateUserSessionCost(INTEGER,DOUBLE precision)
RETURNS INTEGER AS '
 DECLARE
      groupId ALIAS FOR $1;
      rate ALIAS FOR $2;
      user_rec RECORD;
      us_rec RECORD;
      userId text;
      costIn DOUBLE precision;
      costOut DOUBLE precision;
 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
              costIn :=
(us_rec.hit_bytes_in+us_rec.miss_bytes_in)*rate/1048576;
              costOut :=
(us_rec.hit_bytes_out+us_rec.miss_bytes_out)*rate/1048576;
              if (costIn < 0.0001) then
                  costIn := 0;
              end if;
              if (costOut < 0.0001) then
                  costOut := 0;
              end if;

              update user_session set cost_bytes_in=costIn,
                  cost_bytes_out=costOut WHERE user_id=userId AND
session_id=us_rec.session_id;

          END LOOP;
      END LOOP;
      return 1;
 END;
 ' LANGUAGE 'plpgsql'
;