pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Inserting a timed delay in a pl/pgsql procedure
3+ messages / 3 participants
[nested] [flat]

* Inserting a timed delay in a pl/pgsql procedure
@ 2006-08-18 19:26  Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Benjamin Krajmalnik @ 2006-08-18 19:26 UTC (permalink / raw)
  To: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Inserting a timed delay in a pl/pgsql procedure
@ 2006-08-18 19:50  John DeSoi <desoi@pgedit.com>
  parent: Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 1 reply; 3+ messages in thread

From: John DeSoi @ 2006-08-18 19:50 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Inserting a timed delay in a pl/pgsql procedure
@ 2006-08-18 20:28  Michael Fuhr <mike@fuhr.org>
  parent: John DeSoi <desoi@pgedit.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael Fuhr @ 2006-08-18 20:28 UTC (permalink / raw)
  To: John DeSoi <desoi@pgedit.com>; +Cc: Benjamin Krajmalnik <kraj@illumen.com>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-08-18 20:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-18 19:26 Inserting a timed delay in a pl/pgsql procedure Benjamin Krajmalnik <kraj@illumen.com>
2006-08-18 19:50 ` John DeSoi <desoi@pgedit.com>
2006-08-18 20:28   ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox