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
Ü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
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
--
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
Ü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 r
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 trad
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
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 ot
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
Ü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 P
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
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 h
Ü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 anythi
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,
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 gath
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 ch
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 t
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 res
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 co
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 happe
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'
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 if
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 understan
Ü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
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 RE
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
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 ...
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 o
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, th
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:
> > 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 sup
"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
"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 i
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
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
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
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
> > > > Oracl
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
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 lo
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 gre
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
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 be
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 th
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
> >
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 a
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 int
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
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
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
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 noth
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
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
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 tha
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)
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
> >
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
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
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 i
* 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
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 abou
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 take
61 matches
Mail list logo
