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

On Wed, Mar 23, 2016 at 7:14 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> On Wed, Mar 23, 2016 at 11:09 AM, Magnus Hagander <magnus(at)hagander(dot)net>
> wrote:
> > We want to get it back to working. But short-term, it's more important to
> > limit the scope of the brokenness, since this is a version that people
> are
> > putting in production. Once we have enough info to safely say we've put a
> > workaround in place, we turn it back on.
>
> Do you think it's possible that my amcheck tool might have a role to
> play here? I wrote it for exactly this kind of scenario. If we could
> get it reviewed, then a pre-release version compatible with 9.5 could
> be made available. I'd be willing to work on that side of things if
> core are receptive. Early prototypes of the tool were used to detect
> collation incompatibility issues in production.
>

That's a good question? Would it catch corruption like this? I haven't
actually tested it :) My understanding is that the thing that can happen is
that while we don't actually store incorrect values in the indexes, we can
end up with index pointers in the wrong order in the indexes with this bug?
That does sound like one of those things that the amcheck tool is designed
to find?

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.

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.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/