Thread: Exclusive lock for database rename
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. It's clear that we need to prevent new connections to the database being renamed, but isn't there a less intrusive way to do that (row locks?)? Right now, everyone with createdb privileges effectively has DOS superpowers. -- Peter Eisentraut http://developer.postgresql.org/~petere/
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. I see the following in pg_locks:
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+---------------------+---------
[This is the "dump". (open transaction with select * from pg_database)] 1262 | 0 | | 12296 |
AccessShareLock | t | | 143076 | 12296 | ExclusiveLock | t
[This is pg_locks.] 16839 | 24882 | | 12296 | AccessShareLock | t
[This is the rename.] | | 143078 | 12298 | ExclusiveLock | t 1262 | 0 |
| 12298 | AccessExclusiveLock | f
[This is a new connection.] | | 143079 | 12312 | ExclusiveLock | t 1262 | 0 |
| 12312 | AccessShareLock | f
Why can't 12312 get the lock on 1262?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> It's clear that we need to prevent new connections to the database
> being renamed, but isn't there a less intrusive way to do that (row
> locks?)?
This is fixed already for 8.1.
regards, tom lane
On 2005-11-03, Peter Eisentraut <peter_e@gmx.net> 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
On Thu, Nov 03, 2005 at 07:40:15PM -0000, Andrew - Supernews wrote: > On 2005-11-03, Peter Eisentraut <peter_e@gmx.net> 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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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
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.
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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 daveg@sonic.net If simplicity worked, the world would be overrun with insects.
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.
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 daveg@sonic.net If simplicity worked, the world would be overrun with insects.
Hi, In function IndexSupportInitialize, for each attribute , we call a function LookUpOpClassInfo(...) and then we find opcentry object which contain supportProcs pointer which contain the Oid of the function to be called at the run time. I am not getting How to find out that Oid from attribute type information. It represent every data type of attribute by some number in opindclass structure of iform , for eg. varvhar() is represent by 2003 int is represent by 1978 ...... Please tell me how to find Oid from opindclass information. Thanx and regards, sandeep -- ------------------ | Sandeep Satpal | | M.Tech Student | | Lab 212 KReSIT | ------------------