Sleep functions

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

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

Michael Fuhr <mike(at)fuhr(dot)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

-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200508212134
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

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

On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)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

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

On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)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

Michael Adler wrote:
> On Sun, Aug 21, 2005 at 09:13:20PM -0400, Tom Lane wrote:
> > Michael Fuhr <mike(at)fuhr(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

-----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(at)turnstep(dot)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-----

Robert Treat <xzilla(at)users(dot)sourceforge(dot)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

On Monday 22 August 2005 11:53, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)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

On Mon, Aug 22, 2005 at 11:53:22AM -0400, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461

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(at)users(dot)sourceforge(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073