Re: unbalanced indexes -> fixed via dump/restore?

* Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> [010308 17:07] wrote:
> Tom Lane wrote:
> >
> > Alfred Perlstein <bright(at)wintelcom(dot)net> writes:
> > > * Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [010307 14:30] wrote:
> > >> Plain old DROP INDEX / CREATE INDEX is probably the best-trodden path.
> > >> Your (A) seems like vastly more work than is needed. (B) might be
> > >> marginally easier than DROP/CREATE, but I'm not sure how much I trust
> > >> REINDEX; it's not been around all that long.
> >
> > > Is there a way to do this atomically, meaning so that no one can
> > > get at the table after dropping, but before recreating the index?
> >
> > In 7.1 it should work to do
> >
> > begin;
> > drop index fooi;
> > create index fooi on foo (...);
> > end;
> >
> > The DROP acquires an exclusive lock on foo, so there's no need for
> > an explicit "lock table foo", though you can add one if it seems
> > clearer that way.
> >
> > Before 7.1 this is too risky, because if the create index fails for
> > some reason, you're hosed (the attempted rollback of DROP will screw up).
> >
> > btw, REINDEX essentially does the same thing as the above,
>
> Yes REINDEX is safe under postmaster in 7.1.
> In addtion REINDEX has some advantages.
> 1) no necessity to scatter the index definition.
> 2) it doesn't change any reference among system objects.
>
> > but there's
> > a lot of strange additional locking code in it,which I don't trust
> > much... call it a design disagreement with Hiroshi ;-)
> >
>
> Is it LockClassForUpdate() ? If so it's never a special function.
> It's only implementing a 'FOR UPDATE' part of 'SELECT .. FROM PG_CLASS'
> and 'select .. for update' before 'update ..' is an oridinary
> sequence of update operations.

Is there a way to do this under 7.0.3?

--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]