Re: Reducing relation locking overhead

On 12/3/05, Tom Lane wrote:
> Jochem van Dieten writes:
>> How about the following sceanrio for building a new index:
>> - create an empty index
>> - flag it as incomplete
>> - commit it so it becomes visible to new transactions
>> - new transactions will update the index when inserting / updating
>> - the planner will not use it for queries because it is flagged as incomplete
>> - wait until the the index is visible to all running transactions
>> - start a new seqscan and insert all records in the index
>> - commit
>> - remove the incomplete flag
>> Wouldn't this overcome the lock upgrade problem?
>
> Doesn't really solve the problem for REINDEX, though.  Presumably, the
> reason that you are REINDEXing is that you would like to defragment the
> existing index.  Well, that requires collecting all the index entries
> and sorting them.  The above method is not going to produce a nicely
> sorted index; whatever entries get made on-the-fly during the first
> stage are going to determine the index shape.

So how about merging this approach with the catch-up approach?

- take an access share lock so the index doesn't change
- build the index in a non-locking approach
- flag the index as incomplete
- commit it
- from now on new transaction will register their changes in the index
- wait until it is visible to all
- do a table scan with special xmin-xmax comparison
- commit the mising tuples
- remove the incomplete flag

Jochem