Обсуждение: SLEEP in posgresql
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
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
Jasbinder Singh Bali wrote:
> 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?
--
Guy Rouillier
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'm pretty sure that pg_sleep is not implemented in 8.1.
Am not sure what is the work around
Jas
On 10/10/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:
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 ?On 10/9/07, Guy Rouillier < guyr-ml1@burntmail.com > wrote:Jasbinder Singh Bali wrote:
> 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?
--
Guy Rouillier
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
What if its just SLEEP for 1 second. Why would it keep my stored procedure hanging ?
On 10/10/07, Michal Taborsky - Internet Mall < michal.taborsky@mall.cz> wrote:
Jasbinder Singh Bali napsal(a):
> Instead of that, I re-engineered my while loop in the stored procedure
> as follows.
...
> 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>
Jasbinder Singh Bali napsal(a): > Instead of that, I re-engineered my while loop in the stored procedure > as follows. ... > 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>
Jasbinder Singh Bali wrote: > 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? -- Richard Huxton Archonet Ltd
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
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
On 10/10/07, Richard Huxton <dev@archonet.com> wrote:
Jasbinder Singh Bali wrote:
> 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?
--
Richard Huxton
Archonet Ltd
Jasbinder Singh Bali wrote: > 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. -- Richard Huxton Archonet Ltd
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
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
On 10/10/07, Richard Huxton <dev@archonet.com> wrote:
Jasbinder Singh Bali wrote:
> 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.
--
Richard Huxton
Archonet Ltd
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 ?
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 ?
On 10/10/07, Richard Huxton <dev@archonet.com> wrote:
Jasbinder Singh Bali wrote:
> 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.
--
Richard Huxton
Archonet Ltd
Jasbinder Singh Bali wrote: > 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. -- Richard Huxton Archonet Ltd
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 ?
On 10/9/07, Guy Rouillier <guyr-ml1@burntmail.com> wrote:
Jasbinder Singh Bali wrote:
> 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?
--
Guy Rouillier
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Wednesday 10 October 2007 01:00, Jasbinder Singh Bali wrote: > 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'; -- Robert Treat Database Architect http://www.omniti.com