Re: pgAgent question

["Benjamin Krajmalnik" <kraj(at)illumen(dot)com>]

Title: RE: pgAgent question

The job has a single execution step:

 

select * from fn_calcstats2();

 

The function code follows:

 

CREATE OR REPLACE FUNCTION fn_calcstats2()
  RETURNS "timestamp" AS
$BODY$
DECLARE
   startdate timestamp;
   myrecord  Record;
   myrecord2 Record;
   mycursor refcursor;
BEGIN

 startdate := (current_date - interval '30 days')::timestamp;
 FOR myrecord in select * from tblkstests LOOP
   open mycursor for select  avg(replyval) as myavg  , stddev(replyval) as mysd from tblksraw where tblksraw.testguid = myrecord.testguid and tblksraw.testid = myrecord.testid and tblksraw.testtime >= startdate;
   fetch mycursor into myrecord2;
   update tblkstests set runningavg = myrecord2.myavg, sd = myrecord2.mysd, lcl = myrecord2.myavg - (3 * myrecord2.mysd), ucl = myrecord2.myavg + (3 * myrecord2.mysd) where tblkstests.testguid = myrecord.testguid and tblkstests.testid = myrecord.testid ;
   close mycursor;
 END LOOP;  
 
-- select startdate;
 return startdate;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

---

From: Dave Page [mailto:dpage(at)vale-housing(dot)co(dot)uk]
Sent: Thu 4/20/2006 9:35 AM
To: Benjamin Krajmalnik; pgadmin-support(at)postgresql(dot)org
Subject: RE: [pgadmin-support] pgAgent question

 

---

From: pgadmin-support-owner(at)postgresql(dot)org [mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of Benjamin Krajmalnik
Sent: 20 April 2006 15:02
To: pgadmin-support(at)postgresql(dot)org
Cc: Dave Page
Subject: Re: [pgadmin-support] pgAgent question

Yes.

PgAdmin/pgAgent and PostgreSQL ar running on the same box.

RUnning the stored procedure even from a remote pgAdmin at the console results in the same 16 second performance.

 

I may well have to take this one off to another list as pgAgent simply executes the query using libpq like pgAdmin (though it does so synchronously iirc, whereas pgAdmin runs asynchronously and displays the results as they come in).

 

Can you share the code in the job step(s), and the stored procedure?

 

Regards, Dave.