Re: Re: Missing rows with index scan when collation is not "C" (PostgreSQL 9.5)

On Thu, Mar 24, 2016 at 6:04 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> And if not that one, can we find some other way for people to find out if
> they need to REINDEX after the upgrade? It would be very nice not to have to
> tell everybody to reindex everything, but to actually detect the cases where
> it's needed. Or at least provide a supported way to do that, for those where
> a cluster-wide reindex is really expensive.

If amcheck was made to only verify pages in isolation, then it have a
very strong chance of finding any issues, but not an iron-clad
guarantee -- it might be that the ordering was wrong across pages
(although that seems like a very small space for problems to hide).
Because we know that there is a sane total ordering for both strcoll()
and strxfrm() cases on affected systems, I'm pretty sure that the
version of amcheck in the ongoing CF (that checks child/parent, as
well as sibling relationships) would actually catch any problems of
that kind *reliably*. In other words, it would be okay that it didn't
check every item against every other item, because per Tom's analysis
the transitive law is not broken in either case, even if strcoll() is
buggy.

> Even if we can't sneak amcheck into 9.5, if we can show that it detects the
> problem, then just being able to direct people to "get amcheck from 9.6 if
> you want to check if the reindex is necessary" would still be a strong
> improvement over nothing.

Agreed.

--
Peter Geoghegan