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/
Show quoted text
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/
Show quoted text
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 |
------------------