Thread: Locale, Collation, ICU patch
Regarding the ICU patch in the commitfest here's my plan. IMHO the idea of making ICU a hard dependency which Postgres will have to use forevermore on all systems is a non-starter. I'm not entirely against having ICU as a supported collation system which packagers on systems where the system locale support is weak can choose to make a dependency of their binary packages though, assuming the issues raised elsewhere about ICU are resolved. As long as this bogeyman is scaring us though it's preventing us from having the SQL standard collation syntax and the accompanying catalog and planner changes. And as long as we don't have that support -- which is a big job -- nobody who's interested in implementing ICU or strcoll_l() or any other interfaces for a new platform will get around to it. The actual porting glue to call those functions on each platform is fairly lightweight and could easily be done by experts on that platform who aren't catalog and planner mavens. So we have a bit of a chicken and egg problem. We aren't getting the planner and syntax changes because we aren't sure the support would be good on every platform and we aren't getting the platform support because we don't have the planner and catalog changes. What I want to do is focus on adding the planner and catalog changes somehow. We implement a kind of baseline locale support something only slightly better than what we have now using setlocale before every comparison. This is clearly not the recommended configuration but as long as it handles what we handle today without a performance hit and a bit more besides it would be a big start. I'm assuming we would check if the desired locale is the current locale and skip the assignment. So if only one locale is *actually* in use then basically no additional overhead is incurred. Moreover if the desired locale is C then we can skip the assignment and use strcmp directly. So actually as long as only one non-C locale is in use then no additional overhead would be incurred. The big gotcha is what collation to use when comparing with data in the system tables, especially the shared system tables. I think we do need to define a database-wide encoding and collation to use for system tables. (Unless we can get by with varchar_pattern_ops indexes on system tables?) So the following use cases arise: a) They're actually using only one collation for both the system tables and their own data. This is well handled by our existing setup and would be basically unchanged in the new setup. b) They're using multiple collations for their data but only one "at a time". Either one per database or one per session. In which case they don't incur any overhead c) They're using multiple collations for their data but only one collation in a given application unit of work. This is probably the most common case for OLTP application since each unit of work represents some particular user's operation. In this case as long as the system tables are set up to use the C locale then this would require at most one setlocale() call per unit of work though. d) They're actively using multiple collations in a single query, possibly even within a single sort (something like ORDER BY a COLLATION en_US, b COLLATION es_US). This would perform passably on glibc but abysmally on most other libc's. From that point forward we would go about adding support for strcoll_l() and other interfaces to handle case (d) on various platforms. For platforms with no reasonable interface we could add a --enable-ICU users or packagers could choose to use. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Gregory Stark <stark@enterprisedb.com> writes:
> The big gotcha is what collation to use when comparing with data in the system
> tables, especially the shared system tables. I think we do need to define a
> database-wide encoding and collation to use for system tables.
You mean cluster-wide? If we can get away with that, it'd solve a lot
of problems.
Note that the stuff in the system tables is mostly type "name" not text,
and the comparison semantics for that have always been strcmp(), so the
question of collation doesn't really apply. Name in itself doesn't care
about encoding either, but I think we have to restrict encoding to avoid
the problem of injecting data that's invalidly encoded into one database
from another via the shared catalogs.
The other issue that'd have to be resolved is the problem of system log
output. I think we'd wish that log messages are written in a uniform
encoding (CSV output in particular is going to have a hard time
otherwise) but what do you do when you need to report something that
includes a character not present in that encoding?
regards, tom lane
Tom Lane wrote: > The other issue that'd have to be resolved is the problem of system log > output. I think we'd wish that log messages are written in a uniform > encoding (CSV output in particular is going to have a hard time > otherwise) but what do you do when you need to report something that > includes a character not present in that encoding? > > > I think the only problem with CSV logs would be in trying to read them back into Postgres (which I agree is the main point of having them). We need to be more aggressive about dealing with these problems, or else how will we ever get to per-column charsets/collations? cheers andrew
On Thu, Apr 03, 2008 at 06:54:50PM +0100, Gregory Stark wrote: > The big gotcha is what collation to use when comparing with data in the system > tables, especially the shared system tables. I think we do need to define a > database-wide encoding and collation to use for system tables. (Unless we can > get by with varchar_pattern_ops indexes on system tables?) In my version I simply made that all system tables were in locale C, standard binary sorting. Anything else is likely to blowup if you want to start using multiple encodings. From a performance point of view this is best, since you don't want the planner bogging down because the user wants an expensive collation. > b) They're using multiple collations for their data but only one "at a time". > Either one per database or one per session. In which case they don't incur any > overhead The case I'm thinking of if people wanting some columns to be case-insensetive. I'm not sure however if this will be in the first version though. Also, there isn't one-to-one correspondence between locales and collations. There are many more collations and I hope we will support that. The common variations are ascending/descending and case-sensetivity. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.