Thread: Sleep functions

Sleep functions

From
Michael Fuhr
Date:
What do people think of exposing pg_usleep() to the user?  It's
sometimes useful to have a server-side sleep function, and people
do ask about it occasionally (e.g., Don Drake today in pgsql-admin).
It's easy enough to do in PL/Perl, PL/Tcl, etc., but since the
backend already has pg_usleep(), is there any reason not to expose
it?  I'd propose both sleep() and usleep() functions.

-- 
Michael Fuhr


Re: Sleep functions

From
Andrew Dunstan
Date:

Michael Fuhr wrote:

>What do people think of exposing pg_usleep() to the user?  It's
>sometimes useful to have a server-side sleep function, and people
>do ask about it occasionally (e.g., Don Drake today in pgsql-admin).
>It's easy enough to do in PL/Perl, PL/Tcl, etc., but since the
>backend already has pg_usleep(), is there any reason not to expose
>it?  I'd propose both sleep() and usleep() functions.
>  
>


Be careful with documentation - IIRC on Windows the granularity of the 
Sleep() function is 1 millisec and pg_usleep rounds to the nearest 
millisec except for very low values where it rounds up.

cheers

andrew


Re: Sleep functions

From
"Dave Page"
Date:
-----Original Message-----
From: "Michael Fuhr"<mike@fuhr.org>
Sent: 21/08/05 23:53:50
To: "pgsql-hackers@postgresql.org"<pgsql-hackers@postgresql.org>
Subject: [HACKERS] Sleep functions

> What do people think of exposing
> pg_usleep() to the user?  

Good idea - I've done so myself in the past for testing.

Regards, Dave

-----Unmodified Original Message-----
What do people think of exposing pg_usleep() to the user?  It's
sometimes useful to have a server-side sleep function, and people
do ask about it occasionally (e.g., Don Drake today in pgsql-admin).
It's easy enough to do in PL/Perl, PL/Tcl, etc., but since the
backend already has pg_usleep(), is there any reason not to expose
it?  I'd propose both sleep() and usleep() functions.

-- 
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to      choose an index scan if your joining column's
datatypesdo not      match
 


Re: Sleep functions

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> What do people think of exposing pg_usleep() to the user?

I'm not real enthused about it.  Generally speaking, a sleep() on the
database side means you are idling while holding locks, and that does
not seem like something we want to encourage people to do.

As other responders noted, it's trivial to program this in any of the
untrusted PL languages,  So what you're really proposing is that we give
sleep() to non-superusers, and that seems like a bit of a hard sell.
Let's see a use-case or three.
        regards, tom lane


Re: Sleep functions

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> As other responders noted, it's trivial to program this in any of the
> untrusted PL languages,

Or in (trusted) plperl - see my post on admin.

I would have been a big fan of a sleep function once, for use in plpgsql,
but since I now have perlperl[u], I don't need it any more. :)

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200508212134
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDCSwOvJuQZxSWSsgRAhzAAJ9mXYOWLyhB7/IICDKk3avZkYstQACg3FNm
1a8uSRArDhytzdbHIceQGFc=
=Tc9E
-----END PGP SIGNATURE-----




Re: Sleep functions

From
Michael Fuhr
Date:
On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > What do people think of exposing pg_usleep() to the user?
> 
> I'm not real enthused about it.  Generally speaking, a sleep() on the
> database side means you are idling while holding locks, and that does
> not seem like something we want to encourage people to do.

I don't see how providing a server-side sleep() encourages idling
while holding locks any more than people can already do.  In that
respect, is it any different than a client-side sleep() or going
to lunch with an open transaction in psql?

> As other responders noted, it's trivial to program this in any of the
> untrusted PL languages,  So what you're really proposing is that we give
> sleep() to non-superusers, and that seems like a bit of a hard sell.
> Let's see a use-case or three.

Sure it's trivial in various languages, even in trusted PL/Tcl:

CREATE FUNCTION sleep(integer) RETURNS void AS $$
after [expr $1 * 1000]
$$ LANGUAGE pltcl STRICT;

So aside from the ways to idle I mentioned above, non-superusers
do have a way to perform a server-side sleep(), at least on systems
that use PL/Tcl.  Or is allowing "after" a bug in trusted PL/Tcl?
In any case, I wonder how many people, not having a sleep() function,
effect a delay with a busy loop; an example of such has been posted
in response to the thread in pgsql-admin, and didn't the regression
tests do so until recently?  That seems less desirable than a real
sleep().

A few use cases are learning, testing, and debugging: you might
want to slow down operations so you can more easily watch what's
happening, observe how the slowness affects other operations, or
look for application problems related to timing.  With a server-side
sleep() those delays can be done with simple queries fed into psql
or another interface that doesn't provide a way to sleep, and a
client-side sleep() wouldn't help if you want to slow down operations
inside a PL/pgSQL function.

To others who've written their own sleep() function: what are you
using it for?

-- 
Michael Fuhr


Re: Sleep functions

From
"Dave Page"
Date:
-----Original Message-----
From: "Michael Fuhr"<mike@fuhr.org>
Sent: 22/08/05 05:41:50
To: "Tom Lane"<tgl@sss.pgh.pa.us>
Cc: "pgsql-hackers@postgresql.org"<pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Sleep functions

> To others who've written their own sleep() > function: what are you
> using it for?

LOL - I honestly don't recall now!! I was certainly testing something I needed to slow down.

/D

-----Unmodified Original Message-----
On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > What do people think of exposing pg_usleep() to the user?
>
> I'm not real enthused about it.  Generally speaking, a sleep() on the
> database side means you are idling while holding locks, and that does
> not seem like something we want to encourage people to do.

I don't see how providing a server-side sleep() encourages idling
while holding locks any more than people can already do.  In that
respect, is it any different than a client-side sleep() or going
to lunch with an open transaction in psql?

> As other responders noted, it's trivial to program this in any of the
> untrusted PL languages,  So what you're really proposing is that we give
> sleep() to non-superusers, and that seems like a bit of a hard sell.
> Let's see a use-case or three.

Sure it's trivial in various languages, even in trusted PL/Tcl:

CREATE FUNCTION sleep(integer) RETURNS void AS $$
after [expr $1 * 1000]
$$ LANGUAGE pltcl STRICT;

So aside from the ways to idle I mentioned above, non-superusers
do have a way to perform a server-side sleep(), at least on systems
that use PL/Tcl.  Or is allowing "after" a bug in trusted PL/Tcl?
In any case, I wonder how many people, not having a sleep() function,
effect a delay with a busy loop; an example of such has been posted
in response to the thread in pgsql-admin, and didn't the regression
tests do so until recently?  That seems less desirable than a real
sleep().

A few use cases are learning, testing, and debugging: you might
want to slow down operations so you can more easily watch what's
happening, observe how the slowness affects other operations, or
look for application problems related to timing.  With a server-side
sleep() those delays can be done with simple queries fed into psql
or another interface that doesn't provide a way to sleep, and a
client-side sleep() wouldn't help if you want to slow down operations
inside a PL/pgSQL function.

To others who've written their own sleep() function: what are you
using it for?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org


Re: Sleep functions

From
John DeSoi
Date:
On Aug 22, 2005, at 12:40 AM, Michael Fuhr wrote:

> To others who've written their own sleep() function: what are you
> using it for?


I need it for API and user interface testing. I want to be sure  
things behave correctly when a long running query is interrupted.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL



Re: Sleep functions

From
Michael Adler
Date:
On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > What do people think of exposing pg_usleep() to the user?
> 
> I'm not real enthused about it.  Generally speaking, a sleep() on the
> database side means you are idling while holding locks, and that does
> not seem like something we want to encourage people to do.
> 
> As other responders noted, it's trivial to program this in any of the
> untrusted PL languages,  So what you're really proposing is that we give
> sleep() to non-superusers, and that seems like a bit of a hard sell.
> Let's see a use-case or three.

There may be a better alternative, but wouldn't this let application
writers easily test the effects of a long running transaction?
-Mike


Re: Sleep functions

From
Bruce Momjian
Date:
Michael Adler wrote:
> On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote:
> > Michael Fuhr <mike@fuhr.org> writes:
> > > What do people think of exposing pg_usleep() to the user?
> > 
> > I'm not real enthused about it.  Generally speaking, a sleep() on the
> > database side means you are idling while holding locks, and that does
> > not seem like something we want to encourage people to do.
> > 
> > As other responders noted, it's trivial to program this in any of the
> > untrusted PL languages,  So what you're really proposing is that we give
> > sleep() to non-superusers, and that seems like a bit of a hard sell.
> > Let's see a use-case or three.
> 
> There may be a better alternative, but wouldn't this let application
> writers easily test the effects of a long running transaction?

What is wrong with giving non-super-users sleep() access?  It is a
natural part of almost every programming language. 

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


Re: Sleep functions

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> In any case, I wonder how many people, not having a sleep() function,
> effect a delay with a busy loop; an example of such has been posted
> in response to the thread in pgsql-admin, and didn't the regression
> tests do so until recently?  That seems less desirable than a real
> sleep().

Exactly. If people are going to do it anyways, we might as well provide
a better interface. And sleep() is going to be less expensive than
any home-brewed sleep simulation.

> To others who've written their own sleep() function: what are you
> using it for?

To properly emulate other, slower, RDBMSs? :)

To simulate long-running queries without actually invoking them, and
to simulate concurrency of shorter queries.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200508221055
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkMJ5/QACgkQvJuQZxSWSsi7DwCfcVKyclkFZ5swp/jBvrT3lwq8
P4cAnRh/1WwCAwM/v2bItKKJBMD8ARNT
=itDC
-----END PGP SIGNATURE-----




Re: Sleep functions

From
Greg Stark
Date:
"Dave Page" <dpage@vale-housing.co.uk> writes:

> Sure it's trivial in various languages, even in trusted PL/Tcl:
> 
> CREATE FUNCTION sleep(integer) RETURNS void AS $$
> after [expr $1 * 1000]
> $$ LANGUAGE pltcl STRICT;

Do any of the trusted languages count branches and abort after some large
number to prevent trusted code from causing long delays?

-- 
greg



Re: Sleep functions

From
Robert Treat
Date:
On Mon, 2005-08-22 at 08:14, John DeSoi wrote:
> 
> On Aug 22, 2005, at 12:40 AM, Michael Fuhr wrote:
> 
> > To others who've written their own sleep() function: what are you
> > using it for?
> 
> 
> I need it for API and user interface testing. I want to be sure  
> things behave correctly when a long running query is interrupted.
> 
> 

I know I've used one for a script that reindexes various tables on an
old 7.3 server. I put a sleep of 20 seconds between reindexes to let
built up transactions have a few moments to catch up, thereby smoothing
out i/o. For a long time I used a cpu hogging plpgsql version (since I
had cpu to spare) until I switched to a better pltcl version. If a
server side one existed I would certainly have used that. 


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



Re: Sleep functions

From
Andrew Dunstan
Date:

Greg Stark wrote:

>"Dave Page" <dpage@vale-housing.co.uk> writes:
>
>  
>
>>Sure it's trivial in various languages, even in trusted PL/Tcl:
>>
>>CREATE FUNCTION sleep(integer) RETURNS void AS $$
>>after [expr $1 * 1000]
>>$$ LANGUAGE pltcl STRICT;
>>    
>>
>
>Do any of the trusted languages count branches and abort after some large
>number to prevent trusted code from causing long delays?
>
>  
>

I don't believe so. What arbitrary limit would you like to set?

The thing that bothers me slightly about doing this in PLs is that it 
might well monkey with the signal handling which could cause very hard 
to debug problems. But maybe that's just paranoia - I haven't looked at 
it closely.

cheers

andrew


Re: Sleep functions

From
Tom Lane
Date:
Robert Treat <xzilla@users.sourceforge.net> writes:
>> On Aug 22, 2005, at 12:40 AM, Michael Fuhr wrote:
>>> To others who've written their own sleep() function: what are you
>>> using it for?

> I know I've used one for a script that reindexes various tables on an
> old 7.3 server. I put a sleep of 20 seconds between reindexes to let
> built up transactions have a few moments to catch up, thereby smoothing
> out i/o. For a long time I used a cpu hogging plpgsql version (since I
> had cpu to spare) until I switched to a better pltcl version. If a
> server side one existed I would certainly have used that. 

Tell you the truth, this "use case" qualifies as a poster child for my
concern that a server-side sleep would encourage people to write code
that sits on locks.  If you'd coded some kind of plpgsql loop that did
a REINDEX, sleep N seconds, another REINDEX, etc, you'd have been
sitting on the exclusive lock for each table until the end of the whole
transaction.  Your approach makes lots of sense if you commit each
REINDEX transaction and sleep *outside* the transaction --- but a server
sleep function would do exactly not that.
        regards, tom lane


Re: Sleep functions

From
Robert Treat
Date:
On Monday 22 August 2005 11:53, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> >> On Aug 22, 2005, at 12:40 AM, Michael Fuhr wrote:
> >>> To others who've written their own sleep() function: what are you
> >>> using it for?
> >
> > I know I've used one for a script that reindexes various tables on an
> > old 7.3 server. I put a sleep of 20 seconds between reindexes to let
> > built up transactions have a few moments to catch up, thereby smoothing
> > out i/o. For a long time I used a cpu hogging plpgsql version (since I
> > had cpu to spare) until I switched to a better pltcl version. If a
> > server side one existed I would certainly have used that.
>
> Tell you the truth, this "use case" qualifies as a poster child for my
> concern that a server-side sleep would encourage people to write code
> that sits on locks.  If you'd coded some kind of plpgsql loop that did
> a REINDEX, sleep N seconds, another REINDEX, etc, you'd have been
> sitting on the exclusive lock for each table until the end of the whole
> transaction.  Your approach makes lots of sense if you commit each
> REINDEX transaction and sleep *outside* the transaction --- but a server
> sleep function would do exactly not that.
>

Note that, as I stated,  this was used in a "script", not a plpgsql function.  
Each reindex was committed in a separate transaction, and the sleeps were 
selected between transactions.  I could have done the sleeps outside of the 
database, but doing it inside allowed me to cut down on the number of 
connections (which was critical) and also allowed me to play with settings 
(work_mem for instance) on a single connection.  

IMHO not having a sleep function doesn't prevent what you are worried about, 
it just causes people to do what I did, writing up thier own crappy models 
that starve locks _and_ cpu.  Again server-side sleep is not something I 
need, it's just something I needed. 

Incidentally I have also used the sleep function to help test concurrency 
issues in some situation, where I needed to slow the transactions down enough 
to verify what was going on.   

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: Sleep functions

From
"Jim C. Nasby"
Date:
On Mon, Aug 22, 2005 at 11:53:22AM -0400, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> >> On Aug 22, 2005, at 12:40 AM, Michael Fuhr wrote:
> >>> To others who've written their own sleep() function: what are you
> >>> using it for?
> 
> > I know I've used one for a script that reindexes various tables on an
> > old 7.3 server. I put a sleep of 20 seconds between reindexes to let
> > built up transactions have a few moments to catch up, thereby smoothing
> > out i/o. For a long time I used a cpu hogging plpgsql version (since I
> > had cpu to spare) until I switched to a better pltcl version. If a
> > server side one existed I would certainly have used that. 
> 
> Tell you the truth, this "use case" qualifies as a poster child for my
> concern that a server-side sleep would encourage people to write code
> that sits on locks.  If you'd coded some kind of plpgsql loop that did
> a REINDEX, sleep N seconds, another REINDEX, etc, you'd have been
> sitting on the exclusive lock for each table until the end of the whole
> transaction.  Your approach makes lots of sense if you commit each
> REINDEX transaction and sleep *outside* the transaction --- but a server
> sleep function would do exactly not that.

ISTM that when someone thinks they need a sleep in the database they'll
go to the docs to see if such a capability exists. When they find out it
does, hopefully they'll read far enough to see the nice big warning
we'll put in there about it being a foot-gun. Then they'll be better
informed about if they should actually be doing what they're thinking
about doing.

Right now, they see the function is missing and just cobble something
else together, possibly without regard to the side-effects.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461


Re: Sleep functions

From
Bruce Momjian
Date:
Added to TODO:
       o Add sleep() to PL/PgSQL


---------------------------------------------------------------------------

Robert Treat wrote:
> On Monday 22 August 2005 11:53, Tom Lane wrote:
> > Robert Treat <xzilla@users.sourceforge.net> writes:
> > >> On Aug 22, 2005, at 12:40 AM, Michael Fuhr wrote:
> > >>> To others who've written their own sleep() function: what are you
> > >>> using it for?
> > >
> > > I know I've used one for a script that reindexes various tables on an
> > > old 7.3 server. I put a sleep of 20 seconds between reindexes to let
> > > built up transactions have a few moments to catch up, thereby smoothing
> > > out i/o. For a long time I used a cpu hogging plpgsql version (since I
> > > had cpu to spare) until I switched to a better pltcl version. If a
> > > server side one existed I would certainly have used that.
> >
> > Tell you the truth, this "use case" qualifies as a poster child for my
> > concern that a server-side sleep would encourage people to write code
> > that sits on locks.  If you'd coded some kind of plpgsql loop that did
> > a REINDEX, sleep N seconds, another REINDEX, etc, you'd have been
> > sitting on the exclusive lock for each table until the end of the whole
> > transaction.  Your approach makes lots of sense if you commit each
> > REINDEX transaction and sleep *outside* the transaction --- but a server
> > sleep function would do exactly not that.
> >
> 
> Note that, as I stated,  this was used in a "script", not a plpgsql function.  
> Each reindex was committed in a separate transaction, and the sleeps were 
> selected between transactions.  I could have done the sleeps outside of the 
> database, but doing it inside allowed me to cut down on the number of 
> connections (which was critical) and also allowed me to play with settings 
> (work_mem for instance) on a single connection.  
> 
> IMHO not having a sleep function doesn't prevent what you are worried about, 
> it just causes people to do what I did, writing up thier own crappy models 
> that starve locks _and_ cpu.  Again server-side sleep is not something I 
> need, it's just something I needed. 
> 
> Incidentally I have also used the sleep function to help test concurrency 
> issues in some situation, where I needed to slow the transactions down enough 
> to verify what was going on.   
> 
> -- 
> Robert Treat
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

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


Re: Sleep functions

From
Bruce Momjian
Date:
Michael Fuhr wrote:
> On Wed, Aug 24, 2005 at 12:49:57PM -0400, Bruce Momjian wrote:
> > 
> > Added to TODO:
> > 
> >         o Add sleep() to PL/PgSQL
> 
> Just to PL/pgSQL?  If we're going to add it (which doesn't seem to
> be decided yet), why not as an ordinary function that could be
> called from SQL as well?

Good point.  TODO modified.

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


Re: Sleep functions

From
Michael Fuhr
Date:
On Wed, Aug 24, 2005 at 12:49:57PM -0400, Bruce Momjian wrote:
> 
> Added to TODO:
> 
>         o Add sleep() to PL/PgSQL

Just to PL/pgSQL?  If we're going to add it (which doesn't seem to
be decided yet), why not as an ordinary function that could be
called from SQL as well?

-- 
Michael Fuhr