Ühel kenal päeval, T, 2005-12-06 kell 20:50, kirjutas Jochem van Dieten:
> 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 of table (won't work for in-page
> > updates without code changes)
> >
> > 2) create the index as we do now, but only for pages which are visible
> > in SNAP1
> >
> > 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?
I'm hoping there to be some clever way to circumvent (the effects) of
it. But I can't see it yet.
> Wouldn't you need a lock to guard against that? (Not that
> I don't know if that is possible or desirable.)
That may be needed. At least I hope it to be possible in a way that can
quarantee avoiding deadlocks.
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 LOOP UP TO N SEC : IF NO OTHER TRANSACTIONS: BREAK ELSE:
CONTINUE IF NO OTHER TRANSACTIONS: BREAK ELSE: UNLOCK AGAINST STARTING NEW TRANSACTIONS SLEEP N SEC
TAKE SNAP2
COMMIT (AND UNLOCK)
This will eventually succeed (given right values for N ) and will
quarantee that SNAP2 and COMMIT are atomic wrt other backends.
--------------
Hannu