Thread: Stored Procedures woes

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'
;

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

Oliver Elphick <olly@lfix.co.uk> writes:
>> We are using a number of stored procedures that are called often from our
>> client programs.

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

I doubt it would be an infinite-recursion problem, as that would soon
lead to stack overflow and core dump (or at least very obvious memory
bloat).  I'm wondering about inefficient plans being generated for the
queries in the procedure.

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

Right.  See also recent discussion on pgsql-performance about
investigating plans used for plpgsql queries.

            regards, tom lane

"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