pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedInserting 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>
2006-08-18 19:50 ` Re: Inserting a timed delay in a pl/pgsql procedure John DeSoi <desoi@pgedit.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: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 ` Re: Inserting a timed delay in a pl/pgsql procedure Michael Fuhr <mike@fuhr.org>
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 19:26 Inserting a timed delay in a pl/pgsql procedure Benjamin Krajmalnik <kraj@illumen.com>
2006-08-18 19:50 ` Re: Inserting a timed delay in a pl/pgsql procedure John DeSoi <desoi@pgedit.com>
@ 2006-08-18 20:28 ` Michael Fuhr <mike@fuhr.org>
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