Re: sleep?

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

-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200508211601
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDCN3BvJuQZxSWSsgRAhtJAKD0QDRWb6kk+AGWHMPQqM7lwRAXKACgpHM0
EkA+7aOQoI3BzgLhkva53mQ=
=hD9m
-----END PGP SIGNATURE-----

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)

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(at)gmail(dot)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.

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

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;

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(at)pgedit(dot)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

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)fuhr(dot)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

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

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(at)fuhr(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461