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@xxxxxxxxxxxx> 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
