3. Fix the tracking of when reindexes need to be rebuilt, so that you
can't get it wrong (as you're alluding to above).
I've mentioned this in the past, but didn't seem to get any traction, so I'll try it again ;-)
The focus on indexes when a collation changes is, in my opinion, the least of the problems. You definitely have to worry about indexes, but they can be easily rebuilt. What about other places where collation is hardened into the system, such as constraints?
For example, in ICU 4.6 the handling of accents changed for French. Previously accents were considered right-to-left but ICU 4.6 reversed this. So consider a constraint like CHECK COL < 'coté' (last letter is U+00E9, small letter e with acute). Prior to ICU 4.6 the value 'côte' (second letter is U+00F4, small letter o with circumflex) would have passed this constraint. With 4.6 or later it would be rejected because of the accent ordering change. As soon as the collation changes, this table becomes inconsistent and a reindex isn't going to help it. This becomes a data cleansing problem at this point (which sucks for the user because their data was clean immediately prior to the "upgrade").
There have similarly been cases where ICU changes have caused equal characters to become unequal and vice versa. (Unfortunately all my ICU notes with examples are at my previous employer.) Consider the effect on RI constraints. The primary key can be fixed with a reindex (although dealing with two existing values becoming equal is a pain). But then the user also has to deal with the foreign keys since they may now have foreign keys which have no match in the primary key.
And constraints problems are even easier than triggers. Consider a database with complex BI rules that are implemented through triggers that fire when values are/are not equal. If the equality of strings change, there could be bad data throughout the tables. (At least with constraints the inter-column dependencies are explicit in the catalogs. With triggers anything goes.)
All this collation stuff is great, and I know users want it, but it feels like were pushing them out of an airplane with a ripped parachute every time the collation libraries change. Maybe they'll land safely or maybe things will get very messy.
- Doug
Salesforce
