Re: Should I implement DROP INDEX CONCURRENTLY?

On Sat, Dec 31, 2011 at 8:26 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Fri, Dec 30, 2011 at 10:20 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> On ons, 2011-08-24 at 11:24 -0700, Daniel Farina wrote:
>>> I was poking around at tablecmds and index.c and wonder if a similar
>>> two-pass approach as used by CREATE INDEX CONCURRENTLY can be used to
>>> create a DROP INDEX CONCURRENTLY, and if there would be any interest
>>> in accepting such a patch.
>>
>> Hmm, it seems I just independently came up with this same concept.  My
>> problem is that if a CREATE INDEX CONCURRENTLY fails, you need an
>> exclusive lock on the table just to clean that up.  If the table is
>> under constant load, you can't easily do that.  So a two-pass DROP INDEX
>> CONCURRENTLY might have been helpful for me.
>
> Here's a patch for this. Please review.

I don't see how setting indisvalid to false helps with this, because
IIUC when a session sees indisvalid = false, it is supposed to avoid
using the index for queries but still make new index entries when a
write operation happens - but to drop an index, I think you'd need to
get into a state where no one was using the index for anything at all.

Maybe we need to change indisvalid to a "char" and make it three
valued: c = being created currently, v = valid, d = being dropped
concurrently, or something like that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company