Re: Make LC_COLLATE and LC_CTYPE be database level?

[Ming Li <ml...@pivotal.io>]

Hi Paul,

If we make LC_COLLATE and LC_CTYPE be database level, it means we should
tell segment node which COLLATE set each time query runs, so we should push
down query plan with these info each time.

The reason is that we can set different locale for different DB, but on
segment node, there is only one DB called template1 ( here don't consider
dummy DB hcatalog), all query will be run in this DB on segment nodes.

Thanks.

On Mon, Aug 8, 2016 at 3:34 PM, Paul Guo <pa...@gmail.com> wrote:

> 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 <he...@iki.fi>
>
> 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
>
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------------------------------------------------
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------------------------------------------------
>
> -------------
>
>  jfkajdkfjanbknfljkljk2j4i24rwi992jklfsjafklajsklfjasdklfjdsa
> klfjsdklnlnbndfklgjsaklfjsakfjaskjfklsajfklasjfklsajfklsajfk
> lsadj209321094802938590284025802480fjsadfjsakdlfjklsajfklsajfk
>
> sajfksajfkajfkajfkajskfsaknbn3jk12j3kl1j4k1j313j00dfjsafjasf
> jaskjfkj1k3jk123j12k3jkfja0dsfas0fas0fa0fasnvnsjafajsfksajkf
> ajsfkjsakfj1203013ifjafjaskfjkalfjkajfkasfj10230fasjfkasfjaskfd
>
> sakjkdfsjakf
>
>  Kfjaksfjkajfkajfk
>
>  Sajfdajfkajskfjaskfjaskj
>
> (3 rows)
>
>
>
> postgres=# select * from test_varchar where f1<'k';
>
>
>      f1
>
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------------------------------------------------
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------------------------------------------------
>
> -------------
>
>  jfkajdkfjanbknfljkljk2j4i24rwi992jklfsjafklajsklfjasdklfjdsa
> klfjsdklnlnbndfklgjsaklfjsakfjaskjfklsajfklasjfklsajfklsajfk
> lsadj209321094802938590284025802480fjsadfjsakdlfjklsajfklsajfk
>
> sajfksajfkajfkajfkajskfsaknbn3jk12j3kl1j4k1j313j00dfjsafjasf
> jaskjfkj1k3jk123j12k3jkfja0dsfas0fas0fa0fasnvnsjafajsfksajkf
> ajsfkjsakfj1203013ifjafjaskfjkalfjkajfkasfj10230fasjfkasfjaskfd
>
> sakjkdfsjakf
>
> (1 row)
>