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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
