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])

Fri, 1 Jun 2001 00:54:41 +1000
Chris Dunlop <chris@onthe.net.au>
Australian timezone configure option
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

Mon, 04 Jun 2001 10:25:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Australian timezone configure option 
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
 

Wed, 6 Jun 2001 09:55:30 +1000
Chris Dunlop <chris@onthe.net.au>
Re: Australian timezone configure option
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

Tue, 05 Jun 2001 20:03:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Australian timezone configure option 
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>

Wed, 6 Jun 2001 10:43:16 +1000
Chris Dunlop <chris@onthe.net.au>
Re: Australian timezone configure option

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

Mon, 11 Jun 2001 18:52:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
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

Tue, 12 Jun 2001 12:25:11 +1000
Justin Clift <aa2@bigpond.net.au>
Re: 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...


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

Tue, 12 Jun 2001 12:30:56 +1000
Chris Dunlop <chris@onthe.net.au>
Re: Australian timezone configure option
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
> 

Tue, 12 Jun 2001 12:32:07 +1000
Chris Dunlop <chris@onthe.net.au>
Re: 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                        |  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

Mon, 11 Jun 2001 22:39:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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.

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";
Mon, 11 Jun 2001 23:53:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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.
> 
> 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>
Tue, 12 Jun 2001 00:46:38 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
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";

Tue, 12 Jun 2001 14:46:54 +1000
Chris Dunlop <chris@onthe.net.au>
Re: Australian timezone configure option
> > 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

Tue, 12 Jun 2001 00:51:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
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

Tue, 12 Jun 2001 02:55:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Australian timezone configure option 
(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

Tue, 12 Jun 2001 15:04:52 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Australian timezone configure option
> 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";
Tue, 12 Jun 2001 11:18:29 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
> (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

Tue, 12 Jun 2001 11:21:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
Thomas Lockhart writes:

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

I'm not particularly happy about "popularizing" that compile time option
beyond its current state (i.e., get in and edit config.h), and there's a
reason why I haven't done it myself yet.

--enable-xxx type configure options should, as a matter of principle, not
replace one behaviour by another.  (The proposed option replaces U.S.
rules by Australian rules.)  In this case it might look like a minor
issue, but it's a slippery slope.  For one thing, packages build by
Australians will cease to behave reasonably in the rest of the world.
That is the same reason why we don't want people altering NAMEDATALEN and
BLCKSZ from configure.

A run-time option seems like the appropriate solution.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Tue, 12 Jun 2001 18:19:05 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Re: [PATCHES] Australian timezone configure option
> Thomas Lockhart writes:
> 
> > 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).
> 
> I'm not particularly happy about "popularizing" that compile time option
> beyond its current state (i.e., get in and edit config.h), and there's a
> reason why I haven't done it myself yet.
> 
> --enable-xxx type configure options should, as a matter of principle, not
> replace one behaviour by another.  (The proposed option replaces U.S.
> rules by Australian rules.)  In this case it might look like a minor
> issue, but it's a slippery slope.  For one thing, packages build by
> Australians will cease to behave reasonably in the rest of the world.
> That is the same reason why we don't want people altering NAMEDATALEN and
> BLCKSZ from configure.
> 
> A run-time option seems like the appropriate solution.

Agreed.  Compile-time is just the wrong way to go.  We should have
compile-time stuff that just relates to the OS/compiler and sometimes
installed software, or simply stuff that just can't be changed without a
recompile/initdb like NAMEDATALEN.

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

Tue, 12 Jun 2001 12:21:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: [PATCHES] Australian timezone configure option
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

Tue, 12 Jun 2001 12:22:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Australian timezone configure option 
> 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

Tue, 12 Jun 2001 12:26:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
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

Tue, 12 Jun 2001 12:39:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Australian timezone configure option 
> 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

Tue, 12 Jun 2001 12:52:22 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
> > 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

Tue, 12 Jun 2001 16:31:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option

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


Wed, 13 Jun 2001 09:35:20 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Australian timezone configure option
"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

Tue, 12 Jun 2001 21:38:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Australian timezone configure option 
> 
> >        <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

Tue, 12 Jun 2001 21:43:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
> 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";
Wed, 13 Jun 2001 13:16:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
> 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

Thu, 14 Jun 2001 00:29:18 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Australian timezone configure option
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

Thu, 14 Jun 2001 10:42:08 +1000
Chris Dunlop <chris@onthe.net.au>
Re: Australian timezone configure option
> > 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

Wed, 13 Jun 2001 20:56:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
> > 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

Wed, 13 Jun 2001 20:58:24 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Australian timezone configure option
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

Wed, 13 Jun 2001 21:20:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Australian timezone configure option 
> 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

Thu, 14 Jun 2001 13:54:59 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Australian timezone configure option