Thread: Default timezone changes in 9.1

We recently began upgrading our clients' servers from 9.0 -> 9.2. After a few deployments and a little digging we noticed that 9.0 -> 9.1 broke the use of no timezone set within postgresql.conf. That is, not setting the option was now defaulting to GMT instead of the system timezone. Unfortunately, this put quite a damper on our "one configuration file to rule them all" setup across servers located throughout various time zones.

We recently began upgrading our clients' servers from 9.0 -> 9.2. After a few deployments and a little digging we noticed that 9.0 -> 9.1 broke the use of no timezone set within postgresql.conf. That is, not setting the option was now defaulting to GMT instead of the system timezone. Unfortunately, this put quite a damper on our "one configuration file to rule them all" setup across servers located throughout various time zones.

I obtained the commit ca4af308c32d03db5fbacb54d6e583ceb904f268 from the git repository and have reversed it against 9.2. Though it didn't apply as smoothly as initially hoped; applying a few of the failed hunks manually allowed me to successfully compile 9.2.1 with these changes reversed.

After some (light) testing, the previous functionality of the system choosing the timezone at runtime seems to be functional again.

I found this functionality invaluable and figured I'd get the patch (http://pastebin.com/5AyaX2RF) posted in case anyone else ever needs this functionality back. It works against 9.2.1; no guarantees on future releases of course and YMMV.

T.J.

I simply do not understand why you would _NOT_ want to store date/times as GMT!

Storing as GMT, allows the times to be easily converted into whatever time zone you are in, also allows for times to be correctly ordered irrespective of time zone.

If I make a phone call from Auckland to New York at 2am NZST on Friday, then my colleague is talking to me at the same time - even though it is still Thursday for them!


Cheers,
Gavin

Sorry about the double post, I thought the list disallowed attachments so I sent it again with a pastebin link instead of an attachment.

This change does not affect the storage at all. If it did, pre-9.1 things would have been a mess. Rather, this allows the system to determine the timezone for localized use at runtime.

Post 9.1, the system determines this via initdb data directory initialization and automatically sets it within postgresql.conf.

In other words, the default now is *not* GMT but rather the system detected timezone at initdb runtime. Removing that statically set configuration option, in this case, *then* assumes GMT.

In other words, it may be 2 AM NZST, but would you really want to walk around and have all of your clocks read 1 PM (GMT)? :)

T.J.

Sorry about the double post, I thought the list disallowed attachments so I sent it again with a pastebin link instead of an attachment.

This change does not affect the storage at all. If it did, pre-9.1 things would have been a mess. Rather, this allows the system to determine the timezone for localized use at runtime.

Post 9.1, the system determines this via initdb data directory initialization and automatically sets it within postgresql.conf.

In other words, the default now is *not* GMT but rather the system detected timezone at initdb runtime. Removing that statically set configuration option, in this case, *then* assumes GMT.

In other words, it may be 2 AM NZST, but would you really want to walk around and have all of your clocks read 1 PM (GMT)? :)

T.J.

On Sat, Dec 15, 2012 at 10:35 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:

On 16/12/12 16:07, Terence Ferraro wrote:

We recently began upgrading our clients' servers from 9.0 -> 9.2. After a few deployments and a little digging we noticed that 9.0 -> 9.1 broke the use of no timezone set within postgresql.conf. That is, not setting the option was now defaulting to GMT instead of the system timezone. Unfortunately, this put quite a damper on our "one configuration file to rule them all" setup across servers located throughout various time zones.

I obtained the commit ca4af308c32d03db5fbacb54d6e583ceb904f268 from the git repository and have reversed it against 9.2. Though it didn't apply as smoothly as initially hoped; applying a few of the failed hunks manually allowed me to successfully compile 9.2.1 with these changes reversed.

After some (light) testing, the previous functionality of the system choosing the timezone at runtime seems to be functional again.

I found this functionality invaluable and figured I'd get the patch (http://pastebin.com/5AyaX2RF) posted in case anyone else ever needs this functionality back. It works against 9.2.1; no guarantees on future releases of course and YMMV.

T.J.

I simply do not understand why you would _NOT_ want to store date/times as GMT!

Storing as GMT, allows the times to be easily converted into whatever time zone you are in, also allows for times to be correctly ordered irrespective of time zone.

If I make a phone call from Auckland to New York at 2am NZST on Friday, then my colleague is talking to me at the same time - even though it is still Thursday for them!


Cheers,
Gavin

My machine time is stored in GMT, but displayed in local time.  If I store time as the local time zone, then I could end up with a file created after I had amended it, or having a negative uptime!   Similarly, I want my date/times to be stored in GMT, but displayed in local time.

This is similar to storing money as an integer value of cents then formatting it for output with 2 decimal place.  Storing money as a double, is a common programming error - though, sometimes storing money as a double is actually valid.

The key point is the storage type used should be one most appropriate for processing, and need not be what is displayed.  In a database, someone's name will normally be stored in 2 or fields, but printed out as if it had been stored as a single string.


Cheers,
Gavin

On Sat, Dec 15, 2012 at 11:54 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:

All of your points are correct. The patch I provided changes none of that.

I think what you're missing here is that the timezone option in postgresql.conf does not change the internals of how a timestamp is stored. Rather, without it, by leaving that option blank, you would NOT see your local time when running SELECT CURRENT_TIMESTAMP. Instead, you'd see the GMT time.

Likewise, if I created a configuration file on my local machine, initdb would set the timezone option to "US/Eastern" and if I then distributed that to other servers located in various timezones they would ALL display CURRENT_TIMESTAMP with reference to "US/Eastern" instead of the actual machine timezone.

Previously (and now, with the patch), leaving the timezone option blank would simply determine, at run time, the machine's time zone and display times in that local timezone.

Does that make sense? The timezone parameter has nothing to do with storage types and everything to do with the display of the local time.

On Sat, Dec 15, 2012 at 11:54 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:

Please do not top post, see end of email for rest of reply...
(Bottom posting is the convention here, so people can see the context before reading your reply.)

On 16/12/12 16:52, Terence Ferraro wrote:

Sorry about the double post, I thought the list disallowed attachments so I sent it again with a pastebin link instead of an attachment.

This change does not affect the storage at all. If it did, pre-9.1 things would have been a mess. Rather, this allows the system to determine the timezone for localized use at runtime.

Post 9.1, the system determines this via initdb data directory initialization and automatically sets it within postgresql.conf.

In other words, the default now is *not* GMT but rather the system detected timezone at initdb runtime. Removing that statically set configuration option, in this case, *then* assumes GMT.

In other words, it may be 2 AM NZST, but would you really want to walk around and have all of your clocks read 1 PM (GMT)? :)

T.J.

On Sat, Dec 15, 2012 at 10:35 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:

On 16/12/12 16:07, Terence Ferraro wrote:

We recently began upgrading our clients' servers from 9.0 -> 9.2. After a few deployments and a little digging we noticed that 9.0 -> 9.1 broke the use of no timezone set within postgresql.conf. That is, not setting the option was now defaulting to GMT instead of the system timezone. Unfortunately, this put quite a damper on our "one configuration file to rule them all" setup across servers located throughout various time zones.

I obtained the commit ca4af308c32d03db5fbacb54d6e583ceb904f268 from the git repository and have reversed it against 9.2. Though it didn't apply as smoothly as initially hoped; applying a few of the failed hunks manually allowed me to successfully compile 9.2.1 with these changes reversed.

After some (light) testing, the previous functionality of the system choosing the timezone at runtime seems to be functional again.

I found this functionality invaluable and figured I'd get the patch (http://pastebin.com/5AyaX2RF) posted in case anyone else ever needs this functionality back. It works against 9.2.1; no guarantees on future releases of course and YMMV.

T.J.

I simply do not understand why you would _NOT_ want to store date/times as GMT!

Storing as GMT, allows the times to be easily converted into whatever time zone you are in, also allows for times to be correctly ordered irrespective of time zone.

If I make a phone call from Auckland to New York at 2am NZST on Friday, then my colleague is talking to me at the same time - even though it is still Thursday for them!


Cheers,
Gavin

My machine time is stored in GMT, but displayed in local time.  If I store time as the local time zone, then I could end up with a file created after I had amended it, or having a negative uptime!   Similarly, I want my date/times to be stored in GMT, but displayed in local time.

This is similar to storing money as an integer value of cents then formatting it for output with 2 decimal place.  Storing money as a double, is a common programming error - though, sometimes storing money as a double is actually valid.

The key point is the storage type used should be one most appropriate for processing, and need not be what is displayed.  In a database, someone's name will normally be stored in 2 or fields, but printed out as if it had been stored as a single string.


Cheers,
Gavin

All of your points are correct. The patch I provided changes none of that.

I think what you're missing here is that the timezone option in postgresql.conf does not change the internals of how a timestamp is stored. Rather, without it, by leaving that option blank, you would NOT see your local time when running SELECT CURRENT_TIMESTAMP. Instead, you'd see the GMT time.

Likewise, if I created a configuration file on my local machine, initdb would set the timezone option to "US/Eastern" and if I then distributed that to other servers located in various timezones they would ALL display CURRENT_TIMESTAMP with reference to "US/Eastern" instead of the actual machine timezone.

Previously (and now, with the patch), leaving the timezone option blank would simply determine, at run time, the machine's time zone and display times in that local timezone.

Does that make sense? The timezone parameter has nothing to do with storage types and everything to do with the display of the local time.

I had a horrible feeling, I was missing something obvious!  :-)

Hi,

On 2012-12-15 22:07:32 -0500, Terence Ferraro wrote:
> We recently began upgrading our clients' servers from 9.0 -> 9.2. After a
> few deployments and a little digging we noticed that 9.0 -> 9.1 broke the
> use of no timezone set within postgresql.conf. That is, not setting the
> option was now defaulting to GMT instead of the system timezone.
> Unfortunately, this put quite a damper on our "one configuration file to
> rule them all" setup across servers located throughout various time zones.
>
> I obtained the commit ca4af308c32d03db5fbacb54d6e583ceb904f268 from the git
> repository and have reversed it against 9.2. Though it didn't apply as
> smoothly as initially hoped; applying a few of the failed hunks manually
> allowed me to successfully compile 9.2.1 with these changes reversed.
>
> After some (light) testing, the previous functionality of the system
> choosing the timezone at runtime seems to be functional again.

Setting the timezone to 'localtime' seems to work as well, even without
a patch. I haven't found any documentation about it and I am not
completely sure about the semantics, but it looks ok on a first glance.

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Terence Ferraro <terencejferraro@gmail.com> writes:
> Post 9.1, the system determines this via initdb data directory
> initialization and automatically sets it within postgresql.conf.
> In other words, the default now is *not* GMT but rather the system detected
> timezone at initdb runtime. Removing that statically set configuration
> option, in this case, *then* assumes GMT.

This argument is nonsense.  If you want GMT, why don't you just let
initdb set it that way?  It should work to set TZ=GMT in its
environment for instance.

There were a couple reasons why we made that change:
* it's fairly expensive to infer the system timezone, and people
complained about the cost of doing so on every postmaster start.
* it made timezone work more like the locale settings, which have
always been set at initdb time.

So we're not going to be reverting the change, but I am curious to find
out why you find it so important as to be willing to run a modified
version that does this the old way.

            regards, tom lane

Andres Freund <andres@2ndquadrant.com> writes:
> Setting the timezone to 'localtime' seems to work as well, even without
> a patch. I haven't found any documentation about it and I am not
> completely sure about the semantics, but it looks ok on a first glance.

I think on most distros that use the Olson tz database, 'localtime' is
defined by means of adding a symlink in the system timezone directory.
It's going to refer to whatever zone was chosen as the system-wide
default.  That would match what the postmaster sees as the system
timezone if you have not set TZ in its environment ... but it would also
match what initdb sees as the system timezone.  I'm still not sure what
the OP actually wants to accomplish by moving the time of making the
choice.

            regards, tom lane

On 2012-12-16 12:25:13 -0500, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > Setting the timezone to 'localtime' seems to work as well, even without
> > a patch. I haven't found any documentation about it and I am not
> > completely sure about the semantics, but it looks ok on a first glance.
>
> I think on most distros that use the Olson tz database, 'localtime' is
> defined by means of adding a symlink in the system timezone directory.
> It's going to refer to whatever zone was chosen as the system-wide
> default.  That would match what the postmaster sees as the system
> timezone if you have not set TZ in its environment ... but it would also
> match what initdb sees as the system timezone.  I'm still not sure what
> the OP actually wants to accomplish by moving the time of making the
> choice.

I guess he wants - and I have seen that before - to use the same
postgresql.conf across a number of different systems. Where the
individual servers are in different timezones...

Doing initdb, extracting the few values that should be kept, and then
putting those and an include for the common configuration file into the
configuration is harder than just replacing it alltogether.

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres@2ndquadrant.com> writes:
> On 2012-12-16 12:25:13 -0500, Tom Lane wrote:
>> I'm still not sure what
>> the OP actually wants to accomplish by moving the time of making the
>> choice.

> I guess he wants - and I have seen that before - to use the same
> postgresql.conf across a number of different systems. Where the
> individual servers are in different timezones...

> Doing initdb, extracting the few values that should be kept, and then
> putting those and an include for the common configuration file into the
> configuration is harder than just replacing it alltogether.

True, but if he's assuming that timezone is and always will be the only
thing that needs to vary across servers, he's in for pain someday
anyway.  Probably sooner not later.

Another possibility for per-server overrides is to specify stuff on the
postmaster command line, assuming you're using a custom start script
that you can tweak to add parameter settings there.

            regards, tom lane

On Sun, Dec 16, 2012 at 1:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

With the exception of a few parameters (max_connections and the ssl related variables that we enable), the default configuration file (circa 9.0) has worked extremely well across 100+ machines so far over the last two years and counting. However, we are simply deploying these on commodity machines ($300-400 off the shelf). Spec wise such machines have not changed significantly (I suppose the shift away from higher clock speeds to more cores can be thanked for that).

So, really as a testament to Postgres and all the hard work you guys have done, using the same config file has really been quite pain free (truly, thanks!) =)

I'm not at all concerned about mainlining the regression; for windows machines we have to compile postgres ourselves anyways to enable SSL compression (the EnterpriseDB ssl library is not linked against zlib).

However, I figured I'd at least gain a little google notoriety with some specific keywords in case anyone else makes the leap from 9.0 forward and also found this runtime functionality useful.

I will take your custom script suggestion into consideration. However, since the regression seems to be working well, I think I'm likely to keep the simplicity of the system determining this for me.

Thanks,
T.J.

On 2012-12-16, Terence Ferraro <terencejferraro@gmail.com> wrote:

> With the exception of a few parameters (max_connections and the ssl related
> variables that we enable), the default configuration file (circa 9.0) has
> worked extremely well across 100+ machines so far over the last two years
> and counting. However, we are simply deploying these on commodity machines
> ($300-400 off the shelf). Spec wise such machines have not changed
> significantly (I suppose the shift away from higher clock speeds to more
> cores can be thanked for that).

You cam possibly get some of what you want using "SQL" like:

 alter database "DB_NAME" set timezone = 'localtime';

 You can do the similarly with other connection parameters on a
per-user or per-database basis too.

--
⚂⚃ 100% natural

On Sat, Dec 22, 2012 at 3:41 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2012-12-16, Terence Ferraro <terencejferraro@gmail.com> wrote:
>
>> With the exception of a few parameters (max_connections and the ssl related
>> variables that we enable), the default configuration file (circa 9.0) has
>> worked extremely well across 100+ machines so far over the last two years
>> and counting. However, we are simply deploying these on commodity machines
>> ($300-400 off the shelf). Spec wise such machines have not changed
>> significantly (I suppose the shift away from higher clock speeds to more
>> cores can be thanked for that).
>
> You cam possibly get some of what you want using "SQL" like:
>
>  alter database "DB_NAME" set timezone = 'localtime';
>
>  You can do the similarly with other connection parameters on a
> per-user or per-database basis too.
>

If the goal is just to use a single config and have tz match the
system, the setting localtime in the postgresql.conf should suffice.
IIRC this is what we've started doing, since we we're bit by this as
well. (I think the first systems we noticed it on were ones where
system was UTC and Postgres was GMT, which was mostly a cosmetic
problem, but it surprised us elsewhere too). It makes me wonder if
there was enough thought put into the backwards compatibility angle of
this; either what the default should be, or to make sure people were
aware of the change.

Robert Treat
play: xzilla.net
work: omniti.com