SLEEP in posgresql

[Jasbinder Singh Bali]

Hi,

I have a while loop and I want to re-iterate after every 't' seconds.
I was reading up on the postgresql documentation that says pg_sleep(t)
should be handy.
However i doesn't work.

Instead of that, I re-engineered my while loop in the stored procedure as
follows.

while a=b loop
--do something
select pg_sleep(5);
end loop

I doubt this would work because when I try to run
SELECT pg_sleep(5) stand alone, it throws error.

I was wondering how to implement the SLEEP functionality here.

Thanks,
~Jas

[Guy Rouillier]

Post by Jasbinder Singh Bali
Hi,
I have a while loop and I want to re-iterate after every 't' seconds.
I was reading up on the postgresql documentation that says pg_sleep(t)
should be handy.
However i doesn't work.

Hmmm, I'm looking at section 9.9.5 Delaying Execution in the PostgreSQL
8.2.0 Documentation. Following the example presented there, I fired up
psql and ran the following:

postgres=# select current_timestamp; select pg_sleep(3); select
current_timestamp;
now
----------------------------
2007-10-09 23:50:32.649-04
(1 row)

pg_sleep
----------

(1 row)

now
----------------------------
2007-10-09 23:50:35.774-04
(1 row)

Seems to be working. What version are you using and on what platform?

[Jasbinder Singh Bali]

I'm using Postgresql Version 8.1.4. on fedora core 6
I'm pretty sure that pg_sleep is not implemented in 8.1.
Am not sure what is the work around

Jas

I think pg_sleep is not implemented in 8.1 and earlier versions. Is there
any alternative if someone is using versions before 8.2 ?

Post by Guy Rouillier

Post by Jasbinder Singh Bali
Hi,
I have a while loop and I want to re-iterate after every 't' seconds.
I was reading up on the postgresql documentation that says pg_sleep(t)
should be handy.
However i doesn't work.

Hmmm, I'm looking at section 9.9.5 Delaying Execution in the PostgreSQL
8.2.0 Documentation. Following the example presented there, I fired up
postgres=# select current_timestamp; select pg_sleep(3); select
current_timestamp;
now
----------------------------
2007-10-09 23:50:32.649-04
(1 row)
pg_sleep
----------
(1 row)
now
----------------------------
2007-10-09 23:50:35.774-04
(1 row)
Seems to be working. What version are you using and on what platform?
--
Guy Rouillier
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
message can get through to the mailing list cleanly

[Robert Treat]

Post by Jasbinder Singh Bali
I'm using Postgresql Version 8.1.4. on fedora core 6
I'm pretty sure that pg_sleep is not implemented in 8.1.
Am not sure what is the work around

Yeah can code your own sleep function in plpgsql, but it tends to be resource
intensive. Better would probably be to use an external lang, like this:

CREATE OR REPLACE FUNCTION otools.sleep(integer)
RETURNS integer
AS $$
my ($seconds) = @_; return sleep($seconds);
$$ LANGUAGE 'PLPERLU';

[Harpreet Dhaliwal]

I think pg_sleep is not implemented in 8.1 and earlier versions. Is there
any alternative if someone is using versions before 8.2 ?

Post by Guy Rouillier

Post by Jasbinder Singh Bali
Hi,
I have a while loop and I want to re-iterate after every 't' seconds.
I was reading up on the postgresql documentation that says pg_sleep(t)
should be handy.
However i doesn't work.

Hmmm, I'm looking at section 9.9.5 Delaying Execution in the PostgreSQL
8.2.0 Documentation. Following the example presented there, I fired up
postgres=# select current_timestamp; select pg_sleep(3); select
current_timestamp;
now
----------------------------
2007-10-09 23:50:32.649-04
(1 row)
pg_sleep
----------
(1 row)
now
----------------------------
2007-10-09 23:50:35.774-04
(1 row)
Seems to be working. What version are you using and on what platform?
--
Guy Rouillier
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
message can get through to the mailing list cleanly

[Jasbinder Singh Bali]

What if its just SLEEP for 1 second. Why would it keep my stored procedure
hanging ?

Post by Jasbinder Singh Bali
Instead of that, I re-engineered my while loop in the stored procedure
as follows.

...

Post by Jasbinder Singh Bali
I was wondering how to implement the SLEEP functionality here.

Hello.
I can't comment the function itself, but I want to bring something else
to your attention. Note, that the stored procedure is always run as a
single transaction and by doing the sleep in it, it will probbly run for
a long time, or maybe even forever. The problem is that "Long running
transactions are evil(tm)"
Postgres, and almost any real database engine for that matter, has
problems when there are tansactions that run for a very long time. It
prevents the cleanup of stale records, because the engine has to keep
them around for this long running transaction.
You might consider doing the actual work in the transaction, but the
sleeping in between shoud be done outside.
Is there any thought being given to have the PL/pgSQL scripting language
outside the function body? Like Ora has? It would be perfect for this
case and I remember more than a dozen times in last year when I could
have used it and saved some PHP work (and network communiaction).
--
Michal Táborský
chief systems architect
Internet Mall, a.s.
<http://www.MALL.cz>

[Richard Huxton]

Post by Jasbinder Singh Bali
What if its just SLEEP for 1 second. Why would it keep my stored procedure
hanging ?

Because presumably your loop-condition isn't under your control
(otherwise you wouldn't need to sleep).

Can you *always* guarantee the condition (a=b) will happen within a
reasonable time-frame?

[Jasbinder Singh Bali]

my loop is a busy wait and keeps iterating until a=b condition is met.
However, it would lead to millions of instructions executing per second.

So to save resources, I want to keep a sleep before re-iterating. Don't
understand how is SLEEP disastrous here even if i don't know when is my loop
going to end

Post by Jasbinder Singh Bali

Post by Jasbinder Singh Bali
What if its just SLEEP for 1 second. Why would it keep my stored

procedure

Post by Jasbinder Singh Bali
hanging ?

Because presumably your loop-condition isn't under your control
(otherwise you wouldn't need to sleep).
Can you *always* guarantee the condition (a=b) will happen within a
reasonable time-frame?
--
Richard Huxton
Archonet Ltd

[Richard Huxton]

Post by Jasbinder Singh Bali
my loop is a busy wait and keeps iterating until a=b condition is met.
However, it would lead to millions of instructions executing per second.
So to save resources, I want to keep a sleep before re-iterating. Don't
understand how is SLEEP disastrous here even if i don't know when is my loop
going to end

What if it takes a week?

That means you'll have a transaction open for a week blocking vacuum
from reclaiming space.

[Jasbinder Singh Bali]

I don't know how is a sleep of 1 second going to be harmful here instead of
keeping a busy wait.

Even if a=b is going to take 1 week as u say, isn't a sleep of 1 second, if
nothing, would save some CPU resources instead of blindly looping for ever ?
Aren't busy waits dangerous from CPU resources point of view ? Won't it keep
my CPU busy for ever. Also, if there's a sleep of 1 second, is it going to
give me some saving in CPU resources or not ?

Post by Richard Huxton

Post by Jasbinder Singh Bali
my loop is a busy wait and keeps iterating until a=b condition is met.
However, it would lead to millions of instructions executing per second.
So to save resources, I want to keep a sleep before re-iterating. Don't
understand how is SLEEP disastrous here even if i don't know when is my

loop

Post by Jasbinder Singh Bali
going to end

What if it takes a week?
That means you'll have a transaction open for a week blocking vacuum
from reclaiming space.
--
Richard Huxton
Archonet Ltd

[Richard Huxton]

Post by Jasbinder Singh Bali
I don't know how is a sleep of 1 second going to be harmful here instead of
keeping a busy wait.
Even if a=b is going to take 1 week as u say, isn't a sleep of 1 second, if
nothing, would save some CPU resources instead of blindly looping for ever ?
Aren't busy waits dangerous from CPU resources point of view ? Won't it keep
my CPU busy for ever. Also, if there's a sleep of 1 second, is it going to
give me some saving in CPU resources or not ?

It's not the sleep that people are saying is harmful. It's the waiting
in a loop in a transaction.

[Jasbinder Singh Bali]

I don't know how is a sleep of 1 second going to be harmful here instead of
keeping a busy wait.

Even if a=b is going to take 1 week as u say, isn't a sleep of 1 second, if
nothing, would save some CPU resources instead of blindly looping for ever ?
Aren't busy

Post by Richard Huxton

Post by Jasbinder Singh Bali
my loop is a busy wait and keeps iterating until a=b condition is met.
However, it would lead to millions of instructions executing per second.
So to save resources, I want to keep a sleep before re-iterating. Don't
understand how is SLEEP disastrous here even if i don't know when is my

loop

Post by Jasbinder Singh Bali
going to end

What if it takes a week?
That means you'll have a transaction open for a week blocking vacuum
from reclaiming space.
--
Richard Huxton
Archonet Ltd

[Michal Taborsky - Internet Mall]

Post by Jasbinder Singh Bali
Instead of that, I re-engineered my while loop in the stored procedure
as follows.

...

Post by Jasbinder Singh Bali
I was wondering how to implement the SLEEP functionality here.

Hello.

I can't comment the function itself, but I want to bring something else
to your attention. Note, that the stored procedure is always run as a
single transaction and by doing the sleep in it, it will probbly run for
a long time, or maybe even forever. The problem is that "Long running
transactions are evil(tm)"

Postgres, and almost any real database engine for that matter, has
problems when there are tansactions that run for a very long time. It
prevents the cleanup of stale records, because the engine has to keep
them around for this long running transaction.

You might consider doing the actual work in the transaction, but the
sleeping in between shoud be done outside.

Note to PG developers:
Is there any thought being given to have the PL/pgSQL scripting language
outside the function body? Like Ora has? It would be perfect for this
case and I remember more than a dozen times in last year when I could
have used it and saved some PHP work (and network communiaction).
--
Michal Táborský
chief systems architect
Internet Mall, a.s.
<http://www.MALL.cz>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/