Currently although lc_collate and lc_ctype setting are used for both master and segments during init_db (see hawqinit.sh), however the two variables are not actually that meaningful, to my knowledge. The reason is that: take lc_collate as example, the collate settings on segments seem to be quite related to the environment settings (shell profile). That could lead to some confusions in some queries (especially for query that needs string comparison (e.g. man strcoll(3)).
Take a real query as example. I set different locale settings on one segment node in two tests, and I run the same query command, the results are finally different. This is really bad, but seems to be not difficult to fix. I checked upstream pg commit logs it seems that pg have had similar patch. I think we should fix this issue based on upstream patch. commit 61d967498802ab86d8897cb3c61740d7e9d712f6 Author: Heikki Linnakangas <[email protected]> Date: Tue Sep 23 09:20:39 2008 +0000 Make LC_COLLATE and LC_CTYPE database-level settings. Collation and ctype are now more like encoding, stored in new datcollate and datctype columns in pg_database. postgres=# select * from test_varchar where f1<'k'; f1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- jfkajdkfjanbknfljkljk2j4i24rwi992jklfsjafklajsklfjasdklfjdsaklfjsdklnlnbndfklgjsaklfjsakfjaskjfklsajfklasjfklsajfklsajfklsadj209321094802938590284025802480fjsadfjsakdlfjklsajfklsajfk sajfksajfkajfkajfkajskfsaknbn3jk12j3kl1j4k1j313j00dfjsafjasfjaskjfkj1k3jk123j12k3jkfja0dsfas0fas0fa0fasnvnsjafajsfksajkfajsfkjsakfj1203013ifjafjaskfjkalfjkajfkasfj10230fasjfkasfjaskfd sakjkdfsjakf Kfjaksfjkajfkajfk Sajfdajfkajskfjaskfjaskj (3 rows) postgres=# select * from test_varchar where f1<'k'; f1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- jfkajdkfjanbknfljkljk2j4i24rwi992jklfsjafklajsklfjasdklfjdsaklfjsdklnlnbndfklgjsaklfjsakfjaskjfklsajfklasjfklsajfklsajfklsadj209321094802938590284025802480fjsadfjsakdlfjklsajfklsajfk sajfksajfkajfkajfkajskfsaknbn3jk12j3kl1j4k1j313j00dfjsafjasfjaskjfkj1k3jk123j12k3jkfja0dsfas0fas0fa0fasnvnsjafajsfksajkfajsfkjsakfj1203013ifjafjaskfjkalfjkajfkasfj10230fasjfkasfjaskfd sakjkdfsjakf (1 row)
