Ok, that explains then the problem... but the index is arguably corrupt
in this case, with the important difference that it's not even fixable
by reindex...
I guess what the OP really wants is a solution to his problem. If the
table is not too big, a quick fix would be to just drop the index.
Then figure out an encoding+locale combination which can be used to
properly host the same sample data which leads to failure here and still
satisfies the OP's string sorting and other needs, and then dump
reload... or is there other better way to fix things ?
I guess a note in the docs about not using the same
encoding/locale/(postgres version?)/(OS?) combination as the OP would
make sense too ?
Cheers,
Csaba.
On Thu, 2005-12-15 at 16:20, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Csaba Nagy wrote:
> >> Based on the 3rd query of the OP, where the direct comparison results in
> >> "true" for all the rows which matched the "like", I would exclude the
> >> localisation issues variant... unless = is not equals in all cases ;-)
>
> > Well spotted Csaba - that _would_ seem to point to the index.
>
> No, localization issues should be real high on your list. In particular
> I wonder whether this is the old bugaboo of using a database encoding
> that's incompatible with the postmaster's locale setting. We've seen
> that on some platforms strcoll() gets completely confused by this and
> returns comparison results that are not even self-consistent.
>
> Non-self-consistent comparison results can lead to an index that is
> either actually or effectively corrupt (because index searches proceed
> down the wrong tree path and thus fail to find items that should be
> found). So the observation that only index searches fail is consistent
> with this idea.
>
> regards, tom lane