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