Re: Sleep functions - Mailing list pgsql-hackers

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