From: | Hannu Krosing <hannu(at)skype(dot)net> |
---|---|
To: | Jochem van Dieten <jochemd(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <gsstark(at)mit(dot)edu>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing |
Date: | 2005-12-06 20:26:02 |
Message-ID: | 1133900762.3719.14.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Ü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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2005-12-06 20:29:27 | Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing |
Previous Message | Bruce Momjian | 2005-12-06 20:25:13 | Re: Upcoming PG re-releases |