Jochem van Dieten <jochemd@gmail.com> 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.
This same problem applies to the build-lock-catchup paradigm, although
less severely since you can hope that the entries to be added on at the
end are only a small part of the total and will fit in the excess space
that you leave in the index leaf pages. If you spend too long catching
up, though (as in the multiple-pass ideas that various people were
advocating), you'll end up with an index messy enough that it's
questionable why you bothered.
regards, tom lane