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

--On 24. März 2016 14:04:22 +0100 Magnus Hagander <magnus(at)hagander(dot)net>
wrote:

> 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?

This is exactly where the prototype btreecheck helped a lot. The last time
i used it to track down problems we got

> WARNING: page order invariant violated for index

which nailed down collation problems on that specific machine and to
identify indexes, where we got the problem.

For example, if you take the bug report from Marc-Olaf and check the
affected table/index with the current amcheck patch, you get:

bernd(at)localhost:test #= SELECT bt_index_check('foo_val_idx');
ERROR: XX002: page order invariant violated for index "foo_val_idx"
DETAIL: Lower index tid=(1,1) (points to heap tid=(0,1)) higher index
tid=(1,2) (points to heap tid=(0,2)) page lsn=0/0.
LOCATION: bt_target_page_check, amcheck.c:687
STATEMENT: SELECT bt_index_check('foo_val_idx');

So if you ask me, this absolutely is a "must-have".

--
Thanks

Bernd