Pivotal Knowledge Base

Follow

lc_numeric Run-Time Configuration Parameter Produces Unexpected Results in Pivotal Greenplum

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.8.1 and older
OS RHEL 6.x

Symptom

When using the SET command to change the run-time configuration parameter lc_numeric to change the locale for number formatting it will intermittently return results as if the SET command was never issued.

Description:

Create a simple table with a 1,1 value in it that we will change based on lc_numeric. Default we are expecting will be 11 but 1.1 under de_DE

gpadmin=# \d tbl_lc_numeric_test
     Table "public.tbl_lc_numeric_test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 a      | text                  |
 s      | character varying(50) |
Distributed by: (a)

gpadmin=# select * from tbl_lc_numeric_test;
  a  |     s
-----+-----------
 3   | blablabla
 1,1 | bla
 2   | blabla
(3 rows)

Review the below session, with time stamps, showing the activity suddenly change:

gpadmin=# set lc_numeric='de_DE.utf8';                                          SET
gpadmin=# show lc_numeric
gpadmin-# ;
 lc_numeric
------------
 de_DE.utf8
(1 row)

gpadmin=# \echo `date`                                                          Thu Apr 21 10:05:00 PDT 2016
gpadmin=# select to_number(a,'999999D99999')::numeric(10,5), s from tbl_lc_numeric_test;
 to_number |     s
-----------+-----------
   1.10000 | bla
   3.00000 | blablabla
   2.00000 | blabla
(3 rows)

gpadmin=# \echo `date`                                                          Thu Apr 21 10:05:07 PDT 2016
gpadmin=# select to_number(a,'999999D99999')::numeric(10,5), s from tbl_lc_numeric_test;
 to_number |     s
-----------+-----------
   2.00000 | blabla
   1.10000 | bla
   3.00000 | blablabla
(3 rows)

gpadmin=# \echo `date`                                                          Thu Apr 21 10:05:12 PDT 2016
gpadmin=# select to_number(a,'999999D99999')::numeric(10,5), s from tbl_lc_numeric_test;
 to_number |     s
-----------+-----------
   3.00000 | blablabla
   2.00000 | blabla
   1.10000 | bla
(3 rows)

gpadmin=# \echo `date`                                                          Thu Apr 21 10:05:30 PDT 2016
gpadmin=# select to_number(a,'999999D99999')::numeric(10,5), s from tbl_lc_numeric_test;
 to_number |     s
-----------+-----------
   3.00000 | blablabla
   2.00000 | blabla
  11.00000 | bla
(3 rows)

Cause 

This issue is caused by a software defect and has already been fixed per the Release Notes of 4.3.8.2. See the list of resolved issues under Resolved Issues in Greenplum Database 4.3.8.x section (internal reference: MPP-26325).

RCA 

lc_numeric is missing a GUC_GPDB_ADOPT parameter so it gets reset after about 18 seconds as its options are overwritten by other query executor processes.

Resolution

This issue is fixed in GPDB 4.3.8.2. Upgrading to this version or higher will resolve this issue.

Comments

Powered by Zendesk