Thread: Logging duration of batch runs
Hi,
I have a stored procedure run periodically that assign accounting records
to their respective customers based on username and other criteria.
It also does all kinds of validation work on the accounting records.
I would have liked to have the procedure log start and stop times
using RAISE NOTICE and also store start and stop time of each run
to a logging table.
I currently have following:
---snipp---
CREATE OR REPLACE FUNCTION radius.radius_acct_batch() RETURNS void
AS '
DECLARE
batch_id int;
realm RECORD;
start_time timestamp;
stop_time timestamp;
BEGIN
-- start new batch
start_time := ''now'';
INSERT INTO radius.radius_acct_batch ( radius_acct_batch_run_start ) VALUES ( start_time );
SELECT INTO batch_id currval(''radius.radius_acct_batch_radius_acct_batch_id_seq'');
RAISE NOTICE ''radius.radius_acct_batch, radius_acct_batch_id=%, start_time=%.'', batch_id, start_time;
-- do actual work
...
...
...
...
-- register end date in batch
stop_time := ''now'';
UPDATE radius.radius_acct_batch SET radius_acct_batch_run_end=stop_time WHERE radius_acct_batch_id=batch_id;
RAISE NOTICE ''radius.radius_acct_batch, radius_acct_batch_id=%, stop_time=%, done.'', batch_id, stop_time;
RETURN;
END'
LANGUAGE plpgsql;
---snipp---
the problem is that start_time and stop_time are identical.
Is there any way I can get plpgsql to reevaluate now at the end
of the batch ???
Greetings
Christian
--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
On Wed, Jun 23, 2004 at 12:49:19PM +0200, Christian Kratzer wrote: > Hi, > > I have a stored procedure run periodically that assign accounting records > to their respective customers based on username and other criteria. > It also does all kinds of validation work on the accounting records. > > I would have liked to have the procedure log start and stop times > using RAISE NOTICE and also store start and stop time of each run > to a logging table. <snip> > the problem is that start_time and stop_time are identical. > > Is there any way I can get plpgsql to reevaluate now at the end > of the batch ??? You want timeofday(), not now(). Now() is transaction time, timeofday() is the real current time. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Hi, j On Wed, 23 Jun 2004, Martijn van Oosterhout wrote: > On Wed, Jun 23, 2004 at 12:49:19PM +0200, Christian Kratzer wrote: >> Hi, >> >> I have a stored procedure run periodically that assign accounting records >> to their respective customers based on username and other criteria. >> It also does all kinds of validation work on the accounting records. >> >> I would have liked to have the procedure log start and stop times >> using RAISE NOTICE and also store start and stop time of each run >> to a logging table. > > <snip> > >> the problem is that start_time and stop_time are identical. >> >> Is there any way I can get plpgsql to reevaluate now at the end >> of the batch ??? > > You want timeofday(), not now(). Now() is transaction time, timeofday() > is the real current time. thankx! That did it ... Greetings Christian -- Christian Kratzer ck@cksoft.de CK Software GmbH http://www.cksoft.de/ Phone: +49 7452 889 135 Fax: +49 7452 889 136
Christian Kratzer wrote: > Hi, > > I have a stored procedure run periodically that assign accounting > records to their respective customers based on username and other criteria. > It also does all kinds of validation work on the accounting records. > > I would have liked to have the procedure log start and stop times using > RAISE NOTICE and also store start and stop time of each run > to a logging table. [snip] > Is there any way I can get plpgsql to reevaluate now at the end > of the batch ??? You want timeofday() rather than now(). Note that it returns text rather than a timestamp. -- Richard Huxton Archonet Ltd