From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | Daniel Farina <daniel(at)heroku(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Should I implement DROP INDEX CONCURRENTLY? |
Date: | 2011-08-24 19:38:09 |
Message-ID: | 4063.1314214689@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Wed, Aug 24, 2011 at 1:24 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
>> At Heroku we use CREATE INDEX CONCURRENTLY with great success, but
>> recently when frobbing around some indexes I realized that there is no
>> equivalent for DROP INDEX, and this is a similar but lesser problem
>> (as CREATE INDEX takes much longer), as DROP INDEX takes an ACCESS
>> EXCLUSIVE lock on the parent table while doing the work to unlink
>> files, which nominally one would think to be trivial, but I assure you
>> it is not at times for even indexes that are a handful of gigabytes
>> (let's say ~=< a dozen).
> Are you sure that you are really waiting on the time to unlink the
> file? there's other stuff going on in there like waiting for lock,
> plan invalidation, etc. Point being, maybe the time consuming stuff
> can't really be deferred which would make the proposal moot.
Assuming the issue really is the physical unlinks (which I agree I'd
like to see some evidence for), I wonder whether the problem could be
addressed by moving smgrDoPendingDeletes() to after locks are released,
instead of before, in CommitTransaction/AbortTransaction. There does
not seem to be any strong reason why we have to do that before lock
release, since incoming potential users of a table should not be trying
to access the old physical storage after that anyway.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Farina | 2011-08-24 20:04:55 | Re: Should I implement DROP INDEX CONCURRENTLY? |
Previous Message | Robert Haas | 2011-08-24 19:36:29 | Re: Should I implement DROP INDEX CONCURRENTLY? |