pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsleep?
15+ messages / 11 participants
[nested] [flat]
* sleep?
@ 2005-08-21 19:22 Don Drake <dondrake@gmail.com>
0 siblings, 4 replies; 15+ messages in thread
From: Don Drake @ 2005-08-21 19:22 UTC (permalink / raw)
To: pgsql-admin
Is there a sleep function in plpgsql? I need to wait a period time (60
seconds) in a while loop.
I didn't see anything in the docs.
Thanks.
-Don
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
http://www.mobilemeridian.com/
312-560-1574
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-21 20:03 Greg Sabino Mullane <greg@turnstep.com>
parent: Don Drake <dondrake@gmail.com>
3 siblings, 0 replies; 15+ messages in thread
From: Greg Sabino Mullane @ 2005-08-21 20:03 UTC (permalink / raw)
To: pgsql-admin
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Is there a sleep function in plpgsql? I need to wait a period time
> 60 seconds) in a while loop.
You will need to us another language, such as plperl. You could
certainly create a sleep function and have your plpgsql call that:
CREATE FUNCTION sleep(INT) RETURNS TEXT LANGUAGE plperl AS
$$
select(undef,undef,undef,shift);
return "Time to wake up!";
$$;
SELECT sleep(2);
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200508211601
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDCN3BvJuQZxSWSsgRAhtJAKD0QDRWb6kk+AGWHMPQqM7lwRAXKACgpHM0
EkA+7aOQoI3BzgLhkva53mQ=
=hD9m
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-21 20:26 Bricklen Anderson <banderson@presinet.com>
parent: Don Drake <dondrake@gmail.com>
3 siblings, 0 replies; 15+ messages in thread
From: Bricklen Anderson @ 2005-08-21 20:26 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-admin
Don Drake wrote:
> Is there a sleep function in plpgsql? I need to wait a period time (60
> seconds) in a while loop.
>
> I didn't see anything in the docs.
>
> Thanks.
>
> -Don
>
create or replace function sleep(integer) returns void as $$
return sleep(shift)
$$ language plperlu immutable strict;
select sleep(5);
--(sleeps for 5 seconds)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-21 21:09 Guido Barosio <gbarosio@gmail.com>
parent: Don Drake <dondrake@gmail.com>
3 siblings, 1 reply; 15+ messages in thread
From: Guido Barosio @ 2005-08-21 21:09 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-admin
Don't know about such thing, but I guess that a plpgsql function could help
using datetime var's.
Just a guess, don't know if the optimal solution.
Best wishes,
G.-
On 8/21/05, Don Drake <dondrake@gmail.com> wrote:
>
> Is there a sleep function in plpgsql? I need to wait a period time (60
> seconds) in a while loop.
>
> I didn't see anything in the docs.
>
> Thanks.
>
> -Don
>
> --
> Donald Drake
> President
> Drake Consulting
> http://www.drakeconsult.com/
> http://www.MailLaunder.com/ <http://www.maillaunder.com/;
> http://www.mobilemeridian.com/
> 312-560-1574
>
>
--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-21 22:41 Michael Fuhr <mike@fuhr.org>
parent: Don Drake <dondrake@gmail.com>
3 siblings, 0 replies; 15+ messages in thread
From: Michael Fuhr @ 2005-08-21 22:41 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: pgsql-admin
On Sun, Aug 21, 2005 at 02:22:25PM -0500, Don Drake wrote:
> Is there a sleep function in plpgsql? I need to wait a period time (60
> seconds) in a while loop.
SELECT oid::regprocedure
FROM pg_proc
WHERE proname ILIKE '%sleep%' OR prosrc ILIKE '%sleep%';
oid
-----
(0 rows)
When I need a sleep function on the server side I write one in C,
PL/Perl, PL/Tcl, PL/Python, etc. PostgreSQL 8.0 and later have an
internal pg_usleep() function but it's not exposed to the user. I
asked about exposing it recently but didn't see any replies (it was
in response to a message in pgsql-committers; I suppose I should
have asked in pgsql-hackers instead).
--
Michael Fuhr
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-22 01:43 John DeSoi <desoi@pgedit.com>
parent: Guido Barosio <gbarosio@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: John DeSoi @ 2005-08-22 01:43 UTC (permalink / raw)
To: Guido Barosio <gbarosio@gmail.com>; +Cc: Don Drake <dondrake@gmail.com>; pgsql-admin
On Aug 21, 2005, at 5:09 PM, Guido Barosio wrote:
> Don't know about such thing, but I guess that a plpgsql function
> could help using datetime var's.
>
> Just a guess, don't know if the optimal solution.
Yes, you can write one in plpgsql using timeofday, but it is horribly
inefficient wastes CPU cycles busy looping. I use the version below
for some simple testing, but it is not useful for a production
system. It would be nice to have a sleep function that does not
require anything beyond plpgsql.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
create or replace function sleep (integer) returns time as '
declare
seconds alias for $1;
later time;
thetime time;
begin
thetime := timeofday()::timestamp;
later := thetime + (seconds::text || '' seconds'')::interval;
loop
if thetime >= later then
exit;
else
thetime := timeofday()::timestamp;
end if;
end loop;
return later;
end;
' language plpgsql;
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-22 13:34 Don Drake <dondrake@gmail.com>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 1 reply; 15+ messages in thread
From: Don Drake @ 2005-08-22 13:34 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: Guido Barosio <gbarosio@gmail.com>; pgsql-admin
Thanks for all of the replies. I recompiled my DB with plperlu and am using
perl's sleep function.
I agree that a basic function (non-CPU intensive sleep) like this should be
built in.
Thanks again.
-Don
On 8/21/05, John DeSoi <desoi@pgedit.com> wrote:
>
>
> On Aug 21, 2005, at 5:09 PM, Guido Barosio wrote:
>
> > Don't know about such thing, but I guess that a plpgsql function
> > could help using datetime var's.
> >
> > Just a guess, don't know if the optimal solution.
>
>
> Yes, you can write one in plpgsql using timeofday, but it is horribly
> inefficient wastes CPU cycles busy looping. I use the version below
> for some simple testing, but it is not useful for a production
> system. It would be nice to have a sleep function that does not
> require anything beyond plpgsql.
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
>
> create or replace function sleep (integer) returns time as '
> declare
> seconds alias for $1;
> later time;
> thetime time;
> begin
> thetime := timeofday()::timestamp;
> later := thetime + (seconds::text || '' seconds'')::interval;
> loop
> if thetime >= later then
> exit;
> else
> thetime := timeofday()::timestamp;
> end if;
> end loop;
>
> return later;
> end;
> ' language plpgsql;
>
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
http://www.mobilemeridian.com/
312-560-1574
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-22 14:01 Michael Fuhr <mike@fuhr.org>
parent: Don Drake <dondrake@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Michael Fuhr @ 2005-08-22 14:01 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
On Mon, Aug 22, 2005 at 08:34:29AM -0500, Don Drake wrote:
> I agree that a basic function (non-CPU intensive sleep) like this should be
> built in.
It's being discussed in pgsql-hackers:
http://archives.postgresql.org/pgsql-hackers/2005-08/msg00633.php
Do you have any use cases in addition to what's already been
mentioned? Sleeping isn't really a database operation, so there
needs to be some justification for making it a standard function.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-22 14:24 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Michael Fuhr <mike@fuhr.org>
1 sibling, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2005-08-22 14:24 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Don Drake <dondrake@gmail.com>; John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
Michael Fuhr wrote:
> On Mon, Aug 22, 2005 at 08:34:29AM -0500, Don Drake wrote:
> > I agree that a basic function (non-CPU intensive sleep) like this should be
> > built in.
>
> It's being discussed in pgsql-hackers:
>
> http://archives.postgresql.org/pgsql-hackers/2005-08/msg00633.php
>
> Do you have any use cases in addition to what's already been
> mentioned? Sleeping isn't really a database operation, so there
> needs to be some justification for making it a standard function.
Well, we needed it for our own regression tests, so I assume others
would need it as well.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-22 15:06 Goulet, Dick <DGoulet@vicr.com>
0 siblings, 0 replies; 15+ messages in thread
From: Goulet, Dick @ 2005-08-22 15:06 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Michael Fuhr <mike@fuhr.org>; +Cc: Don Drake <dondrake@gmail.com>; John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
Bruce,
Agree that pausing or sleeping is not really a database
function, more of a programming function. But Oracle and I understand
though I can't find it at the moment SQL*Server do offer this
functionality within their DB's just because people do store functions
and procedures that are executed as internal database jobs and
consequently need this functionality. Not sure about DB2, been too
long, but I seem to remember it being there for other reasons though.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bruce Momjian
Sent: Monday, August 22, 2005 10:25 AM
To: Michael Fuhr
Cc: Don Drake; John DeSoi; Guido Barosio; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] sleep?
Michael Fuhr wrote:
> On Mon, Aug 22, 2005 at 08:34:29AM -0500, Don Drake wrote:
> > I agree that a basic function (non-CPU intensive sleep) like this
should be
> > built in.
>
> It's being discussed in pgsql-hackers:
>
> http://archives.postgresql.org/pgsql-hackers/2005-08/msg00633.php
>
> Do you have any use cases in addition to what's already been
> mentioned? Sleeping isn't really a database operation, so there
> needs to be some justification for making it a standard function.
Well, we needed it for our own regression tests, so I assume others
would need it as well.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania
19073
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-22 15:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2005-08-22 15:56 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Michael Fuhr <mike@fuhr.org>; Don Drake <dondrake@gmail.com>; John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Michael Fuhr wrote:
>> Do you have any use cases in addition to what's already been
>> mentioned? Sleeping isn't really a database operation, so there
>> needs to be some justification for making it a standard function.
> Well, we needed it for our own regression tests, so I assume others
> would need it as well.
... and you'll notice that the regression test that uses it is still
failing intermittently on some of the buildfarm machines, which rather
calls the whole approach into question ...
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-22 16:24 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2005-08-22 16:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Fuhr <mike@fuhr.org>; Don Drake <dondrake@gmail.com>; John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Michael Fuhr wrote:
> >> Do you have any use cases in addition to what's already been
> >> mentioned? Sleeping isn't really a database operation, so there
> >> needs to be some justification for making it a standard function.
>
> > Well, we needed it for our own regression tests, so I assume others
> > would need it as well.
>
> ... and you'll notice that the regression test that uses it is still
> failing intermittently on some of the buildfarm machines, which rather
> calls the whole approach into question ...
It means that our use of sleep() wasn't the fix, but we need it to try,
didn't we?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-23 14:42 Don Drake <dondrake@gmail.com>
parent: Michael Fuhr <mike@fuhr.org>
1 sibling, 2 replies; 15+ messages in thread
From: Don Drake @ 2005-08-23 14:42 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
Sorry for the delay in replying.
The use-case that I have is the following. I'm writing a job-control
tracking sub-system that will store when jobs are started, finished, failed,
etc. I would like to have the ability to have a process that is requesting
to start, to actually wait a specified period time before starting. It could
wait for another job to finish. I'm writing this in plpgsql since I'm
storing status in the db.
-Don
On 8/22/05, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Mon, Aug 22, 2005 at 08:34:29AM -0500, Don Drake wrote:
> > I agree that a basic function (non-CPU intensive sleep) like this should
> be
> > built in.
>
> It's being discussed in pgsql-hackers:
>
> http://archives.postgresql.org/pgsql-hackers/2005-08/msg00633.php
>
> Do you have any use cases in addition to what's already been
> mentioned? Sleeping isn't really a database operation, so there
> needs to be some justification for making it a standard function.
>
> --
> Michael Fuhr
>
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
http://www.mobilemeridian.com/
312-560-1574
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-23 15:07 Joshua D. Drake <jd@commandprompt.com>
parent: Don Drake <dondrake@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Joshua D. Drake @ 2005-08-23 15:07 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
Don Drake wrote:
> Sorry for the delay in replying.
>
> The use-case that I have is the following. I'm writing a job-control
> tracking sub-system that will store when jobs are started, finished,
> failed, etc. I would like to have the ability to have a process that
> is requesting to start, to actually wait a specified period time
> before starting. It could wait for another job to finish. I'm
> writing this in plpgsql since I'm storing status in the db.
I would write a userspace daemon that ran outside of PostgreSQL to call
your procedures as you need them to run.
Sincerely,
Joshua D. Drake
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: sleep?
@ 2005-08-23 18:34 Jim C. Nasby <jnasby@pervasive.com>
parent: Don Drake <dondrake@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Jim C. Nasby @ 2005-08-23 18:34 UTC (permalink / raw)
To: Don Drake <dondrake@gmail.com>; +Cc: Michael Fuhr <mike@fuhr.org>; John DeSoi <desoi@pgedit.com>; Guido Barosio <gbarosio@gmail.com>; pgsql-admin
On a related note, you might be interested in
http://pgfoundry.org/projects/pgjob/
On Tue, Aug 23, 2005 at 09:42:27AM -0500, Don Drake wrote:
> Sorry for the delay in replying.
>
> The use-case that I have is the following. I'm writing a job-control
> tracking sub-system that will store when jobs are started, finished, failed,
> etc. I would like to have the ability to have a process that is requesting
> to start, to actually wait a specified period time before starting. It could
> wait for another job to finish. I'm writing this in plpgsql since I'm
> storing status in the db.
>
> -Don
>
> On 8/22/05, Michael Fuhr <mike@fuhr.org> wrote:
> >
> > On Mon, Aug 22, 2005 at 08:34:29AM -0500, Don Drake wrote:
> > > I agree that a basic function (non-CPU intensive sleep) like this should
> > be
> > > built in.
> >
> > It's being discussed in pgsql-hackers:
> >
> > http://archives.postgresql.org/pgsql-hackers/2005-08/msg00633.php
> >
> > Do you have any use cases in addition to what's already been
> > mentioned? Sleeping isn't really a database operation, so there
> > needs to be some justification for making it a standard function.
> >
> > --
> > Michael Fuhr
> >
>
>
>
> --
> Donald Drake
> President
> Drake Consulting
> http://www.drakeconsult.com/
> http://www.MailLaunder.com/
> http://www.mobilemeridian.com/
> 312-560-1574
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2005-08-23 18:34 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-21 19:22 sleep? Don Drake <dondrake@gmail.com>
2005-08-21 20:03 ` Greg Sabino Mullane <greg@turnstep.com>
2005-08-21 20:26 ` Bricklen Anderson <banderson@presinet.com>
2005-08-21 21:09 ` Guido Barosio <gbarosio@gmail.com>
2005-08-22 01:43 ` John DeSoi <desoi@pgedit.com>
2005-08-22 13:34 ` Don Drake <dondrake@gmail.com>
2005-08-22 14:01 ` Michael Fuhr <mike@fuhr.org>
2005-08-22 14:24 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-08-22 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-22 16:24 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-08-23 14:42 ` Don Drake <dondrake@gmail.com>
2005-08-23 15:07 ` Joshua D. Drake <jd@commandprompt.com>
2005-08-23 18:34 ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-21 22:41 ` Michael Fuhr <mike@fuhr.org>
2005-08-22 15:06 Re: sleep? Goulet, Dick <DGoulet@vicr.com>
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