Re: Sleep functions

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