Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing

Ü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