How to add an unsupported time zone to the Greenplum Database?
Post date: Oct 05, 2012 3:5:27 PM
Some time zones that the host operating system supports are not supported bythe Greenplum Database.
For example, if the OS time zone is "Asia/Jakarta," the corresponding time zone abbreviation "WIT" is not supported in the Greenplum Database.
gpstop
export TZ=Asia/Jakarta
gpstart
gpadmin=# show timezone;
TimeZone
--------------
Asia/Jakarta
(1 row)
gpadmin=# select * from pg_timezone_names where name='Asia/Jakarta';
name | abbrev | utc_offset | is_dst
--------------+--------+------------+--------
Asia/Jakarta | WIT | 07:00:00 | f
(1 row)
gpadmin=# create table t1(id timestamp with time zone);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gpadmin=# insert into t1 values('2011-11-23 10:33:48.888539 WIT');
ERROR: invalid input syntax for type timestamp with time zone: "2011-11-23 10:33:48.888539 WIT"
Root Cause:
If Greenplum is using an unsupported time zone such as "Asia/Jakarta" and its abbreviation "WIT," any Greenplum internal operation that inserts into gp_configuration_history triggers errors since gp_configuration_history has a column "time" with type"timestamp with time zone."
For example, gprecoverseg may be affected because it will modify gp_segment_configuration and also gp_configuration_history. The modification of the two tables is in the same transaction so if the operation on gp_configuration_history fails, the whole transaction will fail and rollback. As a result, the Greenplum configuration will in an unhealthy status.
Fix
Follow these steps:
Calculate the offset seconds based on the time zone. For example, "Asia/Jakarta" is for GMT+7, so offset seconds = 7 * 3600 seconds = 25200 seconds.
On master, add "<time zone abbreviation> <offset seconds>" into $GPHOME/share/postgresql/timezonesets/Default. For example, add "WIT 25200" into $GPHOME/share/postgresql/timezonesets/Default for "Asia/Jakarta."
Restart the GP cluster.
Example:
[gpadmin@mdw]$ echo "WIT 25200" > $GPHOME/share/postgresql/timezonesets/Default
gpstop -r
gpadmin=# select * from pg_timezone_abbrevs where abbrev='WIT';
abbrev | utc_offset | is_dst
--------+------------+--------
WIT | 07:00:00 | f
(1 row)
gpadmin=# insert into t1 values('2011-11-23 10:33:48.888539 WIT');
INSERT 0 1
Time: 70.492 ms