Re: disabling an index without deleting it?

On Tue, Feb 26, 2008 at 8:48 PM, Markus Bertheau
<mbertheau(dot)pg(at)googlemail(dot)com> wrote:
> 2008/2/27, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>
> > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > > "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
> >
> > >> begin;
> > >> drop index abc_dx;
> > >> select ....
> > >> rollback;
> > >>
> > >> and viola, your index is still there. note that there are likely some
> > >> locking issues with this, so be careful with it in production. But on
> > >> a test box it's a very easy way to test various indexes.
> >
> > > Wouldn't you also bloat the index?
> >
> >
> > No, what makes you think that? The index won't change at all in the
> > above example. The major problem is, as Scott says, that DROP INDEX
> > takes exclusive lock on the table so any other sessions will be locked
> > out of it for the duration of your test query.
>
> Why is the exclusive lock not taken later, so that this method can be
> used reasonably risk-free on production systems? From what I
> understand the later would be either a statement that would
> (potentially) be modifying the index, like an UPDATE or an INSERT, or
> actual transaction commit. If none of these occur and the transaction
> is rollbacked, the exclusive lock doesn't have to be taken at all.

It would rock to be able to do that on a production database. Any
Oracle DBA looking over your shoulder would fall to the floor and need
resuscitation.