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

On 12/6/05, Hannu Krosing wrote:
>
> 1) run a transaction repeatedly, trying to hit a point of no concurrent
> transactions, encance the odds by locking out starting other
> transactions for a few (tenths or hundredths of) seconds, if it
> succeeds, record SNAP1, commit and and continue, else rollback, then
> sleep a little and retry.

Which locks can be released by committing here?

> 2) build index on all rows inserted before SNAP1
>
> 3) run a transaction repeatedly, trying to hit a point of no concurrent
> transactions by locking out other transactions for a few (tenths or
> hundredths of) seconds, if it succeeds, record SNAP2, mark index as
> visible for inserts, commit. now all new transactions see the index and
> use it when inserting new tuples.
>
> 4) scan over table, add all tuples between SNAP1 and SNAP2 to index

You can not guarantee that every tuple inserted in the table will be
visible to SNAP 2 if you take SNAP2 before the commit of the
insert-only index has dropped below the global XMIN-horizon.

> 5) mark index as usable for query plans

How about:

- begin transaction X1
- insert all visible tuples in an index
- mark index incomplete
- commit

- wait for X1 to become visible to all running transactions (X1 is
known from the XMIN in pg_class / pg_index)

- begin transaction X2
- insert all missing tuples in index
- mark index complete
- commit

Jochem