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:

1. 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.

2. 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."

3. 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