Pivotal Knowledge Base

Follow

LC_COLLATE and LC_CTYPE won't work after setting them through the hawq init

Environment

Product Version
Pivotal HDB  2.0, 2.0.1

Symptom

The HDB initialized with Unicode locale shows -

1. The default value of 'C' for LC_CTYPE & LC_COLLATE settings in pg_settings catalog table.
2. Incorrect sort result over Unicode characters - this is due to LC_COLLATE having the default value of 'C' internally.

This problem wasn't there with previous versions of HDB 1.2.x or 1.3.x.

Example - 

$ hawq init cluster --locale ja_JP.utf8 --lc-collate ja_JP.utf8 --lc-ctype ja_JP.utf8 --lc-messages ja_JP.utf8 --lc-monetary ja_JP.utf8 --lc-numeric ja_JP.utf8 --lc-time ja_JP.utf8
:
=# SELECT name, setting from pg_settings where name like 'lc%';
name | setting
-------------+------------
lc_collate | C
lc_ctype | C
lc_messages | ja_JP.utf8
lc_monetary | ja_JP.utf8
lc_numeric | ja_JP.utf8
lc_time | ja_JP.utf8
(6 rows)

$ cat sort.sql ## Prepare Test queries
DROP TABLE IF EXISTS t;
CREATE TABLE t (id INT, name TEXT);
INSERT INTO t VALUES (1, 'ウ');
INSERT INTO t VALUES (2, 'チ');
INSERT INTO t VALUES (3, 'abd');
INSERT INTO t VALUES (4, 'DEF');
INSERT INTO t VALUES (5, 'ghi');
SELECT * FROM t ORDER BY name;

$ psql postgres -f sort.sql ## It returns incorrect sort result
psql:sort2.sql:1: NOTICE: table "t" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
id | name
----+------
1 | ウ
3 | abd
4 | DEF
5 | ghi
2 | チ
(5 rows)

Cause

This is a regression code issue introduced since HDB 2.0.

Resolution

A permanent fix has been introduced 2.1.0.

Workaround

Add following line to the /usr/local/hawq/greenplum_path.sh of all nodes(master & segment nodes) and restart the HDB. 

unset LC_ALL
unset LC_COLLATE
export LANG='Your_Own_Unicode_Locale'

Please note that this workaround doesn't solve the display issue of pg_settings table, which will be solved in HDB 2.1.0. The display issue doesn't affect the sort operation & result.

Example - 

$ cat /usr/local/hawq/greenplum_path.sh
GPHOME=/usr/local/hawq-2.0.0.0
:
:
export HAWQSITE_CONF
export PYTHONHOME=$GPHOME/ext/python
unset LC_ALL
unset LC_COLLATE
export LANG='ja_JP.utf8' ### <<< Add your locale setting here.
$ source /usr/local/hawq/greenplum_path.sh
$ locale
LANG=ja_JP.utf8
LC_CTYPE="ja_JP.utf8"
LC_NUMERIC="ja_JP.utf8"
LC_TIME="ja_JP.utf8"
LC_COLLATE="ja_JP.utf8"
:
:
LC_ALL=

$ hawq stop cluster
$ hawq start cluster
$ psql postgres -f sort.sql
psql:sort2.sql:1: NOTICE: table "t" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
id | name
----+------
4 | DEF
3 | abd
5 | ghi
1 | ウ
2 | チ
(5 rows)



 

Comments

Powered by Zendesk