Postgres Pro
Downloads
Home   >   mailing lists

Thread: sleep?

sleep?

From
Don Drake
Date:
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

Re: sleep?

From
Guido Barosio
Date:
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.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.

Re: sleep?

From
Michael Fuhr
Date:
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

Re: sleep?

From
"Greg Sabino Mullane"
Date:
-----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-----

Re: sleep?

From
Bricklen Anderson
Date:
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)

Re: sleep?

From
John DeSoi
Date:
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;

Re: sleep?

From
Don Drake
Date:
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

Re: sleep?

From
Michael Fuhr
Date:
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

Re: sleep?

From
Bruce Momjian
Date:
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

Re: sleep?

From
"Goulet, Dick"
Date:
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

Re: sleep?

From
Bruce Momjian
Date:
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

Re: sleep?

From
Tom Lane
Date:
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

Re: sleep?

From
Don Drake
Date:
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

Re: sleep?

From
"Joshua D. Drake"
Date:
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

Re: sleep?

From
"Jim C. Nasby"
Date:
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