Обсуждение: Inserting a timed delay in a pl/pgsql procedure

I have a problem right now where the execution time of a scheduled
stored procedure is creating an issue with our background data logging
system.
Initially I thought it may be caused due to table locks or the like, but
after much testing I have ruled that out, since data is being inserted
while the scheduled SP runs.

The problem cause is one over which unfortunately I have no control.
However, I wanted to run a test and see if I cuold minimize the problem
by inserting a delay in the stored procedure so that after each loop of
the data aggregation, it "sleeps" for a second or so to let the
backround logger despool its queue.

I tried the following:

  FOR myrecord in select * from tblkstests where stats = true and
accountno=423 LOOP
       returndate := fn_calcteststats(myrecord.kstestssysid);
       nextexecdate := returndate + interval '0.25 second';
       LOOP
           EXIT when current_timestamp > nextexecdate;
       END LOOP;
  END LOOP;


However, it appears that current_timestamp is not being recalculated.
Any ideas? While normally we want the stored procedures to run as
quickly as possible, this is a case where I need to insert a delay.
I guess an option would be to place a counter and loop through it, but
it does not allow me the control I relly need.

I am not sure this will resolve the problem, but it's a start.

On Aug 18, 2006, at 3:26 PM, Benjamin Krajmalnik wrote:

> However, it appears that current_timestamp is not being recalculated.
> Any ideas? While normally we want the stored procedures to run as
> quickly as possible, this is a case where I need to insert a delay.
> I guess an option would be to place a counter and loop through it, but
> it does not allow me the control I relly need.

current_timestamp returns the same value for the scope of the
transaction (or function call). Search the archives for "sleep" for
previous discussions of this issue. You can use timeofday to get a
changing time value, but any looping you do in plpgsql will eat a lot
of CPU cycles. The only other solution I recall was to use a
different pl language like perl which has a proper sleep function.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Fri, Aug 18, 2006 at 03:50:02PM -0400, John DeSoi wrote:
> The only other solution I recall was to use a  different pl language
> like perl which has a proper sleep function.

That's what I've done.  8.2 will have a pg_sleep() function so
writing your own will no longer be necessary.

http://archives.postgresql.org/pgsql-committers/2006-01/msg00187.php

--
Michael Fuhr