Hannu Krosing wrote:
Ühel kenal päeval, L, 2005-12-10 kell 21:07, kirjutas Tom Lane:
In any case the design idea here
seems to be we don't care how long REINDEX takes as long as it's not
blocking anyone.
Yes, thats the general idea.
Within reason of course, for example making a
On Sat, 2005-12-10 at 21:07 -0500, Tom Lane wrote:
In any case the design idea here
seems to be we don't care how long REINDEX takes as long as it's not
blocking anyone.
All sounds great so far. I'd like this as an option for CREATE INDEX
also.
Best Regards, Simon Riggs
Ühel kenal päeval, L, 2005-12-10 kell 21:07, kirjutas Tom Lane:
Hannu Krosing [EMAIL PROTECTED] writes:
How do you plan to determine any rows not already present in the index
without explicitly remembering the start and end snapshots of existing
CREATE INDEX (SNAP1 and SNAP2 in my
Ühel kenal päeval, N, 2005-12-08 kell 14:53, kirjutas Tom Lane:
Given the discussion so far, it seems likely to me that completely
concurrent REINDEX is indeed out of reach, and that what we ought to
be thinking about is what sort of compromise design (ie, partially
concurrent REINDEX) is
Hannu Krosing [EMAIL PROTECTED] writes:
How do you plan to determine any rows not already present in the index
without explicitly remembering the start and end snapshots of existing
CREATE INDEX (SNAP1 and SNAP2 in my proposal)?
I was thinking in terms of actually looking into the index to see
On Thu, 2005-12-01 at 21:37 -0500, Tom Lane wrote:
In looking at the current pgbench results, I notice that one
considerable contribution to LWLock contention is access to the
heavyweight-lock manager.
4. The only reason we need to take relation-level locks on indexes
at all is to make the
Ühel kenal päeval, N, 2005-12-08 kell 01:08, kirjutas Jim C. Nasby:
On Thu, Dec 08, 2005 at 08:57:42AM +0200, Hannu Krosing wrote:
??hel kenal p??eval, N, 2005-12-08 kell 00:16, kirjutas Jim C. Nasby:
On Sat, Dec 03, 2005 at 10:15:25AM -0500, Greg Stark wrote:
Tom Lane [EMAIL PROTECTED]
Simon Riggs [EMAIL PROTECTED] writes:
Further thoughts:
1. Normally, we do not lock indexes via the LockMgrLock
2. When a REINDEX-like operation comes along, it first of all updates an
MaintIntentLock flag on the index relation, which causes a relcache
invalidation. It then waits until all
On Thu, 2005-12-08 at 16:05, Tom Lane wrote:
[SNIP]
There isn't any way for it to do that (ie, be sure everyone else has
adjusted to the new state of affairs), short of acquiring some sort of
short-term exclusive lock on the table, which is a really bad idea.
The pending lock would block other
On Thu, 2005-12-08 at 16:23 +0100, Csaba Nagy wrote:
On Thu, 2005-12-08 at 16:05, Tom Lane wrote:
[SNIP]
There isn't any way for it to do that (ie, be sure everyone else has
adjusted to the new state of affairs), short of acquiring some sort of
short-term exclusive lock on the table, which
Simon Riggs [EMAIL PROTECTED] writes:
I'm hand-waving here, so I'll stop. But we do know there *is* a way,
because this is already implemented elsewhere, somehow.
That's not really the point --- the question is whether the cure is
worse than the disease. It's entirely possible that the
On Fri, Dec 02, 2005 at 03:25:58PM -0500, Greg Stark wrote:
Postgres would have no trouble building an index of the existing data using
only shared locks. The problem is that any newly inserted (or updated) records
could be missing from such an index.
To do it you would then have to gather
On Sat, Dec 03, 2005 at 10:15:25AM -0500, Greg Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
What's worse, once you have excluded writes you have to rescan the entire
table to be sure you haven't missed anything. So in the scenarios where this
whole thing is actually interesting, ie
Ühel kenal päeval, N, 2005-12-08 kell 00:16, kirjutas Jim C. Nasby:
On Sat, Dec 03, 2005 at 10:15:25AM -0500, Greg Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
What's worse, once you have excluded writes you have to rescan the entire
table to be sure you haven't missed anything. So in
On Thu, Dec 08, 2005 at 08:57:42AM +0200, Hannu Krosing wrote:
??hel kenal p??eval, N, 2005-12-08 kell 00:16, kirjutas Jim C. Nasby:
On Sat, Dec 03, 2005 at 10:15:25AM -0500, Greg Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
What's worse, once you have excluded writes you have to
On 12/5/05, Hannu Krosing wrote:
Concurrent CREATE INDEX
Concurrent index NDX1 on table TAB1 is created like this:
1) start transaction. take a snapshot SNAP1
1.1) optionally, remove pages for TAB1 from FSM to force (?) all newer
inserts/updates to happen at end
Jochem van Dieten [EMAIL PROTECTED] writes:
On 12/5/05, Hannu Krosing wrote:
3) record the index in pg_class, but mark it as do not use for lookups
in a new field. Take snapshot SNAP2. commit transaction.
What happens if another transaction takes a snapshot between SNAP2 and
the commit?
Hannu Krosing [EMAIL PROTECTED] writes:
What I have in mind would be something like this to get both SNAP2 and
commit between any transactions:
LOOP:
LOCK AGAINST STARTING NEW TRANSACTIONS
I can hardly credit that let's block startup of ALL new transactions
is a more desirable
Hannu Krosing [EMAIL PROTECTED] writes:
Is it possible to release a lock without commit ?
Yes, but I don't see where that helps you here.
(To do any of this, you'd need to use the same kluge VACUUM does to hold
selected locks across a series of transactions. So in reality you'd
probably be
Hannu Krosing [EMAIL PROTECTED] writes:
The scenario where concurrent create index command is be needed is 24/7
OLTP databases, which can't be taken down for maintenance. Usully they
can be arranged to tolerate postponing a few transactions for one
second.
Well, the dominant defining
On Sun, Dec 04, 2005 at 10:40:55PM -0800, Kevin Brown wrote:
One thing I don't quite understand about the discussion is why there's
particular attention being paid to deadlocks with respect to REINDEX
when it clearly can happen in the general case when lock promotion is
involved. Why is
Ühel kenal päeval, R, 2005-12-02 kell 02:14, kirjutas Tom Lane:
Greg Stark [EMAIL PROTECTED] writes:
It was a *major* new feature that many people were waiting for when Oracle
finally implemented live CREATE INDEX and REINDEX. The ability to run create
an index without blocking any
Kevin Brown [EMAIL PROTECTED] writes:
And now I see why, since it will introduce deadlocks (sigh). But what
of the other rule (always acquiring locks against the table before the
index)? You may have stopped reading at the above...
We already do that.
One thing I don't quite understand
I wonder if it would work to release the AccessShareLock before
acquiring the ExclusiveLock. Of course, this would let any ALTER TABLE
or DROP TABLE to do anything they wanted, but we could check that the
table is still the same after reacquiring the exclusive lock. REINDEX
would have to abort
Tom Lane wrote:
The concern about deadlock applies to the various proposals that involve
upgrading to a write-prevention lock at some late point in the process.
That clearly has the potential to deadlock against relatively weak lock
requests.
After looking at the various lock types, I don't
Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
Tom Lane wrote:
Even ignoring that, you *still* have a lock upgrade problem
in this sketch.
Hmm, well, I can see a deadlock potential for those operations that
have to acquire multiple locks simultaneously, and I suppose that the
Kevin Brown [EMAIL PROTECTED] writes:
I guess the real question here is: is it possible to, in code,
guarantee the order of lock acquisition by any given transaction?
Yes, but not in our code :-(. This is largely determined by what the
application does.
regards, tom
Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
I guess the real question here is: is it possible to, in code,
guarantee the order of lock acquisition by any given transaction?
Yes, but not in our code :-(. This is largely determined by what the
application does.
Yeah, that's what
Kevin Brown [EMAIL PROTECTED] writes:
The fact that you've been holding the AccessShareLock for quite a
long time means that the window for deadlock problems is very wide.
But with respect to deadlocks, that's true only if deadlocks are
possible, which is true only if the order of lock
Kevin Brown [EMAIL PROTECTED] writes:
- when requesting a type of lock, one must first acquire all lesser
lock types against the object in order of strength. Hence, one must
acquire AccessShareLock before acquiring AccessExclusiveLock.
This is exactly wrong ...
Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
- when requesting a type of lock, one must first acquire all lesser
lock types against the object in order of strength. Hence, one must
acquire AccessShareLock before acquiring AccessExclusiveLock.
This is exactly wrong ...
And
On Fri, 2005-12-02 at 17:45 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
CREATE INDEX uses SnapshotAny, so the scan that feeds the build could
easily include rows added after the CREATE INDEX started. When the scan
was exhausted we could mark that last TID and return to it
On Friday 02 December 2005 09:53, Simon Riggs wrote:
On Fri, 2005-12-02 at 02:14 -0500, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
It was a *major* new feature that many people were waiting for when
Oracle finally implemented live CREATE INDEX and REINDEX. The ability
to run
Tom Lane [EMAIL PROTECTED] writes:
What's worse, once you have excluded writes you have to rescan the entire
table to be sure you haven't missed anything. So in the scenarios where this
whole thing is actually interesting, ie enormous tables, you're still
talking about a fairly long interval
Alvaro Herrera [EMAIL PROTECTED] writes:
The problem is that you need to upgrade the lock at the end of the
operation. This is very deadlock prone, and likely to abort the whole
operation just when it's going to finish. Is this a showstopper? Tom
seems to think it is. I'm not sure anyone
Greg Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
What's worse, once you have excluded writes you have to rescan the entire
table to be sure you haven't missed anything. So in the scenarios where this
whole thing is actually interesting, ie enormous tables, you're still
talking
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
What's worse, once you have excluded writes you have to rescan the entire
table to be sure you haven't missed anything.
I was thinking you would set a flag to disable use of the FSM for
inserts/updates while the reindex
Greg Stark [EMAIL PROTECTED] writes:
Other than that case is there any other case the reindex could deadlock with?
Only SELECT, INSERT, UPDATE, and DELETE.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions
Kevin Brown [EMAIL PROTECTED] writes:
In the above for large relations, the bulk of the REINDEX should
happen without any locks being held by the REINDEX operation.
As I just pointed out to Greg, the arm-waving notion that you can turn
off the FSM requires a great deal of low-level locking that
Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
In the above for large relations, the bulk of the REINDEX should
happen without any locks being held by the REINDEX operation.
As I just pointed out to Greg, the arm-waving notion that you can turn
off the FSM requires a great deal of
Kevin Brown [EMAIL PROTECTED] writes:
Tom Lane wrote:
Even ignoring that, you *still* have a lock upgrade problem
in this sketch.
Hmm, well, I can see a deadlock potential for those operations that
have to acquire multiple locks simultaneously, and I suppose that the
table + FSM lock would
Tom Lane [EMAIL PROTECTED] writes:
Greg Stark [EMAIL PROTECTED] writes:
Other than that case is there any other case the reindex could deadlock
with?
Only SELECT, INSERT, UPDATE, and DELETE.
How does that happen? What exclusive locks do these take that reindex would
conflict with? I
On Sat, 2005-12-03 at 08:47 -0500, Robert Treat wrote:
On Friday 02 December 2005 09:53, Simon Riggs wrote:
On Fri, 2005-12-02 at 02:14 -0500, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
It was a *major* new feature that many people were waiting for when
Oracle finally
Simon Riggs wrote:
Understood. At 7.3, REINDEXing is essential, since rows never got
deleted and space was not reused. That is not the case now, hence a
REINDEX is less often required.
But it's still required or at least desirable under some circumstances.
If it could be improved, it would be
On 12/3/05, Tom Lane wrote:
Jochem van Dieten writes:
How about the following sceanrio for building a new index:
- create an empty index
- flag it as incomplete
- commit it so it becomes visible to new transactions
- new transactions will update the index when inserting / updating
- the
On Sat, 2005-12-03 at 17:16 -0300, Alvaro Herrera wrote:
Simon Riggs wrote:
Understood. At 7.3, REINDEXing is essential, since rows never got
deleted and space was not reused. That is not the case now, hence a
REINDEX is less often required.
But it's still required or at least
Tom Lane [EMAIL PROTECTED] wrote
The real situation is that you must hold at least AccessShareLock on the
table throughout the entire operation, else you have no defense against
(say) someone dropping the index or the entire table out from under you.
And when you add onto this lock in order
Qingqing Zhou [EMAIL PROTECTED] writes:
What if we change to cost-based removal, i.e., remove the one whose cost is
smaller. In this case, an two-days-to-be-done reindex should never get
killed.
Instead, it would kill all useful work in your system :-(. An old
transaction would be the one
On Fri, 2005-12-02 at 02:14 -0500, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
It was a *major* new feature that many people were waiting for when Oracle
finally implemented live CREATE INDEX and REINDEX. The ability to run create
an index without blocking any operations on a
Simon Riggs [EMAIL PROTECTED] writes:
On Fri, 2005-12-02 at 02:14 -0500, Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
It was a *major* new feature that many people were waiting for when Oracle
finally implemented live CREATE INDEX and REINDEX. The ability to run
create
an
On 02 Dec 2005 15:25:58 -0500, Greg Stark [EMAIL PROTECTED] wrote:
I suspect this comes out of a very different storage model from Postgres's.
Postgres would have no trouble building an index of the existing data using
only shared locks. The problem is that any newly inserted (or updated)
Gregory Maxwell wrote:
On 02 Dec 2005 15:25:58 -0500, Greg Stark [EMAIL PROTECTED] wrote:
I suspect this comes out of a very different storage model from Postgres's.
Postgres would have no trouble building an index of the existing data using
only shared locks. The problem is that any
On Fri, 2005-12-02 at 19:04 -0300, Alvaro Herrera wrote:
Gregory Maxwell wrote:
On 02 Dec 2005 15:25:58 -0500, Greg Stark [EMAIL PROTECTED] wrote:
I suspect this comes out of a very different storage model from
Postgres's.
Postgres would have no trouble building an index of the
On 12/2/05, Alvaro Herrera wrote:
Gregory Maxwell wrote:
After you're mostly caught up, change locking behavior to block
further updates while the final catchup happens. This could be driven
by a hurestic that says make up to N attempts to catch up without
blocking, after that just take a
Simon Riggs [EMAIL PROTECTED] writes:
CREATE INDEX uses SnapshotAny, so the scan that feeds the build could
easily include rows added after the CREATE INDEX started. When the scan
was exhausted we could mark that last TID and return to it after the
sort/build.
And do what? This has nothing
Jochem van Dieten [EMAIL PROTECTED] writes:
How about the following sceanrio for building a new index:
- create an empty index
- flag it as incomplete
- commit it so it becomes visible to new transactions
- new transactions will update the index when inserting / updating
- the planner will
In looking at the current pgbench results, I notice that one
considerable contribution to LWLock contention is access to the
heavyweight-lock manager. Almost all of that comes from taking
relation-level locks, so we could cut down the contention if we could
reduce the number of distinct locks
4. The only reason we need to take relation-level locks on indexes
at all is to make the world safe for REINDEX being done concurrently
with read-only accesses to the table (that don't use the index being
reindexed). If we went back to requiring exclusive lock for reindex we
could forget all
* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote:
4. The only reason we need to take relation-level locks on indexes
at all is to make the world safe for REINDEX being done concurrently
with read-only accesses to the table (that don't use the index being
reindexed). If we went back to
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Surely in the real world REINDEX is run so rarely compared to all those other
operations it'd be a win...
It's not a question of frequency. We're not talking about something like a 10%
performance loss. You're talking about whether REINDEX is
Greg Stark [EMAIL PROTECTED] writes:
It was a *major* new feature that many people were waiting for when Oracle
finally implemented live CREATE INDEX and REINDEX. The ability to run create
an index without blocking any operations on a table, even updates, was
absolutely critical for 24x7
61 matches
Mail list logo
