Thread: Daylight Saving Time question PostgreSQL 8.1.4

Daylight Saving Time question PostgreSQL 8.1.4

From
"Michael Ledford"
Date:
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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Josh Berkus
Date:
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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Martijn van Oosterhout
Date:
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@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Josh Berkus
Date:
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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
"Michael Ledford"
Date:
> 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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Josh Berkus
Date:
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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Zdenek Kotala
Date:
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Josh Berkus
Date:
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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Andrew Dunstan
Date:
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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Zdenek Kotala
Date:
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.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






Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Naz Gassiep
Date:
Granted, but a configure switch would allow users who want to use OS TZ file in conjunction with a compiled from
sourceinstallation. Many users of OSes with package managers such as Debian or RedHat may, for whatever reason, want to
usea source tarball to install and also use the OS TZ list. <br /><br /> That being said, this user group may be small
enoughto ignore. Just throwing it in for thought. <br /><br /> Tom Lane wrote: <blockquote
cite="mid7680.1173809641@sss.pgh.pa.us"type="cite"><pre wrap="">Josh Berkus <a class="moz-txt-link-rfc2396E"
href="mailto:josh@agliodbs.com"><josh@agliodbs.com></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"><prewrap="">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
datatypesdo not      match
 
 </pre></blockquote>

Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Martijn van Oosterhout
Date:
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@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Andrew Dunstan
Date:
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



Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Zdenek Kotala
Date:
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
compileit 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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Zdenek Kotala
Date:
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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Alvaro Herrera
Date:
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.


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.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


Re: Daylight Saving Time question PostgreSQL 8.1.4

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.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