Re: [GENERAL] SLEEP in posgresql

[Robert Treat]

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

[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 ?

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

[Richard Huxton]

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

Re: [GENERAL] SLEEP in posgresql

[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

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]

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

[Richard Huxton]

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

[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

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

[Richard Huxton]

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

[Michal Taborsky - Internet Mall]

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.


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

  http://archives.postgresql.org/

Re: [GENERAL] SLEEP in posgresql

[Jasbinder Singh Bali]

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.
> 
>

Re: [GENERAL] SLEEP in posgresql

[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

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
> >
>
>

Re: [GENERAL] SLEEP in posgresql

[Guy Rouillier]

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