Re: Exclusive lock for database rename

On Sat, Nov 05, 2005 at 10:47:30AM +0100, Jochem van Dieten wrote:
> On 11/4/05, Jim C. Nasby wrote:
> >
> > I would argue that in cases like this (and 'this' means just about any
> > DDL, for starters) that it would be better not to block everyone until
> > work can actually be done. Or at least make that an option.
>
> Would it be possible to simulate this by manually trying to grab a
> lock on a relation using NOWAIT in a loop or are the locks DDL
> requires different from the ones acquired by the LOCK statement?

What you want is probably some kind of "attempt to grab lock with
timeout". Ie, it tries to grab the lock but gets stuck waiting for
someone else. After some timeout it fails, waits a few seconds and
tries again. That few seconds allows other clients waiting for you to
unstuck.

Set the timeout to maybe 30 seconds. Then no query will wait for your
lock for more than 30 seconds. Or maybe exponentially rising delay,
otherwise you'll never guarentee completion. With notice to client what
is happening, hopefully...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.