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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
