Re: Questions about indexes with text_pattern_ops

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> How so? If you think this change is a bad idea you'd better speak up
>>> PDQ.
>
>> Well I think it's fine for 'foo ' != 'foo' even if they sort similarly.
>
>> But I'm not sure it makes sense for <'foo ','a'> to sort after <'foo','b'> if
>> the locale says that 'foo ' should be compare "equal" to 'foo' and 'a' before
>> 'b'.
>
> I don't think we can concern ourselves with that; it would require
> allowing different columns of an index to interact, which would be
> impossibly messy. What's more, it'd destroy the property that a btree
> index is sorted by its leading column(s) as well as by all its columns.

Well, I was thinking we might have to separate the equal operators from the
btree opclass. Equals would be a stricter property than "sorts as same".

It would be mighty strange to have values which compare unequal but are
neither < or > though. Or which compare equal but also compare < or >.

It might be a little less surprising if we invent a new operator === for
"actually the same" and have == report whether two objects sort as equals. But
I'm not sure our experience with Turkish doesn't show that that will still
surprise people.

It may be more right in an abstract ideal world -- the reality is that text
collation is annoyingly complex. But this may be a case where we can get away
with just eliding this hassle.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!