Re: unbalanced indexes -> fixed via dump/restore? - Mailing list pgsql-general

From Hiroshi Inoue
Subject Re: unbalanced indexes -> fixed via dump/restore?
Date
Msg-id 3AA82D0D.3CD472B6@tpf.co.jp
Whole thread Raw
In response to unbalanced indexes -> fixed via dump/restore?  (will trillich <will@serensoft.com>)
Responses Re: unbalanced indexes -> fixed via dump/restore?  (Alfred Perlstein <bright@wintelcom.net>)
List pgsql-general
Tom Lane wrote:
>
> Alfred Perlstein <bright@wintelcom.net> writes:
> > * Tom Lane <tgl@sss.pgh.pa.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.

Regards,
Hiroshi Inoue

pgsql-general by date:

Previous
From: "Rob Arnold"
Date:
Subject: Re: Optimisation of IN condition
Next
From: Alfred Perlstein
Date:
Subject: Re: unbalanced indexes -> fixed via dump/restore?