Re: Sleep functions

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