Re: Australian timezone configure option - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: Australian timezone configure option
Date
Msg-id 200106131716.f5DHGpH21047@candle.pha.pa.us
Whole thread Raw
In response to Re: Australian timezone configure option  (Chris Dunlop <chris@onthe.net.au>)
Responses Re: Australian timezone configure option  (Chris Dunlop <chris@onthe.net.au>)
List pgsql-patches
> On Mon, Jun 11, 2001 at 11:53:59PM -0400, Bruce Momjian wrote:
> > > Hi,
> > >
> > > Being in Australia, it's always been a minor pain building the support
> > > for Australian timezone rules by defining USE_AUSTRALIAN_RULES to the
> > > compiler.  Not to mention the not inconsiderable pain involved in pawing
> > > through the code and documentation trying to work out why the timezones
> > > were wrong in the first place.
> >
> > OK, this patch makes Australian_timezones a GUC option.  It can be set
> > anytime in psql.  The code uses a static variable to check if the GUC
> > setting has changed and adjust the C struct accordingly.  I have also
> > added code to allow the regression tests to pass even if postgresql.conf
> > has australian_timezones defined.

Here is a new version of the patch.  Tom added callbacks to GUC boolean
variables so I was able to make a separate Australian lookup table and
clear the cache if anyone changes the setting.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.67
diff -c -r1.67 runtime.sgml
*** doc/src/sgml/runtime.sgml    2001/05/17 17:44:17    1.67
--- doc/src/sgml/runtime.sgml    2001/06/13 17:11:04
***************
*** 1201,1206 ****
--- 1201,1217 ----
        </listitem>
       </varlistentry>

+       <term>AUSTRALIAN_TIMEZONES (<type>bool</type>)</term>
+       <listitem>
+        <para>
+         If set to true, <literal>CST</literal>, <literal>EST</literal>,
+         and <literal>SAT</literal> are interpreted as Australian
+         timezones rather than as North American Central/Eastern
+         Timezones and Saturday. The default is false.
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry>
        <indexterm>
         <primary>SSL</primary>
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.64
diff -c -r1.64 datetime.c
*** src/backend/utils/adt/datetime.c    2001/05/03 22:53:07    1.64
--- src/backend/utils/adt/datetime.c    2001/06/13 17:11:08
***************
*** 22,27 ****
--- 22,28 ----
  #include <limits.h>

  #include "miscadmin.h"
+ #include "utils/guc.h"
  #include "utils/datetime.h"

  static int DecodeNumber(int flen, char *field,
***************
*** 36,42 ****
  static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
  static int    DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);

- #define USE_DATE_CACHE 1
  #define ROUND_ALL 0

  static int    DecodePosixTimezone(char *str, int *val);
--- 37,42 ----
***************
*** 117,127 ****
      {"cdt", DTZ, NEG(30)},        /* Central Daylight Time */
      {"cet", TZ, 6},                /* Central European Time */
      {"cetdst", DTZ, 12},        /* Central European Dayl.Time */
- #if USE_AUSTRALIAN_RULES
-     {"cst", TZ, 63},            /* Australia Eastern Std Time */
- #else
      {"cst", TZ, NEG(36)},        /* Central Standard Time */
- #endif
      {DCURRENT, RESERV, DTK_CURRENT},    /* "current" is always now */
      {"dec", MONTH, 12},
      {"december", MONTH, 12},
--- 117,123 ----
***************
*** 134,144 ****
      {"eet", TZ, 12},            /* East. Europe, USSR Zone 1 */
      {"eetdst", DTZ, 18},        /* Eastern Europe */
      {EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */
- #if USE_AUSTRALIAN_RULES
-     {"est", TZ, 60},            /* Australia Eastern Std Time */
- #else
      {"est", TZ, NEG(30)},        /* Eastern Standard Time */
- #endif
      {"feb", MONTH, 2},
      {"february", MONTH, 2},
      {"fri", DOW, 5},
--- 130,136 ----
***************
*** 199,209 ****
      {"pst", TZ, NEG(48)},        /* Pacific Standard Time */
      {"sadt", DTZ, 63},            /* S. Australian Dayl. Time */
      {"sast", TZ, 57},            /* South Australian Std Time */
- #if USE_AUSTRALIAN_RULES
-     {"sat", TZ, 57},
- #else
      {"sat", DOW, 6},
- #endif
      {"saturday", DOW, 6},
      {"sep", MONTH, 9},
      {"sept", MONTH, 9},
--- 191,197 ----
***************
*** 247,252 ****
--- 235,250 ----

  static unsigned int szdatetktbl = sizeof datetktbl / sizeof datetktbl[0];

+ /* Used for SET australian_timezones to override North American ones */
+ static datetkn australian_datetktbl[] = {
+     {"cst", TZ, 63},            /* Australia Eastern Std Time */
+     {"est", TZ, 60},            /* Australia Eastern Std Time */
+     {"sat", TZ, 57},
+ };
+
+ static unsigned int australian_szdatetktbl = sizeof australian_datetktbl /
+                                              sizeof australian_datetktbl[0];
+
  static datetkn deltatktbl[] = {
  /*        text            token    lexval */
      {"@", IGNORE, 0},            /* postgres relative time prefix */
***************
*** 327,339 ****

  static unsigned int szdeltatktbl = sizeof deltatktbl / sizeof deltatktbl[0];

- #if USE_DATE_CACHE
  datetkn    *datecache[MAXDATEFIELDS] = {NULL};

  datetkn    *deltacache[MAXDATEFIELDS] = {NULL};

- #endif
-

  /*
   * Calendar time to Julian date conversions.
--- 325,334 ----
***************
*** 1618,1635 ****
      int            type;
      datetkn    *tp;

- #if USE_DATE_CACHE
      if ((datecache[field] != NULL)
          && (strncmp(lowtoken, datecache[field]->token, TOKMAXLEN) == 0))
          tp = datecache[field];
      else
      {
! #endif
!         tp = datebsearch(lowtoken, datetktbl, szdatetktbl);
! #if USE_DATE_CACHE
      }
      datecache[field] = tp;
- #endif
      if (tp == NULL)
      {
          type = IGNORE;
--- 1613,1631 ----
      int            type;
      datetkn    *tp;

      if ((datecache[field] != NULL)
          && (strncmp(lowtoken, datecache[field]->token, TOKMAXLEN) == 0))
          tp = datecache[field];
      else
      {
!         tp = NULL;
!         if (Australian_timezones)
!             tp = datebsearch(lowtoken, australian_datetktbl,
!                                        australian_szdatetktbl);
!         if (!tp)
!             tp = datebsearch(lowtoken, datetktbl, szdatetktbl);
      }
      datecache[field] = tp;
      if (tp == NULL)
      {
          type = IGNORE;
***************
*** 1937,1954 ****
      int            type;
      datetkn    *tp;

- #if USE_DATE_CACHE
      if ((deltacache[field] != NULL)
          && (strncmp(lowtoken, deltacache[field]->token, TOKMAXLEN) == 0))
          tp = deltacache[field];
      else
      {
- #endif
          tp = datebsearch(lowtoken, deltatktbl, szdeltatktbl);
- #if USE_DATE_CACHE
      }
      deltacache[field] = tp;
- #endif
      if (tp == NULL)
      {
          type = IGNORE;
--- 1933,1946 ----
***************
*** 2455,2457 ****
--- 2447,2458 ----

      return 0;
  }    /* EncodeTimeSpan() */
+
+
+ void ClearDateCache(bool dummy)
+ {
+     int i;
+
+     for (i=0; i < MAXDATEFIELDS; i++)
+         datecache[i] = NULL;
+ }
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.38
diff -c -r1.38 guc.c
*** src/backend/utils/misc/guc.c    2001/06/12 22:54:06    1.38
--- src/backend/utils/misc/guc.c    2001/06/13 17:11:17
***************
*** 33,38 ****
--- 33,39 ----
  #include "parser/parse_expr.h"
  #include "storage/proc.h"
  #include "tcop/tcopprot.h"
+ #include "utils/datetime.h"


  /* XXX these should be in other modules' header files */
***************
*** 69,74 ****
--- 70,77 ----

  bool        SQL_inheritance = true;

+ bool        Australian_timezones = false;
+
  #ifndef PG_KRB_SRVTAB
  #define PG_KRB_SRVTAB ""
  #endif
***************
*** 229,234 ****
--- 232,240 ----

      {"sql_inheritance", PGC_USERSET, &SQL_inheritance, true, NULL},

+     {"australian_timezones", PGC_USERSET, &Australian_timezones,
+     false, ClearDateCache},
+
      {"fixbtree", PGC_POSTMASTER, &FixBTree, true, NULL},

      {NULL, 0, NULL, false, NULL}
***************
*** 327,334 ****
      DEFAULT_CPU_OPERATOR_COST, 0, DBL_MAX, NULL, NULL},

      {"geqo_selection_bias", PGC_USERSET, &Geqo_selection_bias,
!      DEFAULT_GEQO_SELECTION_BIAS, MIN_GEQO_SELECTION_BIAS,
!      MAX_GEQO_SELECTION_BIAS, NULL, NULL},

      {NULL, 0, NULL, 0.0, 0.0, 0.0, NULL, NULL}
  };
--- 333,340 ----
      DEFAULT_CPU_OPERATOR_COST, 0, DBL_MAX, NULL, NULL},

      {"geqo_selection_bias", PGC_USERSET, &Geqo_selection_bias,
!     DEFAULT_GEQO_SELECTION_BIAS, MIN_GEQO_SELECTION_BIAS,
!     MAX_GEQO_SELECTION_BIAS, NULL, NULL},

      {NULL, 0, NULL, 0.0, 0.0, 0.0, NULL, NULL}
  };
***************
*** 360,367 ****
      "", NULL, NULL},

      {"wal_sync_method", PGC_SIGHUP, &XLOG_sync_method,
!         XLOG_sync_method_default,
!     check_xlog_sync_method, assign_xlog_sync_method},

      {NULL, 0, NULL, NULL, NULL, NULL}
  };
--- 366,373 ----
      "", NULL, NULL},

      {"wal_sync_method", PGC_SIGHUP, &XLOG_sync_method,
!     XLOG_sync_method_default, check_xlog_sync_method,
!     assign_xlog_sync_method},

      {NULL, 0, NULL, NULL, NULL, NULL}
  };
***************
*** 956,961 ****
--- 962,968 ----
          case PGC_BOOL:
              val = *((struct config_bool *) record)->variable ? "on" : "off";
              break;
+
          case PGC_INT:
              snprintf(buffer, sizeof(buffer), "%d",
                       *((struct config_int *) record)->variable);
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.11
diff -c -r1.11 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    2001/05/07 23:32:55    1.11
--- src/backend/utils/misc/postgresql.conf.sample    2001/06/13 17:11:17
***************
*** 85,108 ****


  #
- #    Inheritance
- #
- #sql_inheritance = true
-
-
- #
- #    Deadlock
- #
- #deadlock_timeout = 1000
-
-
- #
- #    Expression Depth Limitation
- #
- #max_expr_depth = 10000 # min 10
-
-
- #
  #    Write-ahead log (WAL)
  #
  #wal_buffers = 8 # min 4
--- 85,90 ----
***************
*** 172,174 ****
--- 154,166 ----
  #trace_lock_oidmin = 16384
  #trace_lock_table = 0
  #endif
+
+
+ #
+ #    Misc
+ #
+ #sql_inheritance = true
+ #australian_timezones = false
+ #deadlock_timeout = 1000
+ #max_expr_depth = 10000 # min 10
+
Index: src/include/utils/datetime.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.18
diff -c -r1.18 datetime.h
*** src/include/utils/datetime.h    2001/05/03 22:53:07    1.18
--- src/include/utils/datetime.h    2001/06/13 17:11:21
***************
*** 182,187 ****
--- 182,188 ----
      char        value;            /* this may be unsigned, alas */
  } datetkn;

+ extern datetkn datetktbl[];

  /* TMODULO()
   * Macro to replace modf(), which is broken on some platforms.
***************
*** 264,269 ****
--- 265,271 ----

  extern int    DecodeSpecial(int field, char *lowtoken, int *val);
  extern int    DecodeUnits(int field, char *lowtoken, int *val);
+ extern void ClearDateCache(bool);

  extern int    j2day(int jd);

Index: src/include/utils/guc.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/utils/guc.h,v
retrieving revision 1.8
diff -c -r1.8 guc.h
*** src/include/utils/guc.h    2001/06/12 22:54:06    1.8
--- src/include/utils/guc.h    2001/06/13 17:11:21
***************
*** 70,74 ****
--- 70,75 ----
  extern bool Show_btree_build_stats;

  extern bool SQL_inheritance;
+ extern bool Australian_timezones;

  #endif     /* GUC_H */
Index: src/test/regress/expected/horology-no-DST-before-1970.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/horology-no-DST-before-1970.out,v
retrieving revision 1.12
diff -c -r1.12 horology-no-DST-before-1970.out
*** src/test/regress/expected/horology-no-DST-before-1970.out    2001/04/06 05:50:25    1.12
--- src/test/regress/expected/horology-no-DST-before-1970.out    2001/06/13 17:11:28
***************
*** 4,9 ****
--- 4,11 ----
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
           Date + Time
  ------------------------------
Index: src/test/regress/expected/horology-solaris-1947.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/horology-solaris-1947.out,v
retrieving revision 1.10
diff -c -r1.10 horology-solaris-1947.out
*** src/test/regress/expected/horology-solaris-1947.out    2001/04/06 05:50:25    1.10
--- src/test/regress/expected/horology-solaris-1947.out    2001/06/13 17:11:29
***************
*** 4,9 ****
--- 4,11 ----
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
           Date + Time
  ------------------------------
Index: src/test/regress/expected/horology.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/horology.out,v
retrieving revision 1.23
diff -c -r1.23 horology.out
*** src/test/regress/expected/horology.out    2001/04/06 05:50:25    1.23
--- src/test/regress/expected/horology.out    2001/06/13 17:11:31
***************
*** 4,9 ****
--- 4,11 ----
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
           Date + Time
  ------------------------------
Index: src/test/regress/expected/timestamp.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
retrieving revision 1.12
diff -c -r1.12 timestamp.out
*** src/test/regress/expected/timestamp.out    2001/05/03 19:00:37    1.12
--- src/test/regress/expected/timestamp.out    2001/06/13 17:11:37
***************
*** 4,9 ****
--- 4,11 ----
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
   True
  ------
Index: src/test/regress/sql/horology.sql
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/sql/horology.sql,v
retrieving revision 1.14
diff -c -r1.14 horology.sql
*** src/test/regress/sql/horology.sql    2001/04/06 05:50:29    1.14
--- src/test/regress/sql/horology.sql    2001/06/13 17:11:38
***************
*** 1,10 ****
  --
  -- HOROLOGY
  --
-
  --
  -- date, time arithmetic
  --

  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";

--- 1,11 ----
  --
  -- HOROLOGY
  --
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';

  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";

Index: src/test/regress/sql/timestamp.sql
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/sql/timestamp.sql,v
retrieving revision 1.7
diff -c -r1.7 timestamp.sql
*** src/test/regress/sql/timestamp.sql    2000/11/25 05:00:33    1.7
--- src/test/regress/sql/timestamp.sql    2001/06/13 17:11:38
***************
*** 1,10 ****
  --
  -- DATETIME
  --
-
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26

  SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
  SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";
--- 1,11 ----
  --
  -- DATETIME
  --
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26
+ -- needed so tests pass
+ SET australian_timezones = 'off';

  SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
  SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";

pgsql-patches by date:

Previous
From: Marko Kreen
Date:
Subject: Re: reset all update
Next
From: Bruce Momjian
Date:
Subject: Re: Patch to include PAM support...