Re: Should I implement DROP INDEX CONCURRENTLY?

On ons, 2011-08-24 at 11:24 -0700, Daniel Farina 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). By non-trivial, I mean it can take 30+
> seconds, but less than a couple of minutes. The storage layer
> (starting from the higher levels of abstraction) are XFS, a somewhat
> trivial lvm setup, mdraid (8-ways), Amazon EBS (NBD?).
>
> 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.