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

Ühel kenal päeval, T, 2005-12-06 kell 15:41, kirjutas Tom Lane:
> Hannu Krosing <hannu(at)skype(dot)net> 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 thinking of committing a startup transaction and letting
> some of the locks be released by that.)

Hmm, that sounds like an plan:

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.

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

5) mark index as usable for query plans

-------------
Hannu