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

Search Discussions

  • Josh Berkus at Mar 13, 2007 at 4:31 pm
    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
  • Tom Lane at Mar 13, 2007 at 4:50 pm

    Josh Berkus 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
  • Josh Berkus at Mar 13, 2007 at 5:11 pm
    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
  • Andrew Dunstan at Mar 13, 2007 at 6:02 pm

    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
  • Michael Ledford at Mar 13, 2007 at 5:14 pm

    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
  • Josh Berkus at Mar 13, 2007 at 5:40 pm
    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
  • Zdenek Kotala at Mar 13, 2007 at 5:52 pm

    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
  • Josh Berkus at Mar 13, 2007 at 6:02 pm
    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
  • Tom Lane at Mar 13, 2007 at 6:14 pm

    Josh Berkus 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
  • Zdenek Kotala at Mar 14, 2007 at 12:18 pm

    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
  • Martijn van Oosterhout at Mar 14, 2007 at 2:39 pm

    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.
  • Andrew Dunstan at Mar 14, 2007 at 2:53 pm

    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
  • Zdenek Kotala at Mar 14, 2007 at 3:04 pm

    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
  • Alvaro Herrera at Mar 14, 2007 at 3:06 pm

    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.
  • Tom Lane at Mar 14, 2007 at 3:36 pm

    Andrew Dunstan 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
  • Zdenek Kotala at Mar 14, 2007 at 2:56 pm

    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
  • Tom Lane at Mar 14, 2007 at 3:21 pm

    Martijn van Oosterhout 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
  • Naz Gassiep at Mar 14, 2007 at 1:11 pm
    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.

    That being said, this user group may be small enough to ignore. Just throwing it in for thought.

    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. 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
  • Martijn van Oosterhout at Mar 13, 2007 at 4:44 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 13, '07 at 4:20p
activeMar 14, '07 at 3:36p
posts20
users8
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase