Re: [GENERAL] SLEEP in posgresql
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SLEEP in posgresql
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SLEEP in posgresql
What if its just SLEEP for 1 second. Why would it keep my stored procedure hanging ? On 10/10/07, Michal Taborsky - Internet Mall [EMAIL PROTECTED] 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
Re: [GENERAL] SLEEP in posgresql
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SLEEP in posgresql
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SLEEP in posgresql
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 On 10/10/07, Richard Huxton [EMAIL PROTECTED] 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
Re: [GENERAL] SLEEP in posgresql
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SLEEP in posgresql
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 On 10/10/07, Richard Huxton [EMAIL PROTECTED] 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
Re: [GENERAL] SLEEP in posgresql
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 ? On 10/10/07, Richard Huxton [EMAIL PROTECTED] 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
Re: [GENERAL] SLEEP in posgresql
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 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
Re: [GENERAL] SLEEP in posgresql
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SLEEP in posgresql
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 On 10/10/07, Harpreet Dhaliwal [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
