Re: pg_upgrade problem with invalid indexes

On Fri, Dec 7, 2012 at 04:49:19PM -0500, Bruce Momjian wrote:
> On Fri, Dec 7, 2012 at 10:38:39PM +0100, Andres Freund wrote:
> > On 2012-12-07 16:30:36 -0500, Bruce Momjian wrote:
> > > On Fri, Dec 7, 2012 at 04:21:48PM -0500, Tom Lane wrote:
> > > > Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > > > > On 2012-12-07 13:59:41 -0500, Tom Lane wrote:
> > > > >> indisvalid should be sufficient. If you try to test more than that
> > > > >> you're going to make the code more version-specific, without actually
> > > > >> buying much.
> > > >
> > > > > Doesn't the check need to be at least indisvalid && indisready? Given
> > > > > that 9.2 represents !indislive as indisvalid && !indisready?
> > > >
> > > > Um, good point. It's annoying that we had to do it like that ...
> > >
> > > So, does this affect pg_upgrade? Which PG versions?
> >
> > Only 9.2 :(. Before that there was no DROP INDEX CONCURRENTLY and in 9.3
> > there's an actual indislive field and indisready is always set to false
> > there if indislive is false.
> >
> > But I see no problem using !indisvalid || !indisready as the condition
> > in all (supported) versions.
>
> OK, updated patch attached.

Patch applied back to 9.0.

Now that it is applied, I need to publicize this. How do I do that?
Josh mentioned my blog.

What would cause these invalid indexes? Just CREATE INDEX CONCURRENTLY
failures? What types of failures would users have if these invalid
indexes had been upgraded by pg_upgrade? Can they test their indexes in
any way? I assume they can't run queries on the old cluster to check.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +