Re: [HACKERS] Exclusive lock for database rename
On Tue, Nov 08, 2005 at 04:06:32PM -0800, daveg wrote: I think this wait with an exponentially rising delay hurts not helps. If the stricter lock can be granted in a short time, ie the dalay could be small, then there is no problem. If the lock cannot be granted and the delay expires the stricter lock has incurred extra wait time already and allowed newer conflicting requests ahead of it possibly increasing the total wait time. As the timeout increases newer requests end up waiting for the new longer time anyway so the overall effect is to increase all lockers total wait time. But I don't see an alternative. Group A needs access to the resource, Group B (the rename) needs exclusive access. If you don't start holding off the members of group A, the rename will never complete. If you keep doing say 30 second waits, then any regular queries that take longer than that can block you out forever. The only way to eventually win is to eventually have a timeout longer than the longest currently running query. Anyway, this is theoretical as the code for this doesn't exist. It was just an idea. Have a nice day, -- Martijn van Oosterhout kleptog@svana.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. pgppIdXPTVHyy.pgp Description: PGP signature
Re: [HACKERS] Exclusive lock for database rename
On Wed, Nov 09, 2005 at 09:41:49AM +0100, Martijn van Oosterhout wrote: On Tue, Nov 08, 2005 at 04:06:32PM -0800, daveg wrote: I think this wait with an exponentially rising delay hurts not helps. If the stricter lock can be granted in a short time, ie the dalay could be small, then there is no problem. If the lock cannot be granted and the delay expires the stricter lock has incurred extra wait time already and allowed newer conflicting requests ahead of it possibly increasing the total wait time. As the timeout increases newer requests end up waiting for the new longer time anyway so the overall effect is to increase all lockers total wait time. But I don't see an alternative. Group A needs access to the resource, Group B (the rename) needs exclusive access. If you don't start holding off the members of group A, the rename will never complete. If you keep doing say 30 second waits, then any regular queries that take longer than that can block you out forever. The only way to eventually win is to eventually have a timeout longer than the longest currently running query. Exactly. Timing out the waits won't work. -dg -- David Gould [EMAIL PROTECTED] If simplicity worked, the world would be overrun with insects. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Exclusive lock for database rename
On Sat, Nov 05, 2005 at 11:48:56AM +0100, Martijn van Oosterhout wrote: 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... BTW, if you come up with a working example of this it would be a great addition to the docs. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Exclusive lock for database rename
On Tue, Nov 08, 2005 at 03:14:34PM -0600, Jim C. Nasby wrote: On Sat, Nov 05, 2005 at 11:48:56AM +0100, Martijn van Oosterhout wrote: 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... I think this wait with an exponentially rising delay hurts not helps. If the stricter lock can be granted in a short time, ie the dalay could be small, then there is no problem. If the lock cannot be granted and the delay expires the stricter lock has incurred extra wait time already and allowed newer conflicting requests ahead of it possibly increasing the total wait time. As the timeout increases newer requests end up waiting for the new longer time anyway so the overall effect is to increase all lockers total wait time. -dg -- David Gould [EMAIL PROTECTED] If simplicity worked, the world would be overrun with insects. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Exclusive lock for database rename
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? Jochem ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 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@svana.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. pgp8cZqtEedWB.pgp Description: PGP signature
Re: [HACKERS] Exclusive lock for database rename
On Thu, Nov 03, 2005 at 07:40:15PM -, Andrew - Supernews wrote: On 2005-11-03, Peter Eisentraut [EMAIL PROTECTED] wrote: Peter Eisentraut wrote: Someone wanted to rename a database while someone else was running a rather long pg_dump, so the rename had to wait, and everyone else had to wait for the rename because no new connections would be allowed. As an auxiliary issue, why do the new connections have to wait in this case? The rename waits for the AccessShareLock of the dump to be released, but meanwhile new connections should be able to get AccessShareLocks of their own. No. New AccessShare locks block behind the pending AccessExclusive lock. Otherwise AccessShare locks could starve the exclusive lock forever. 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. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Exclusive lock for database rename
On 2005-11-03, Peter Eisentraut [EMAIL PROTECTED] wrote: Peter Eisentraut wrote: Someone wanted to rename a database while someone else was running a rather long pg_dump, so the rename had to wait, and everyone else had to wait for the rename because no new connections would be allowed. As an auxiliary issue, why do the new connections have to wait in this case? The rename waits for the AccessShareLock of the dump to be released, but meanwhile new connections should be able to get AccessShareLocks of their own. No. New AccessShare locks block behind the pending AccessExclusive lock. Otherwise AccessShare locks could starve the exclusive lock forever. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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 datatypes do not match
