Skip site navigation (1) Skip section navigation (2)

Re: Reducing relation locking overhead

From: Jochem van Dieten <jochemd(at)gmail(dot)com>
To: Gregory Maxwell <gmaxwell(at)gmail(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Reducing relation locking overhead
Date: 2005-12-02 22:41:15
Message-ID: f96a9b830512021441u130ab9aap27700e287e7f38fc@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-12-02 22:41:34
Subject: Re: Numeric 508 datatype
Previous:From: Tom LaneDate: 2005-12-02 22:39:34
Subject: Re: Numeric 508 datatype

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group