Re: PGDay.it collation discussion notes

Tom Lane wrote:
> Another objection to this design is that it's completely unclear that
> functions from text to text should necessarily yield the same collation
> that went into them, but if you treat collation as a hard-wired part of
> the expression syntax tree you aren't going to be able to do anything else.
> (What will you do about functions/operators taking more than one text
> argument?)

Whatever the spec says. Collation is intimately associated with the 
comparison operations, and doesn't make any sense anywhere else. The way 
the default collation for a given operation is determined, by bubbling 
up the collation from the operands, through function calls and other 
expressions, is just to make life a bit easier for the developer who's 
writing the SQL. We could demand that you always explicitly specify a 
collation when you use the text equality or inequality operators, but 
because that would be quite tiresome, a reasonable default is derived 
from the context.

I believe the spec stipulates how that default is derived, so I don't 
think we need to fret over it. We'll need it eventually, but the parser 
changes is not the critical part. We can start off by deriving the 
collation from a GUC variable, for example.

> I think it would be better to treat the collation indicator as part of
> string *values* and let it bubble up through expressions that way.
> The "expr COLLATE ident" syntax would be a simple run-time operation
> that pokes a new collation into a string value.  The notion of a column
> having a particular collation would then amount to a check constraint on
> the values going into the column.

Looking at an individual value, collation just doesn't make sense.
Collation is property of the comparison operation, not of a value.

In the parser, we might have to do something like that though, because 
according to the standard you can tack the COLLATION keyword to string 
constants and have it bubble up. But let's keep that ugliness just 
inside the parser.

One, impractical, way to implement collation would be to have one 
operator class per collation. In fact you could do that today, with no 
backend changes, to support multiple collations. It's totally 
impractical, because for starters you'd need different comparison 
operators, with different names, for each collation. But it's the right 
mental model.

I think the right approach is to invent a new concept called "operator 
modifier". It's basically a 3rd argument to operators. It can be 
specified explicitly when an operator is used, with syntax like "<left> 
Op <right> USING <modifier>", or in case of collation, it's derived from 
the context, per SQL spec. The operator modifier is tacked on to OpExprs 
and SortClauses in the parser, and passed as a 3rd argument to the 
function implementing the operator at execution time.

When an index is created, if the operators in the operator class take an 
operator modifier, it's stored at creation time into a new column in 
pg_index (needs to be a vector or array to handle multi-column indexes). 
The planner needs to check the modifier when it determines whether an 
index can be used or not.

BTW, this reminds me of the discussions we had about the tsearch default 
configuration. It's different, though, because in full text search, 
there's a separate tsvector data type, and the problem was with 
expression indexes, not regular ones.

Another consideration is LC_CTYPE. Just like we want to support
different collations, we should support different character
classifications for upper()/lower(). We might want to tie it into
collation, as using different ctype and collation doesn't usually make
sense, but it's something to keep in mind.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com