Daylight Saving Time question PostgreSQL 8.1.4

It appears that we didn't do enough research in regards to the recent
DST switch. We poorly assumed that having our machine's timezone files
up to date would be sufficient not knowing that our version of
postgres relied on its own timezone files.

The question is... can we symlink the share/postgresql/timezone/
directory to our OS X /usr/share/zoneinfo to without fear of breaking
anything in the future?

I'm also curious about the rationale to maintain a separate timezone
data files for machines that supply them.

Sincerely,
Michael

Michael,

> I'm also curious about the rationale to maintain a separate timezone
> data files for machines that supply them.

It's because we found that we couldn't ensure consistency between operating
systems while relying on OS files.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Tue, Mar 13, 2007 at 12:20:25PM -0400, Michael Ledford wrote:
> It appears that we didn't do enough research in regards to the recent
> DST switch. We poorly assumed that having our machine's timezone files
> up to date would be sufficient not knowing that our version of
> postgres relied on its own timezone files.

You're obviously in a country that doesn't change the rules very often
or you'd have expected it :)

> The question is... can we symlink the share/postgresql/timezone/
> directory to our OS X /usr/share/zoneinfo to without fear of breaking
> anything in the future?

Well, maybe. You see, until recently the zoneinfo files had a single
format so you could just symlink them. For example, Redhat RPM's did
this IIRC. Symlinking was safe.

However, upstream zoneinfo file had a slight format change making them
incompatable, so I can't say with absolute certainty it will work and
stay working. But it can work, people have done it, just test it, and
be prepared to revert after a system upgrade (in case your system's
version changes format).

> I'm also curious about the rationale to maintain a separate timezone
> data files for machines that supply them.

Not all machines have them. Can you be sure of the format? Do you have
permission to open the files directly. Or the biggest issue: can you
tell at compile time where the timezone files will be at runtime.

Good luck.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Michael,
>> I'm also curious about the rationale to maintain a separate timezone
>> data files for machines that supply them.

> It's because we found that we couldn't ensure consistency between operating
> systems while relying on OS files.

Partly that, and partly that we needed operations that the standard C
library doesn't supply. Hence we had to have direct access to the
timezone database, and since different systems have different
representations of timezone data, we couldn't really rely on the
system's data.

You can try the symlink game if you want, but it'll be on your own head
whether it works or not. (For the record, I am hoping to do exactly
that in future releases for Red Hat ... but in that context I know what
the system's timezone code is. I'm less sure that I know what Apple
is using.)

regards, tom lane

Tom,

> You can try the symlink game if you want, but it'll be on your own head
> whether it works or not. (For the record, I am hoping to do exactly
> that in future releases for Red Hat ... but in that context I know what
> the system's timezone code is. I'm less sure that I know what Apple
> is using.)

Yeah, Solaris wants us to do the same thing ... use their files. At some
point, I'll have to hack it and see how it works.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

> You can try the symlink game if you want, but it'll be on your own head
> whether it works or not. (For the record, I am hoping to do exactly
> that in future releases for Red Hat ... but in that context I know what
> the system's timezone code is. I'm less sure that I know what Apple
> is using.)

Thank you all for your speedy and informative replies. I had a good
idea why you wouldn't symlink (for release consistency), but I wasn't
aware that there were format differences in the timezone files.

Currently Apple's format appears to work fine with postgresql. And
given the responses and to make a quick job of it I will be copying
Apple's files only on the machines affected instead of symlinking
until we can coordinate a new version update. It seems that we are
only being affected in Canada...

Thanks again,
Michael

Michael,

> Currently Apple's format appears to work fine with postgresql. And
> given the responses and to make a quick job of it I will be copying
> Apple's files only on the machines affected instead of symlinking
> until we can coordinate a new version update. It seems that we are
> only being affected in Canada...

Canada and AU changed their DST rules with < 4 months notice.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Michael,
>>> I'm also curious about the rationale to maintain a separate timezone
>>> data files for machines that supply them.
>
>> It's because we found that we couldn't ensure consistency between operating
>> systems while relying on OS files.
>
> Partly that, and partly that we needed operations that the standard C
> library doesn't supply. Hence we had to have direct access to the
> timezone database, and since different systems have different
> representations of timezone data, we couldn't really rely on the
> system's data.
>
> You can try the symlink game if you want, but it'll be on your own head
> whether it works or not. (For the record, I am hoping to do exactly
> that in future releases for Red Hat ... but in that context I know what
> the system's timezone code is. I'm less sure that I know what Apple
> is using.)
>

I have following idea:

1) add guc varibale which enable usage of OS time zone files

2) add extra parameters into ./configure script which enable OS TZ
support in the code and get path to OS TZ files.

It probably will require some OS specific code for reading and
translation TZ file format.

What do you mean about it?

Thanks Zdenek

Josh Berkus wrote:
> Tom,
>
>
>> You can try the symlink game if you want, but it'll be on your own head
>> whether it works or not. (For the record, I am hoping to do exactly
>> that in future releases for Red Hat ... but in that context I know what
>> the system's timezone code is. I'm less sure that I know what Apple
>> is using.)
>>
>
> Yeah, Solaris wants us to do the same thing ... use their files. At some
> point, I'll have to hack it and see how it works.
>
>

perhaps we should have a configure flag like
--with-timezone-base=/usr/share/zoneinfo

cheers

andrew

Zdenec,

> I have following idea:
>
> 1) add guc varibale which enable usage of OS time zone files
>
> 2) add extra parameters into ./configure script which enable OS TZ
> support in the code and get path to OS TZ files.

If we're adding it as a configure-time variable, there's no reason to have
a GUC.

> It probably will require some OS specific code for reading and
> translation TZ file format.

This would require each OS to make a committment to support their
compatibility code. Like, we would have to maintain the Solaris code, if
any was required. Ideally, no glue code would be required.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Zdenec,
>> I have following idea:
>> 1) add guc varibale which enable usage of OS time zone files
>> 2) add extra parameters into ./configure script which enable OS TZ
>> support in the code and get path to OS TZ files.

> If we're adding it as a configure-time variable, there's no reason to have
> a GUC.

I see zero reason to have either. It would only make sense to do this
in the context of a platform-specific distribution such as an RPM, and
in that context the simplest solution is to let the RPM specfile make
the substitution (ie, after "make install" and before packaging,
rm -rf PG's timezone tree and insert a symlink). Then it's on the RPM
packager's head whether it's the right thing to do or not. A configure
switch strikes me as mostly a foot-gun, because the average user of
Postgres won't have any way to know whether the files are compatible.

regards, tom lane

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Zdenec,
>>> I have following idea:
>>> 1) add guc varibale which enable usage of OS time zone files
>>> 2) add extra parameters into ./configure script which enable OS TZ
>>> support in the code and get path to OS TZ files.
>
>> If we're adding it as a configure-time variable, there's no reason to have
>> a GUC.
>
> I see zero reason to have either. It would only make sense to do this
> in the context of a platform-specific distribution such as an RPM, and
> in that context the simplest solution is to let the RPM specfile make
> the substitution (ie, after "make install" and before packaging,
> rm -rf PG's timezone tree and insert a symlink). Then it's on the RPM
> packager's head whether it's the right thing to do or not. A configure
> switch strikes me as mostly a foot-gun, because the average user of
> Postgres won't have any way to know whether the files are compatible.

I don't think to make a symlink is good solution. It generates a lot of
future problem with package update or patching. Configure switch is much
comfortable for packagers/patch makers. In case when average user want
to compile own postgres we can offer regression test focused on TZ
validation. (By the way average user is surprise, that postgres has own
zone files)

I also think, usage system's timezone files should be by default and
configure script determines zonefiles location based on OS. Another
location could be set by switch. If for some platform will be necessary
use own copy, special switch (e.g. --enable-internal-tzfiles) setup
postgres for process own timezone copy.

Zdenek

PS: For information there are TZ locations on several OS:

/usr/share/lib/zoneinfo Solaris
/usr/share/zoneinfo Redhat
/opt/dce/lib/zoneinfo HP-UX (no TZif magic word)
/etc/zoneinfo/ Tru64 (no TZif magic word)
/usr/share/zoneinfo/ MacOS
<registers> MS Windows

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Granted, but a configure switch would allow users who want to use OS TZ
file in conjunction with a compiled from source installation. Many
users of OSes with package managers such as Debian or RedHat may, for
whatever reason, want to use a source tarball to install and also use
the OS TZ list.
<br>
<br>
That being said, this user group may be small enough to ignore. Just
throwing it in for thought.
<br>
<br>
Tom Lane wrote:
<blockquote cite="mid7680(dot)1173809641(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Josh Berkus <a class="moz-txt-link-rfc2396E" href="mailto:josh(at)agliodbs(dot)com">&lt;josh(at)agliodbs(dot)com&gt;</a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">Zdenec,
</pre>
<blockquote type="cite">
<pre wrap="">I have following idea:
1) add guc varibale which enable usage of OS time zone files
2) add extra parameters into ./configure script which enable OS TZ
support in the code and get path to OS TZ files.
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap="">If we're adding it as a configure-time variable, there's no reason to have
a GUC.
</pre>
</blockquote>
<pre wrap=""><!---->
I see zero reason to have either. It would only make sense to do this
in the context of a platform-specific distribution such as an RPM, and
in that context the simplest solution is to let the RPM specfile make
the substitution (ie, after "make install" and before packaging,
rm -rf PG's timezone tree and insert a symlink). Then it's on the RPM
packager's head whether it's the right thing to do or not. A configure
switch strikes me as mostly a foot-gun, because the average user of
Postgres won't have any way to know whether the files are compatible.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

</pre>
</blockquote>
</body>
</html>

On Wed, Mar 14, 2007 at 01:13:58PM +0100, Zdenek Kotala wrote:
> I don't think to make a symlink is good solution. It generates a lot of
> future problem with package update or patching. Configure switch is much
> comfortable for packagers/patch makers. In case when average user want
> to compile own postgres we can offer regression test focused on TZ
> validation. (By the way average user is surprise, that postgres has own
> zone files)

What is the actual problem being solved here? That people expected the
timezone changes to be picked up automatically? think if you weigh it
up, that problem is less significant than:

1. You do a minor system upgrade and now postgres crashes because the
file format changed or the files moved.
2. You run a replication system and get different results on different
machine.

I think that from a data integrity point of view the current system is
the best. At the very least what you propose is a modularity violation:
Postgres depending on undocumented private data of another system
component.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Wed, Mar 14, 2007 at 01:13:58PM +0100, Zdenek Kotala wrote:
>> I don't think to make a symlink is good solution. It generates a lot of
>> future problem with package update or patching. Configure switch is much
>> comfortable for packagers/patch makers. In case when average user want
>> to compile own postgres we can offer regression test focused on TZ
>> validation. (By the way average user is surprise, that postgres has own
>> zone files)
>
> What is the actual problem being solved here? That people expected the
> timezone changes to be picked up automatically? think if you weigh it
> up, that problem is less significant than:

People expect consistent timezone setting for all application on one
machine.

> 1. You do a minor system upgrade and now postgres crashes because the
> file format changed or the files moved.

When you perform minor system upgrade which will delivery new TZ file
format, than new version of libc must be delivery anyway and you
probably must recompile postgres on upgraded system -> you can check if
TZ files works fine and if not you can compile it with build in.

If file is moved, postgres raises error. But I don't see problem there.
If you compare changes between 8.1.5 and 8.1.6, you can see a lot of
removed files.

> 2. You run a replication system and get different results on different
> machine.

However on another point of view, You very often have application and
postgres on one machine. And if you have different tz files for
application and for postgres, the result should be really strange. This
case is most common than replication issue.

>
> I think that from a data integrity point of view the current system is
> the best. At the very least what you propose is a modularity violation:
> Postgres depending on undocumented private data of another system
> component.

Yes, it is true, dependency on private data is not good. But It is
"smaller evil", than have more different timezone on one system.

Zdenek

Martijn van Oosterhout wrote:
> I think that from a data integrity point of view the current system is
> the best. At the very least what you propose is a modularity violation:
> Postgres depending on undocumented private data of another system
> component.
>
>
>

I don't think you can reasonably describe the system timezone database
as undocumented private data. Plenty of other systems rely on it, as we
used to do.

But I take Tom's point about most users not knowing if their TZ database
is usable or not. Maybe we need a tool (maybe on pgfoundry) that will do
some analysis to find out, if such a thing is possible.

cheers

andrew

Andrew Dunstan wrote:
> Martijn van Oosterhout wrote:
>> I think that from a data integrity point of view the current system is
>> the best. At the very least what you propose is a modularity violation:
>> Postgres depending on undocumented private data of another system
>> component.
>>
>>
>>
>
> I don't think you can reasonably describe the system timezone database
> as undocumented private data. Plenty of other systems rely on it, as we
> used to do.
>
> But I take Tom's point about most users not knowing if their TZ database
> is usable or not. Maybe we need a tool (maybe on pgfoundry) that will do
> some analysis to find out, if such a thing is possible.

I guess some regression test should test TZ validity?

Zdenek

Andrew Dunstan wrote:
> Martijn van Oosterhout wrote:
> >I think that from a data integrity point of view the current system is
> >the best. At the very least what you propose is a modularity violation:
> >Postgres depending on undocumented private data of another system
> >component.
>
> I don't think you can reasonably describe the system timezone database
> as undocumented private data. Plenty of other systems rely on it, as we
> used to do.

No -- we relied on the libc's TZ API, which is not the same.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> What is the actual problem being solved here? That people expected the
> timezone changes to be picked up automatically? think if you weigh it
> up, that problem is less significant than: ...

One other point is that symlinking to system timezone info will not
cause Postgres to "pick up changes automatically". You'll probably
still need a postmaster restart to get the new settings loaded in.
If the timezone info update is delivered as part of a Postgres update,
the need for this is fairly obvious, but if it's happening as part
of an update of a seemingly unrelated package, not so much.

To me, the idea of linking to system timezone info is a convenience
for packagers, and it is (and should be) the packagers' responsibility
to determine whether it's a sane idea for their environment. If they
are competent to determine that, they are certainly competent enough
to make it happen without assistance from our configure script.

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> But I take Tom's point about most users not knowing if their TZ database
> is usable or not. Maybe we need a tool (maybe on pgfoundry) that will do
> some analysis to find out, if such a thing is possible.

It's not really *that* hard: diff between our timezone files and the
system files will make it pretty clear. For instance, diffing a CVS
HEAD installation against a not-too-up-to-date Fedora Core 5 system
shows only a few different files, reflecting the fact that they're
different snapshots of the zic database:

$ diff -r --br . /usr/share/zoneinfo
Files ./America/Havana and /usr/share/zoneinfo/America/Havana differ
Files ./America/Santiago and /usr/share/zoneinfo/America/Santiago differ
Files ./Antarctica/Palmer and /usr/share/zoneinfo/Antarctica/Palmer differ
Files ./Australia/Perth and /usr/share/zoneinfo/Australia/Perth differ
Files ./Australia/West and /usr/share/zoneinfo/Australia/West differ
Files ./Chile/Continental and /usr/share/zoneinfo/Chile/Continental differ
Files ./Chile/EasterIsland and /usr/share/zoneinfo/Chile/EasterIsland differ
Files ./Cuba and /usr/share/zoneinfo/Cuba differ
Files ./Pacific/Easter and /usr/share/zoneinfo/Pacific/Easter differ
Only in ./US: Pacific-New
Only in /usr/share/zoneinfo: iso3166.tab
Only in /usr/share/zoneinfo: posix
Only in /usr/share/zoneinfo: posixrules
Only in /usr/share/zoneinfo: right
Only in /usr/share/zoneinfo: zone.tab
$

But IMHO the thing that you need to know to make an informed decision
is what the future update path for that system is likely to be. In the
case of me packaging Postgres for Red Hat, I feel pretty comfortable
that there will be no major surgery on glibc's timezone code within
any single RHEL release series, so if it works today it'll still work
tomorrow. A Sun engineer packaging Postgres for Solaris may be able
to make the same kind of determination. But I think Joe Average User
is sticking his neck out if he assumes such a thing for say a Gentoo
box ...

regards, tom lane