Skip site navigation (1) Skip section navigation (2)

Re: Questions about indexes with text_pattern_ops

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Kaare Rasmussen" <kaare(at)jasonic(dot)dk>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Questions about indexes with text_pattern_ops
Date: 2008-02-25 18:06:36
Message-ID: 87oda43o7n.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"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!

In response to

Responses

pgsql-hackers by date

Next:From: Bernd HelmleDate: 2008-02-25 18:08:36
Subject: Re: Strange behavior with leap dates and centuries BC
Previous:From: Peter EisentrautDate: 2008-02-25 17:55:42
Subject: pgsql: Link postgres from all object files at once, to avoid the

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group