Re: Reducing relation locking overhead

On 12/2/05, Alvaro Herrera wrote:
> Gregory Maxwell wrote:
>>
>> After you're mostly caught up, change locking behavior to block
>> further updates while the final catchup happens. This could be driven
>> by a hurestic that says make up to N attempts to catch up without
>> blocking, after that just take a lock and finish the job. Presumably
>> the catchup would be short compared to the rest of the work.
>
> The problem is that you need to upgrade the lock at the end of the
> operation. This is very deadlock prone, and likely to abort the whole
> operation just when it's going to finish. Is this a showstopper? Tom
> seems to think it is. I'm not sure anyone is going to be happy if they
> find that their two-day reindex was aborted just when it was going to
> finish.

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?

Jochem