Re: How to pass around collation information

On 28/05/10 19:27, Peter Eisentraut wrote:
> I have been thinking about this collation support business a bit.
> Ignoring for the moment where we would get the actual collation routines
> from, I wonder how we are going to pass this information around in the
> system. Someone declares a collation on a column in a table, and
> somehow this information needs to arrive in bttextcmp() and friends.

Yes. Comparison operators need it, as do functions like isalpha().

> Also, functions that take in a string and return one (e.g., substring),
> need to take in this information and return it back out. How should
> this work?

Hmm, I don't see what substring would need collation for. And it
certainly shouldn't be returning it. Collation is a property of the
comparison operators (and isalpha etc.), and the planner needs to deduce
the right collation for each such operation in the query. That involves
looking at the tables and columns involved, as well as per-user
information and any explicit COLLATE clauses in the query, but all that
happens at plan-time.

> Option 1, make it part of the datum. That way it will pass through the
> system just fine, but it would waste a lot of storage and break just
> about everything that operates on string types now, as well as
> pg_upgrade. So that's probably out.

It's also fundamentally wrong, collation is not a property of a datum
but of the operation.

> Option 2, invent some new mechanism that accompanies a datum or a type
> whereever it goes. Kind of like typmod, but not really. Then the
> collation information would presumably be made available to functions
> through the fmgr interface. The binary representation of data values
> stays the same.

Something like that. I'm thinking that bttextcmp() and friends will
simply take an extra argument indicating the collation, and we'll teach
the operator / operator class infrastructure about that too.

One way to approach this is to realize that it's already possible to use
multiple collations in a database. You just have to define separate < =
> operators and operator classes for every collation, and change all
your queries to use the right operator depending on the desired
collation everywhere where you use < = > (including ORDER BYs, with the
USING <operator> syntax). The behavior is exactly what we want, it's
just completely inpractical, so we need something to do the same in a
less cumbersome way.

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