Default timezone changes in 9.1

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,
thenmy 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.

On Sat, Dec 15, 2012 at 10:35 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 <mailto: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, thenmy 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:

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 irres
pective 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.

On 16/12/12 18:23, Terence Ferraro wrote:

On Sat, Dec 15, 2012 at 11:54 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz <mailto: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
<mailto: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, thenmy 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general