Thread: 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
-----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
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
-----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-----
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
-----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
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@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
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
-----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-----
"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
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
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
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
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
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
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
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
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