Thread: Australian timezone configure option
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. This patch makes it a configure option - much easier to use, and much more obvious for the other Aussies who keep wondering why their timezones are all messed up... Obviously 'autoconf' needs to be run after applying the patch. Cheers, Chris, OnTheNet --- postgresql-7.1.2/configure.in.orig Fri May 11 11:34:39 2001 +++ postgresql-7.1.2/configure.in Thu May 31 23:54:27 2001 @@ -150,6 +150,16 @@ # +# Australian timezone (--enable-australian-tz) +# +AC_MSG_CHECKING([whether to build with Australian timezone rules]) +PGAC_ARG_BOOL(enable, australian-tz, no, [ --enable-australian-tz enable Australian timezone rules ], + [AC_DEFINE([USE_AUSTRALIAN_RULES], 1, + [Set to 1 if you want Australian timezone rules (--enable-australian-tz)])]) +AC_MSG_RESULT([$enable_australian_tz]) + + +# # Locale (--enable-locale) # AC_MSG_CHECKING([whether to build with locale support])
Chris Dunlop <chris@onthe.net.au> writes:
> This patch makes it a configure option - much easier to use,
Seems like a good idea, but that patch couldn't possibly work as-is.
Where's the config.h.in entry? Have you tested it?
regards, tom lane
Chris Dunlop <chris@onthe.net.au> writes:
> Oops... overzealeous trimming of the actual patch file I generated
> which included all the changes to 'configure' generated by autoconf.
> Patch including config.h.in changes below.
That looks better.
Could we also trouble you for documentation patches? IIRC, there's a
list of all interesting configure options somewhere in the
administrator's guide.
regards, tom lane
Tom Lane wrote:
> Could we also trouble you for documentation patches? IIRC, there's a
> list of all interesting configure options somewhere in the
> administrator's guide.
Documentation ? We don't need no steenking documentation!...
diff -ur postgresql-7.1.2.orig/doc/src/sgml/installation.sgml postgresql-7.1.2/doc/src/sgml/installation.sgml
--- postgresql-7.1.2.orig/doc/src/sgml/installation.sgml Tue May 15 01:11:31 2001
+++ postgresql-7.1.2/doc/src/sgml/installation.sgml Wed Jun 6 10:35:30 2001
@@ -462,6 +462,20 @@
</varlistentry>
<varlistentry>
+ <term>--enable-australian-tz</term>
+ <listitem>
+ <para>
+ Enables Australian timezone support. This changes the interpretation
+ of timezones in input date/time strings from US-centric to
+ Australian-centric. Specifically, 'EST' is changed from GMT-5 (US
+ Eastern Standard Time) to GMT+10 (Australian Eastern Standard Time)
+ and 'CST' is changed from GMT-5:30 (US Central Standard Time) to
+ GMT+10:30 (Australian Central Standard Time).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term>--enable-locale</term>
<listitem>
<para>
On Mon, Jun 04, 2001 at 10:25:02AM -0400, Tom Lane wrote:
> Chris Dunlop <chris@onthe.net.au> writes:
> > This patch makes it a configure option - much easier to use,
>
> Seems like a good idea, but that patch couldn't possibly work as-is.
> Where's the config.h.in entry? Have you tested it?
>
> regards, tom lane
Oops... overzealeous trimming of the actual patch file I generated
which included all the changes to 'configure' generated by autoconf.
Patch including config.h.in changes below.
Test against the unpatched database, local timezone is "Australian EST"
i.e. GMT+10:
$ psql -c "select 'Jun 6 02:34:32 EST 2001'::datetime" template1
?column?
------------------------
2001-06-06 17:34:32+10
(1 row)
Notice the returned time is different to the input time. Against
the patched database:
psql -c "select 'Jun 6 02:34:32 EST 2001'::datetime" template1
?column?
------------------------
2001-06-06 02:34:32+10
(1 row)
Cheers,
Chris,
OnTheNet
diff -ru postgresql-7.1.2.orig/configure.in postgresql-7.1.2/configure.in
--- postgresql-7.1.2.orig/configure.in Fri May 11 11:34:39 2001
+++ postgresql-7.1.2/configure.in Thu May 31 23:54:27 2001
@@ -150,6 +150,16 @@
#
+# Australian timezone (--enable-australian-tz)
+#
+AC_MSG_CHECKING([whether to build with Australian timezone rules])
+PGAC_ARG_BOOL(enable, australian-tz, no, [ --enable-australian-tz enable Australian timezone rules ],
+ [AC_DEFINE([USE_AUSTRALIAN_RULES], 1,
+ [Set to 1 if you want Australian timezone rules (--enable-australian-tz)])])
+AC_MSG_RESULT([$enable_australian_tz])
+
+
+#
# Locale (--enable-locale)
#
AC_MSG_CHECKING([whether to build with locale support])
diff -ru postgresql-7.1.2.orig/src/include/config.h.in postgresql-7.1.2/src/include/config.h.in
--- postgresql-7.1.2.orig/src/include/config.h.in Sun Apr 15 08:55:02 2001
+++ postgresql-7.1.2/src/include/config.h.in Thu May 31 23:58:16 2001
@@ -33,6 +33,9 @@
/* A canonical string containing the version number, platform, and C compiler */
#undef PG_VERSION_STR
+/* Set to 1 if you want Australian timezone rules (--enable-australian-tz) */
+#undef USE_AUSTRALIAN_RULES
+
/* Set to 1 if you want LOCALE support (--enable-locale) */
#undef USE_LOCALE
I have decided to make this configurable via postgresql.conf so you don't need separate binaries / configure switch to run in Australia. I will send a patch over for testing. > Tom Lane wrote: > > Could we also trouble you for documentation patches? IIRC, there's a > > list of all interesting configure options somewhere in the > > administrator's guide. > > > Documentation ? We don't need no steenking documentation!... > > > diff -ur postgresql-7.1.2.orig/doc/src/sgml/installation.sgml postgresql-7.1.2/doc/src/sgml/installation.sgml > --- postgresql-7.1.2.orig/doc/src/sgml/installation.sgml Tue May 15 01:11:31 2001 > +++ postgresql-7.1.2/doc/src/sgml/installation.sgml Wed Jun 6 10:35:30 2001 > @@ -462,6 +462,20 @@ > </varlistentry> > > <varlistentry> > + <term>--enable-australian-tz</term> > + <listitem> > + <para> > + Enables Australian timezone support. This changes the interpretation > + of timezones in input date/time strings from US-centric to > + Australian-centric. Specifically, 'EST' is changed from GMT-5 (US > + Eastern Standard Time) to GMT+10 (Australian Eastern Standard Time) > + and 'CST' is changed from GMT-5:30 (US Central Standard Time) to > + GMT+10:30 (Australian Central Standard Time). > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > <term>--enable-locale</term> > <listitem> > <para> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- 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
Hi all, Can we *not* make USE_AUSTRALIAN_RULES into a "configure" option? Seems like this would conflict with Tom's suggestion of making it a GUC option instead. I vote for having it as a GUC option instead. Regards and best wishes, Justin Clift On Wednesday 06 June 2001 10:43, Chris Dunlop wrote: > Tom Lane wrote: > > Could we also trouble you for documentation patches? IIRC, there's a > > list of all interesting configure options somewhere in the > > administrator's guide. > > Documentation ? We don't need no steenking documentation!... > > > diff -ur postgresql-7.1.2.orig/doc/src/sgml/installation.sgml > postgresql-7.1.2/doc/src/sgml/installation.sgml --- > postgresql-7.1.2.orig/doc/src/sgml/installation.sgml Tue May 15 01:11:31 > 2001 +++ postgresql-7.1.2/doc/src/sgml/installation.sgml Wed Jun 6 > 10:35:30 2001 @@ -462,6 +462,20 @@ > </varlistentry> > > <varlistentry> > + <term>--enable-australian-tz</term> > + <listitem> > + <para> > + Enables Australian timezone support. This changes the > interpretation + of timezones in input date/time strings from > US-centric to + Australian-centric. Specifically, 'EST' is changed > from GMT-5 (US + Eastern Standard Time) to GMT+10 (Australian > Eastern Standard Time) + and 'CST' is changed from GMT-5:30 (US > Central Standard Time) to + GMT+10:30 (Australian Central Standard > Time). > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > <term>--enable-locale</term> > <listitem> > <para> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
> On Mon, Jun 11, 2001 at 06:52:52PM -0400, Bruce Momjian wrote: > > > > I have decided to make this configurable via postgresql.conf so you > > don't need separate binaries / configure switch to run in Australia. I > > will send a patch over for testing. > > > Great, that's better than a configure option... > I thought so. -- 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
> 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.
test=> select datetime('2001-01-01 00:00:00 EST');
timestamp
------------------------
2001-01-01 00:00:00-05
(1 row)
test=> set australian_timezones = true;
SET VARIABLE
test=> select datetime('2001-01-01 00:00:00 EST');
timestamp
------------------------
2000-12-31 09:00:00-05
(1 row)
test=> reset all;
RESET VARIABLE
test=> select datetime('2001-01-01 00:00:00 EST');
timestamp
------------------------
2001-01-01 00:00:00-05
(1 row)
--
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: 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/12 03:52:10
***************
*** 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,
***************
*** 35,40 ****
--- 36,42 ----
static int DecodeTimezone(char *str, int *tzp);
static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
+ static void CheckAustralianTimezones(int field);
#define USE_DATE_CACHE 1
#define ROUND_ALL 0
***************
*** 85,91 ****
* entries by 10 and truncate the text field at MAXTOKLEN characters.
* the text field is not guaranteed to be NULL-terminated.
*/
! static datetkn datetktbl[] = {
/* text token lexval */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{"acsst", DTZ, 63}, /* Cent. Australia */
--- 87,93 ----
* entries by 10 and truncate the text field at MAXTOKLEN characters.
* the text field is not guaranteed to be NULL-terminated.
*/
! datetkn datetktbl[] = {
/* text token lexval */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{"acsst", DTZ, 63}, /* Cent. Australia */
***************
*** 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},
--- 119,125 ----
{"cdt", DTZ, NEG(30)}, /* Central Daylight Time */
{"cet", TZ, 6}, /* Central European Time */
{"cetdst", DTZ, 12}, /* Central European Dayl.Time */
! {"cst", TZ, NEG(36)}, /* Central Standard Time, may be Australian */
{DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */
{"dec", MONTH, 12},
{"december", MONTH, 12},
***************
*** 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},
--- 132,138 ----
{"eet", TZ, 12}, /* East. Europe, USSR Zone 1 */
{"eetdst", DTZ, 18}, /* Eastern Europe */
{EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */
! {"est", TZ, NEG(30)}, /* Eastern Standard Time, may be Australian */
{"feb", MONTH, 2},
{"february", MONTH, 2},
{"fri", DOW, 5},
***************
*** 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},
--- 193,199 ----
{"pst", TZ, NEG(48)}, /* Pacific Standard Time */
{"sadt", DTZ, 63}, /* S. Australian Dayl. Time */
{"sast", TZ, 57}, /* South Australian Std Time */
! {"sat", DOW, 6}, /* may be changed to Australian */
{"saturday", DOW, 6},
{"sep", MONTH, 9},
{"sept", MONTH, 9},
***************
*** 1618,1623 ****
--- 1608,1615 ----
int type;
datetkn *tp;
+ CheckAustralianTimezones(field);
+
#if USE_DATE_CACHE
if ((datecache[field] != NULL)
&& (strncmp(lowtoken, datecache[field]->token, TOKMAXLEN) == 0))
***************
*** 2455,2457 ****
--- 2447,2495 ----
return 0;
} /* EncodeTimeSpan() */
+
+
+ static void CheckAustralianTimezones(int field)
+ {
+ datetkn *tp;
+ int prev_Australian_timezones = false; /* structure preloaded as false */
+
+ if (Australian_timezones != prev_Australian_timezones)
+ {
+ #if USE_DATE_CACHE
+ datecache[field] = NULL;
+ #endif
+ /* CST */
+ tp = datebsearch("cst", datetktbl, szdatetktbl);
+ Assert(tp);
+ tp->type = TZ;
+ if (!Australian_timezones)
+ tp->value = NEG(36); /* Central Standard Time */
+ else
+ tp->value = 63; /* Australia Eastern Std Time */
+
+ /* EST */
+ tp = datebsearch("est", datetktbl, szdatetktbl);
+ Assert(tp);
+ tp->type = TZ;
+ if (!Australian_timezones)
+ tp->value = NEG(30); /* Eastern Standard Time */
+ else
+ tp->value = 60; /* Australia Eastern Std Time */
+
+ /* SAT */
+ tp = datebsearch("sat", datetktbl, szdatetktbl);
+ Assert(tp);
+ if (!Australian_timezones)
+ {
+ tp->type = DOW;
+ tp->value = 6;
+ }
+ else
+ {
+ tp->type = TZ;
+ tp->value = 57;
+ }
+ prev_Australian_timezones = Australian_timezones;
+ }
+ }
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.37
diff -c -r1.37 guc.c
*** src/backend/utils/misc/guc.c 2001/06/07 04:50:57 1.37
--- src/backend/utils/misc/guc.c 2001/06/12 03:52:11
***************
*** 71,76 ****
--- 71,78 ----
bool SQL_inheritance = true;
+ bool Australian_timezones = false;
+
#ifndef PG_KRB_SRVTAB
#define PG_KRB_SRVTAB ""
#endif
***************
*** 222,227 ****
--- 224,230 ----
{"show_source_port", PGC_SIGHUP, &ShowPortNumber, false},
{"sql_inheritance", PGC_USERSET, &SQL_inheritance, true},
+ {"australian_timezones", PGC_USERSET, &Australian_timezones, false},
{"fixbtree", PGC_POSTMASTER, &FixBTree, true},
***************
*** 880,885 ****
--- 883,889 ----
case PGC_BOOL:
val = *((struct config_bool *) record)->variable ? "on" : "off";
break;
+
case PGC_INT:
snprintf(buffer, 256, "%d", *((struct config_int *) record)->variable);
val = buffer;
***************
*** 955,961 ****
elog(FATAL, "out of memory");
}
else
! /* no equal sign in string */
{
*name = strdup(string);
if (!*name)
--- 959,965 ----
elog(FATAL, "out of memory");
}
else
! /* no equal sign in string */
{
*name = strdup(string);
if (!*name)
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/12 03:52:11
***************
*** 172,174 ****
--- 172,180 ----
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#endif
+
+
+ #
+ # Lock Tracing
+ #
+ #australian_timezones = false
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/12 03:52:12
***************
*** 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.
Index: src/include/utils/guc.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/utils/guc.h,v
retrieving revision 1.7
diff -c -r1.7 guc.h
*** src/include/utils/guc.h 2001/06/07 04:50:57 1.7
--- src/include/utils/guc.h 2001/06/12 03:52:12
***************
*** 68,72 ****
--- 68,73 ----
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/12 03:52:16
***************
*** 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/12 03:52:17
***************
*** 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/12 03:52:19
***************
*** 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/12 03:52:19
***************
*** 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/12 03:52:19
***************
*** 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/12 03:52:20
***************
*** 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";
> > 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 diff for the documentation. -- 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: 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 *** runtime.sgml 2001/05/17 17:44:17 1.67 --- runtime.sgml 2001/06/12 04:45:40 *************** *** 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>
> > 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. > > > Your patch had one reject against 7.1.2 (a single blank line in guc.c), > but it works for me once that was fixed. > > Below is the patch against 7.1.2 I generated from your cvs patch. We will not be adding this to 7.1.X though you are free to use it in that version. It will appear in 7.2. > I guess some documentation would be nice... Done. Thanks for testing it. -- 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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.
This is a horrid approach. What if you get an error partway through
scribbling on the static table? Then you've got inconsistent data.
Nor do I much care for having to execute a check subroutine before any
use of the lookup table (quite aside from speed, are you sure it's being
called before *every* use of the table? how will you make sure that
people remember to call it when they add new routines that use the
table?). If you're going to scribble on the table, ISTM you should
drive that off an assignment-hook callback from the GUC stuff.
Besides which, you forgot to mark the control variable static...
so it doesn't actually reflect the state of the table.
It would be a lot cleaner to extend the lookup table structure so that
you don't need to change the table contents to track the Aussie-rules
setting.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > 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. > > This is a horrid approach. What if you get an error partway through > scribbling on the static table? Then you've got inconsistent data. I have set the variable to -1 on entry so it will reload on failure, though it is only doing C lookups on a static table so it is hard to see how it would fail. > Nor do I much care for having to execute a check subroutine before any > use of the lookup table (quite aside from speed, are you sure it's being > called before *every* use of the table? how will you make sure that > people remember to call it when they add new routines that use the > table?). If you're going to scribble on the table, ISTM you should > drive that off an assignment-hook callback from the GUC stuff. But we don't have such hooks so I did it with as little code as possible. The table itself is 'static' so it is only called in this function. (The old patch had the static removed because I thought I was going to have to do this stuff in the guc files but now it is all in the same file.) > Besides which, you forgot to mark the control variable static... > so it doesn't actually reflect the state of the table. Fixed. I had that in an ealier version but forgot to add it when I moved it out to a separate function. > It would be a lot cleaner to extend the lookup table structure so that > you don't need to change the table contents to track the Aussie-rules > setting. Yes, when we do that we can remove the call and call it instead from the GUC setting. Here is a new version of the patch. I found I didn't need to clear the date cache. -- 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/12 15:02:15 *************** *** 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/12 15:02:17 *************** *** 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, *************** *** 35,40 **** --- 36,42 ---- static int DecodeTimezone(char *str, int *tzp); static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel); static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm); + static void CheckAustralianTimezones(int field); #define USE_DATE_CACHE 1 #define ROUND_ALL 0 *************** *** 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}, --- 119,125 ---- {"cdt", DTZ, NEG(30)}, /* Central Daylight Time */ {"cet", TZ, 6}, /* Central European Time */ {"cetdst", DTZ, 12}, /* Central European Dayl.Time */ ! {"cst", TZ, NEG(36)}, /* Central Standard Time, may be Australian */ {DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */ {"dec", MONTH, 12}, {"december", MONTH, 12}, *************** *** 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}, --- 132,138 ---- {"eet", TZ, 12}, /* East. Europe, USSR Zone 1 */ {"eetdst", DTZ, 18}, /* Eastern Europe */ {EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */ ! {"est", TZ, NEG(30)}, /* Eastern Standard Time, may be Australian */ {"feb", MONTH, 2}, {"february", MONTH, 2}, {"fri", DOW, 5}, *************** *** 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}, --- 193,199 ---- {"pst", TZ, NEG(48)}, /* Pacific Standard Time */ {"sadt", DTZ, 63}, /* S. Australian Dayl. Time */ {"sast", TZ, 57}, /* South Australian Std Time */ ! {"sat", DOW, 6}, /* may be changed to Australian */ {"saturday", DOW, 6}, {"sep", MONTH, 9}, {"sept", MONTH, 9}, *************** *** 1618,1623 **** --- 1608,1615 ---- int type; datetkn *tp; + CheckAustralianTimezones(field); + #if USE_DATE_CACHE if ((datecache[field] != NULL) && (strncmp(lowtoken, datecache[field]->token, TOKMAXLEN) == 0)) *************** *** 2455,2457 **** --- 2447,2494 ---- return 0; } /* EncodeTimeSpan() */ + + + static void CheckAustralianTimezones(int field) + { + datetkn *tp; + /* structure preloaded as false */ + static int prev_Australian_timezones = -1; + + if (Australian_timezones != prev_Australian_timezones) + { + prev_Australian_timezones = -1; /* in case it fails, force reload */ + /* CST */ + tp = datebsearch("cst", datetktbl, szdatetktbl); + Assert(tp); + tp->type = TZ; + if (!Australian_timezones) + tp->value = NEG(36); /* Central Standard Time */ + else + tp->value = 63; /* Australia Eastern Std Time */ + + /* EST */ + tp = datebsearch("est", datetktbl, szdatetktbl); + Assert(tp); + tp->type = TZ; + if (!Australian_timezones) + tp->value = NEG(30); /* Eastern Standard Time */ + else + tp->value = 60; /* Australia Eastern Std Time */ + + /* SAT */ + tp = datebsearch("sat", datetktbl, szdatetktbl); + Assert(tp); + if (!Australian_timezones) + { + tp->type = DOW; + tp->value = 6; + } + else + { + tp->type = TZ; + tp->value = 57; + } + prev_Australian_timezones = Australian_timezones; + } + } Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/misc/guc.c,v retrieving revision 1.37 diff -c -r1.37 guc.c *** src/backend/utils/misc/guc.c 2001/06/07 04:50:57 1.37 --- src/backend/utils/misc/guc.c 2001/06/12 15:02:17 *************** *** 71,76 **** --- 71,78 ---- bool SQL_inheritance = true; + bool Australian_timezones = false; + #ifndef PG_KRB_SRVTAB #define PG_KRB_SRVTAB "" #endif *************** *** 222,227 **** --- 224,230 ---- {"show_source_port", PGC_SIGHUP, &ShowPortNumber, false}, {"sql_inheritance", PGC_USERSET, &SQL_inheritance, true}, + {"australian_timezones", PGC_USERSET, &Australian_timezones, false}, {"fixbtree", PGC_POSTMASTER, &FixBTree, true}, *************** *** 880,885 **** --- 883,889 ---- case PGC_BOOL: val = *((struct config_bool *) record)->variable ? "on" : "off"; break; + case PGC_INT: snprintf(buffer, 256, "%d", *((struct config_int *) record)->variable); val = buffer; *************** *** 955,961 **** elog(FATAL, "out of memory"); } else ! /* no equal sign in string */ { *name = strdup(string); if (!*name) --- 959,965 ---- elog(FATAL, "out of memory"); } else ! /* no equal sign in string */ { *name = strdup(string); if (!*name) 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/12 15:02: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/12 15:02:18 *************** *** 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. Index: src/include/utils/guc.h =================================================================== RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/utils/guc.h,v retrieving revision 1.7 diff -c -r1.7 guc.h *** src/include/utils/guc.h 2001/06/07 04:50:57 1.7 --- src/include/utils/guc.h 2001/06/12 15:02:18 *************** *** 68,72 **** --- 68,73 ---- 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/12 15:02:24 *************** *** 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/12 15:02:24 *************** *** 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/12 15:02:25 *************** *** 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/12 15:02:26 *************** *** 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/12 15:02:26 *************** *** 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/12 15:02:26 *************** *** 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";
> (moved to -hackers list, since this is a *feature change* which should > not just be discussed in -patches) > > >>I have decided to make this configurable via postgresql.conf so you > >>don't need separate binaries / configure switch to run in Australia. > >>I will send a patch over for testing. > > We will not be adding this to 7.1.X though you are free to use it in > > that version. It will appear in 7.2. > > Um, er... > > I'm not particularly happy about the solution, and would like to not see > it in the main source code without further discussion. Sorry I was out > of town for the extensive two hour discussion on the topic ;) > > One could categorize the "Australian problem" as an example of a > localization, and brute-force calls to work around it are a step in the > wrong direction imho. Particularly since Australia contributes fully 25% > of the time zone information in our lookup table, including multiple > synonyms for several time zones. Just as we might want to send a message > to M$ that their SQL hacks are not acceptable, we should send a message > to Australia that playing fast and loose with time zone names should not > be tolerated. Hmm, and while we are at it we should do something about > world hunger and arms race issues. Patches coming soon ;) ;) > > OK, those last few sentences were not serious. But, I would like a > solution that starts to address long-term issues in time zone support. > > Before hacking the rather carefully evolved static tables let's consider > how to support time localization generally (e.g. language-specific names > for months). In the meantime, a compile-time solution for more easily > setting the "CST" interpretation would seem to be an incremental > improvement for "buildability" (and this has already been submitted). > > How about implementing an optional db table-based approach for this > lookup and the other localized info? If it were a compile-time option we > could evaluate the performance impact and allow folks to trade > performance vs features. And perhaps (later, much later. Weeks later... > ;) that choice could be a GUC parameter. Comments? This is way beyond where I want to go with the Australian stuff and I have not seen much demand from users for more than a GUC option. Australians wanted a 'configure' flag, I made it GUC which gets reloaded on first call and can later be assigned to a GUC hook when we get those. -- 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
(moved to -hackers list, since this is a *feature change* which should
not just be discussed in -patches)
>>I have decided to make this configurable via postgresql.conf so you
>>don't need separate binaries / configure switch to run in Australia.
>>I will send a patch over for testing.
> We will not be adding this to 7.1.X though you are free to use it in
> that version. It will appear in 7.2.
Um, er...
I'm not particularly happy about the solution, and would like to not see
it in the main source code without further discussion. Sorry I was out
of town for the extensive two hour discussion on the topic ;)
One could categorize the "Australian problem" as an example of a
localization, and brute-force calls to work around it are a step in the
wrong direction imho. Particularly since Australia contributes fully 25%
of the time zone information in our lookup table, including multiple
synonyms for several time zones. Just as we might want to send a message
to M$ that their SQL hacks are not acceptable, we should send a message
to Australia that playing fast and loose with time zone names should not
be tolerated. Hmm, and while we are at it we should do something about
world hunger and arms race issues. Patches coming soon ;) ;)
OK, those last few sentences were not serious. But, I would like a
solution that starts to address long-term issues in time zone support.
Before hacking the rather carefully evolved static tables let's consider
how to support time localization generally (e.g. language-specific names
for months). In the meantime, a compile-time solution for more easily
setting the "CST" interpretation would seem to be an incremental
improvement for "buildability" (and this has already been submitted).
How about implementing an optional db table-based approach for this
lookup and the other localized info? If it were a compile-time option we
could evaluate the performance impact and allow folks to trade
performance vs features. And perhaps (later, much later. Weeks later...
;) that choice could be a GUC parameter. Comments?
- Thomas
On Tue, Jun 12, 2001 at 12:25:11PM +1000, Justin Clift wrote: > Hi all, > > Can we *not* make USE_AUSTRALIAN_RULES into a "configure" option? > > Seems like this would conflict with Tom's suggestion of making it a GUC > option instead. > > I vote for having it as a GUC option instead. Yup, I think Bruce is actually making it a GUC option. > > Regards and best wishes, > > Justin Clift >
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Here is a new version of the patch.
>
> It's still horridly ugly. Why not put the three Aussie-specific entries
> in a separate aussie_datetktbl array, and have the lookup look like
>
> if (Australian_timezones)
> {
> tp = datebsearch(lowtoken, aussie_datetktbl, sz_aussie_datetktbl);
> if (tp == NULL)
> tp = datebsearch(lowtoken, datetktbl, szdatetktbl);
> }
> else
> tp = datebsearch(lowtoken, datetktbl, szdatetktbl);
>
> instead of modifying the lookup table on the fly.
I thought about that but the use of the cache had me concerned,
particularly for modularity. This way there is only one table to query
with one call to the bsearch function.
However, I can go with the two-table approach if people prefer it.
> > I found I didn't need to clear the date cache.
>
> Hmm, are you sure about that? I'm not.
I checked and it caches a pointer to the struct, not the values
themselves, and we don't change the structure, just the secondary values
and not the key used by the bsearch.
--
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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Here is a new version of the patch.
It's still horridly ugly. Why not put the three Aussie-specific entries
in a separate aussie_datetktbl array, and have the lookup look like
if (Australian_timezones)
{
tp = datebsearch(lowtoken, aussie_datetktbl, sz_aussie_datetktbl);
if (tp == NULL)
tp = datebsearch(lowtoken, datetktbl, szdatetktbl);
}
else
tp = datebsearch(lowtoken, datetktbl, szdatetktbl);
instead of modifying the lookup table on the fly.
> I found I didn't need to clear the date cache.
Hmm, are you sure about that? I'm not.
regards, tom lane
Oops, I see you've just done that, great!
To: Chris Dunlop <chris@onthe.net.au>
cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-patches@postgresql.org (bcc: Chris
Bitmead/ITG/Health)
Subject: Re: [PATCHES] Australian timezone configure option
> On Mon, Jun 11, 2001 at 06:52:52PM -0400, Bruce Momjian wrote:
> >
> > I have decided to make this configurable via postgresql.conf so you
> > don't need separate binaries / configure switch to run in Australia. I
> > will send a patch over for testing.
>
>
> Great, that's better than a configure option...
>
I thought so.
--
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I found I didn't need to clear the date cache.
> >>
> >> Hmm, are you sure about that? I'm not.
>
> > I checked and it caches a pointer to the struct, not the values
> > themselves, and we don't change the structure, just the secondary values
> > and not the key used by the bsearch.
>
> Now I'm going to object LOUDLY. You cannot convince me that the above
> is a good implementation --- it's a complete crock, and will break the
> instant someone looks at it sidewise.
>
> My inclination would actually be to rip out the cache entirely. bsearch
> in a table this size is not so expensive that we need to bypass it, nor
> is it apparent that we are going to see lots of successive lookups for
> the same keyword anyway. How long has that cache been in there, and
> what was the motivation for adding it to begin with?
I see the CACHE coming in with:
1.42 (thomas 16-Feb-00): #define USE_DATE_CACHE 1
--
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
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.
Your patch had one reject against 7.1.2 (a single blank line in guc.c),
but it works for me once that was fixed.
Below is the patch against 7.1.2 I generated from your cvs patch.
I guess some documentation would be nice...
Thanks for your effort!
Cheers,
Chris,
OnTheNet
diff -ru postgresql-7.1.2.orig/src/backend/utils/adt/datetime.c postgresql-7.1.2/src/backend/utils/adt/datetime.c
--- postgresql-7.1.2.orig/src/backend/utils/adt/datetime.c Fri May 4 08:53:07 2001
+++ postgresql-7.1.2/src/backend/utils/adt/datetime.c Tue Jun 12 14:14:38 2001
@@ -22,6 +22,7 @@
#include <limits.h>
#include "miscadmin.h"
+#include "utils/guc.h"
#include "utils/datetime.h"
static int DecodeNumber(int flen, char *field,
@@ -35,6 +36,7 @@
static int DecodeTimezone(char *str, int *tzp);
static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
static int DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
+static void CheckAustralianTimezones(int field);
#define USE_DATE_CACHE 1
#define ROUND_ALL 0
@@ -85,7 +87,7 @@
* entries by 10 and truncate the text field at MAXTOKLEN characters.
* the text field is not guaranteed to be NULL-terminated.
*/
-static datetkn datetktbl[] = {
+datetkn datetktbl[] = {
/* text token lexval */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{"acsst", DTZ, 63}, /* Cent. Australia */
@@ -117,11 +119,7 @@
{"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
+ {"cst", TZ, NEG(36)}, /* Central Standard Time, may be Australian */
{DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */
{"dec", MONTH, 12},
{"december", MONTH, 12},
@@ -134,11 +132,7 @@
{"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
+ {"est", TZ, NEG(30)}, /* Eastern Standard Time, may be Australian */
{"feb", MONTH, 2},
{"february", MONTH, 2},
{"fri", DOW, 5},
@@ -199,11 +193,7 @@
{"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
+ {"sat", DOW, 6}, /* may be changed to Australian */
{"saturday", DOW, 6},
{"sep", MONTH, 9},
{"sept", MONTH, 9},
@@ -1618,6 +1608,8 @@
int type;
datetkn *tp;
+ CheckAustralianTimezones(field);
+
#if USE_DATE_CACHE
if ((datecache[field] != NULL)
&& (strncmp(lowtoken, datecache[field]->token, TOKMAXLEN) == 0))
@@ -2455,3 +2447,49 @@
return 0;
} /* EncodeTimeSpan() */
+
+
+static void CheckAustralianTimezones(int field)
+{
+ datetkn *tp;
+ int prev_Australian_timezones = false; /* structure preloaded as false */
+
+ if (Australian_timezones != prev_Australian_timezones)
+ {
+#if USE_DATE_CACHE
+ datecache[field] = NULL;
+#endif
+ /* CST */
+ tp = datebsearch("cst", datetktbl, szdatetktbl);
+ Assert(tp);
+ tp->type = TZ;
+ if (!Australian_timezones)
+ tp->value = NEG(36); /* Central Standard Time */
+ else
+ tp->value = 63; /* Australia Eastern Std Time */
+
+ /* EST */
+ tp = datebsearch("est", datetktbl, szdatetktbl);
+ Assert(tp);
+ tp->type = TZ;
+ if (!Australian_timezones)
+ tp->value = NEG(30); /* Eastern Standard Time */
+ else
+ tp->value = 60; /* Australia Eastern Std Time */
+
+ /* SAT */
+ tp = datebsearch("sat", datetktbl, szdatetktbl);
+ Assert(tp);
+ if (!Australian_timezones)
+ {
+ tp->type = DOW;
+ tp->value = 6;
+ }
+ else
+ {
+ tp->type = TZ;
+ tp->value = 57;
+ }
+ prev_Australian_timezones = Australian_timezones;
+ }
+}
diff -ru postgresql-7.1.2.orig/src/backend/utils/misc/guc.c postgresql-7.1.2/src/backend/utils/misc/guc.c
--- postgresql-7.1.2.orig/src/backend/utils/misc/guc.c Fri Mar 23 04:41:47 2001
+++ postgresql-7.1.2/src/backend/utils/misc/guc.c Tue Jun 12 14:14:38 2001
@@ -70,6 +70,8 @@
bool SQL_inheritance = true;
+bool Australian_timezones = false;
+
#ifndef PG_KRB_SRVTAB
#define PG_KRB_SRVTAB ""
#endif
@@ -220,6 +222,7 @@
{"show_source_port", PGC_SIGHUP, &ShowPortNumber, false},
{"sql_inheritance", PGC_USERSET, &SQL_inheritance, true},
+ {"australian_timezones", PGC_USERSET, &Australian_timezones, false},
{"fixbtree", PGC_POSTMASTER, &FixBTree, true},
@@ -856,7 +859,7 @@
elog(FATAL, "out of memory");
}
else
-/* no equal sign in string */
+ /* no equal sign in string */
{
*name = strdup(string);
if (!*name)
diff -ru postgresql-7.1.2.orig/src/backend/utils/misc/postgresql.conf.sample
postgresql-7.1.2/src/backend/utils/misc/postgresql.conf.sample
--- postgresql-7.1.2.orig/src/backend/utils/misc/postgresql.conf.sample Fri Mar 16 16:44:33 2001
+++ postgresql-7.1.2/src/backend/utils/misc/postgresql.conf.sample Tue Jun 12 14:14:38 2001
@@ -172,3 +172,9 @@
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#endif
+
+
+#
+# Lock Tracing
+#
+#australian_timezones = false
diff -ru postgresql-7.1.2.orig/src/include/utils/datetime.h postgresql-7.1.2/src/include/utils/datetime.h
--- postgresql-7.1.2.orig/src/include/utils/datetime.h Fri May 4 08:53:07 2001
+++ postgresql-7.1.2/src/include/utils/datetime.h Tue Jun 12 14:14:38 2001
@@ -182,6 +182,7 @@
char value; /* this may be unsigned, alas */
} datetkn;
+extern datetkn datetktbl[];
/* TMODULO()
* Macro to replace modf(), which is broken on some platforms.
diff -ru postgresql-7.1.2.orig/src/include/utils/guc.h postgresql-7.1.2/src/include/utils/guc.h
--- postgresql-7.1.2.orig/src/include/utils/guc.h Thu Mar 22 15:01:12 2001
+++ postgresql-7.1.2/src/include/utils/guc.h Tue Jun 12 14:14:38 2001
@@ -67,5 +67,6 @@
extern bool Show_btree_build_stats;
extern bool SQL_inheritance;
+extern bool Australian_timezones;
#endif /* GUC_H */
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/horology-no-DST-before-1970.out
postgresql-7.1.2/src/test/regress/expected/horology-no-DST-before-1970.out
--- postgresql-7.1.2.orig/src/test/regress/expected/horology-no-DST-before-1970.out Fri Apr 6 15:50:25 2001
+++ postgresql-7.1.2/src/test/regress/expected/horology-no-DST-before-1970.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
--
-- 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
------------------------------
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/horology-solaris-1947.out
postgresql-7.1.2/src/test/regress/expected/horology-solaris-1947.out
--- postgresql-7.1.2.orig/src/test/regress/expected/horology-solaris-1947.out Fri Apr 6 15:50:25 2001
+++ postgresql-7.1.2/src/test/regress/expected/horology-solaris-1947.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
--
-- 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
------------------------------
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/horology.out
postgresql-7.1.2/src/test/regress/expected/horology.out
--- postgresql-7.1.2.orig/src/test/regress/expected/horology.out Fri Apr 6 15:50:25 2001
+++ postgresql-7.1.2/src/test/regress/expected/horology.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
--
-- 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
------------------------------
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/timestamp.out
postgresql-7.1.2/src/test/regress/expected/timestamp.out
--- postgresql-7.1.2.orig/src/test/regress/expected/timestamp.out Fri May 4 05:00:37 2001
+++ postgresql-7.1.2/src/test/regress/expected/timestamp.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
-- 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
------
diff -ru postgresql-7.1.2.orig/src/test/regress/sql/horology.sql postgresql-7.1.2/src/test/regress/sql/horology.sql
--- postgresql-7.1.2.orig/src/test/regress/sql/horology.sql Fri Apr 6 15:50:29 2001
+++ postgresql-7.1.2/src/test/regress/sql/horology.sql Tue Jun 12 14:14:38 2001
@@ -1,10 +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";
diff -ru postgresql-7.1.2.orig/src/test/regress/sql/timestamp.sql postgresql-7.1.2/src/test/regress/sql/timestamp.sql
--- postgresql-7.1.2.orig/src/test/regress/sql/timestamp.sql Sat Nov 25 16:00:33 2000
+++ postgresql-7.1.2/src/test/regress/sql/timestamp.sql Tue Jun 12 14:14:38 2001
@@ -1,10 +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";
On Mon, Jun 11, 2001 at 06:52:52PM -0400, Bruce Momjian wrote: > > I have decided to make this configurable via postgresql.conf so you > don't need separate binaries / configure switch to run in Australia. I > will send a patch over for testing. Great, that's better than a configure option... > > diff -ur postgresql-7.1.2.orig/doc/src/sgml/installation.sgml postgresql-7.1.2/doc/src/sgml/installation.sgml > > --- postgresql-7.1.2.orig/doc/src/sgml/installation.sgml Tue May 15 01:11:31 2001 > > +++ postgresql-7.1.2/doc/src/sgml/installation.sgml Wed Jun 6 10:35:30 2001 > > @@ -462,6 +462,20 @@ > > </varlistentry> > > > > <varlistentry> > > + <term>--enable-australian-tz</term> > > + <listitem> > > + <para> > > + Enables Australian timezone support. This changes the interpretation > > + of timezones in input date/time strings from US-centric to > > + Australian-centric. Specifically, 'EST' is changed from GMT-5 (US > > + Eastern Standard Time) to GMT+10 (Australian Eastern Standard Time) > > + and 'CST' is changed from GMT-5:30 (US Central Standard Time) to > > + GMT+10:30 (Australian Central Standard Time). > > + </para> > > + </listitem> > > + </varlistentry> > > + > > + <varlistentry> > > <term>--enable-locale</term> > > <listitem> > > <para>
I hate the Australian configure option because it means that you can't use the pre-built postgres that comes with RedHat or whatever. Surely the correct solution is to have a config file somewhere that gets read on startup? That way us Australians don't have to be the only ones in the world that need a custom built postgres.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I found I didn't need to clear the date cache.
>>
>> Hmm, are you sure about that? I'm not.
> I checked and it caches a pointer to the struct, not the values
> themselves, and we don't change the structure, just the secondary values
> and not the key used by the bsearch.
Now I'm going to object LOUDLY. You cannot convince me that the above
is a good implementation --- it's a complete crock, and will break the
instant someone looks at it sidewise.
My inclination would actually be to rip out the cache entirely. bsearch
in a table this size is not so expensive that we need to bypass it, nor
is it apparent that we are going to see lots of successive lookups for
the same keyword anyway. How long has that cache been in there, and
what was the motivation for adding it to begin with?
regards, tom lane
> > I checked and it caches a pointer to the struct, not the values > > themselves, and we don't change the structure, just the secondary values > > and not the key used by the bsearch. > > Now I'm going to object LOUDLY. You cannot convince me that the above > is a good implementation --- it's a complete crock, and will break the > instant someone looks at it sidewise. > > My inclination would actually be to rip out the cache entirely. bsearch > in a table this size is not so expensive that we need to bypass it, nor > is it apparent that we are going to see lots of successive lookups for > the same keyword anyway. How long has that cache been in there, and > what was the motivation for adding it to begin with? OK, what do we do with this patch now? We have several Australians who want it and most like it as GUC rather than a configure option. I don't want to add lots more code because I think the GUC capability is simple enough. I can remove the CACHE stuff but only if Thomas agrees. -- 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
I'd just like to ask, will making USE_AUSTRALIAN_RULES as a GUC option mean the regression tests work? And DST changes will work fine too (although I think that's more Linux system related). etc. As in, I'm in favour of a GUC option, but if it breaks regressions tests or other stuff, then I'd have second thoughts. Regards and best wishes, Justin Clift On Tuesday 12 June 2001 15:00, chris.bitmead@health.gov.au wrote: > I hate the Australian configure option because it means that you can't use > the pre-built postgres > that comes with RedHat or whatever. Surely the correct solution is to have > a config file somewhere > that gets read on startup? That way us Australians don't have to be the > only ones in the world > that need a custom built postgres. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
My patch allows the regression tests to pass too. > I'd just like to ask, will making USE_AUSTRALIAN_RULES as a GUC option mean > the regression tests work? > > And DST changes will work fine too (although I think that's more Linux system > related). > > etc. > > As in, I'm in favour of a GUC option, but if it breaks regressions tests or > other stuff, then I'd have second thoughts. > > Regards and best wishes, > > Justin Clift > > On Tuesday 12 June 2001 15:00, chris.bitmead@health.gov.au wrote: > > I hate the Australian configure option because it means that you can't use > > the pre-built postgres > > that comes with RedHat or whatever. Surely the correct solution is to have > > a config file somewhere > > that gets read on startup? That way us Australians don't have to be the > > only ones in the world > > that need a custom built postgres. > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > -- 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
> <varlistentry> > + <term>--enable-australian-tz</term> > + <listitem> > + <para> > + Enables Australian timezone support. This changes the > interpretation > + of timezones in input date/time strings from US-centric to > + Australian-centric. Specifically, 'EST' is changed > from GMT-5 (US > + Eastern Standard Time) to GMT+10 (Australian Eastern > Standard Time) > + and 'CST' is changed from GMT-5:30 (US Central Standard Time) to > + GMT+10:30 (Australian Central Standard Time). > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > <term>--enable-locale</term> > <listitem> > <para> What about us West Australians living in WST (Western Standard Time) ? Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> What about us West Australians living in WST (Western Standard Time) ?
You don't have a problem.
It's only the TZ abbreviations that conflict with US usages that need
to be driven by a switch. The rest of them are just there...
regards, tom lane
>
> > <varlistentry>
> > + <term>--enable-australian-tz</term>
> > + <listitem>
> > + <para>
> > + Enables Australian timezone support. This changes the
> > interpretation
> > + of timezones in input date/time strings from US-centric to
> > + Australian-centric. Specifically, 'EST' is changed
> > from GMT-5 (US
> > + Eastern Standard Time) to GMT+10 (Australian Eastern
> > Standard Time)
> > + and 'CST' is changed from GMT-5:30 (US Central Standard Time) to
> > + GMT+10:30 (Australian Central Standard Time).
> > + </para>
> > + </listitem>
> > + </varlistentry>
> > +
> > + <varlistentry>
> > <term>--enable-locale</term>
> > <listitem>
> > <para>
>
> What about us West Australians living in WST (Western Standard Time) ?
That is already in there. The setting is for names that conflict with
other names:
{"wst", TZ, 48}, /* West Australian Std Time */
--
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
> 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";
> Surely the correct solution is to have a config file somewhere
> that gets read on startup? That way us Australians don't have to be the only
> ones in the world that need a custom built postgres.
I will point out that "you Australians", and, well, "us 'mericans", are
the only countries without the sense to choose unique conventions for
time zone names.
It sounds like having a second lookup table for the Australian rules is
a possibility, and this sounds fairly reasonable to me. Btw, is there an
Australian convention for referring to North American time zones for
those zones with naming conflicts?
- Thomas
> Now I'm going to object LOUDLY. You cannot convince me that the above
> is a good implementation --- it's a complete crock, and will break the
> instant someone looks at it sidewise.
But it hasn't broken in years of use and maintenance, so that does not
sound like an issue. Eventually, we may want full localization with
character sets *and* date/time conventions, and all of this can be
rethought at that time.
> My inclination would actually be to rip out the cache entirely. bsearch
> in a table this size is not so expensive that we need to bypass it, nor
> is it apparent that we are going to see lots of successive lookups for
> the same keyword anyway. How long has that cache been in there, and
> what was the motivation for adding it to begin with?
The cache lookup came from the gods themselves at Berkeley ;)
- Thomas
> > My inclination would actually be to rip out the cache entirely. bsearch > > in a table this size is not so expensive that we need to bypass it, nor > > is it apparent that we are going to see lots of successive lookups for > > the same keyword anyway. How long has that cache been in there, and > > what was the motivation for adding it to begin with? > > The cache lookup came from the gods themselves at Berkeley ;) Wow, I didn't know that. I like the phrase. -- 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
> > 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. > > With the new Australian timezone patch requiring Tom's callback patch > we're diverging more and more from the standard 7.1.2 code base, so for > my own 7.1.2 systems I'm going to revert to my original 'configure' > patch. > > Thanks for your efforts on this (and everything else of course), and > looking forward to the 7.2 code! Yes, I was concerned about that. If you need GUC, the second patch I submitted with fix for static and cache lookup should be fine in 7.1.X. -- 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
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Now I'm going to object LOUDLY. You cannot convince me that the above
>> is a good implementation --- it's a complete crock, and will break the
>> instant someone looks at it sidewise.
> But it hasn't broken in years of use and maintenance, so that does not
> sound like an issue.
Uh, I was complaining about Bruce's idea of scribbling on the datetkn
table and expecting that to change what the datecache records. That's
not something we've been doing for years and years, and no I don't think
it's maintainable.
regards, tom lane
> Uh, I was complaining about Bruce's idea of scribbling on the datetkn
> table and expecting that to change what the datecache records. That's
> not something we've been doing for years and years, and no I don't think
> it's maintainable.
Thanks for the clarification on what you actually meant.
- Thomas
On Wed, Jun 13, 2001 at 01:16:51PM -0400, Bruce Momjian wrote: > > 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. With the new Australian timezone patch requiring Tom's callback patch we're diverging more and more from the standard 7.1.2 code base, so for my own 7.1.2 systems I'm going to revert to my original 'configure' patch. Thanks for your efforts on this (and everything else of course), and looking forward to the 7.2 code! Cheers, Chris, OnTheNet