Thread: daylight savings patches needed?
From the FAQ: ==================== 1.14) Will PostgreSQL handle recent daylight saving time changes in various countries? PostgreSQL versions prior to 8.0 use the operating system's timezone database for daylight saving information. All current versions of PostgreSQL 8.0 and later contain up-to-date timezone information. ==================== In this context, assuming we have applied the relevant OS patches (RHEL, Debian, HPUX 11.11, 11.23), does the phrase "contain up-to-date timezone information" mean that all 7.[234].x and 8.x installations are prepared to properly handle the March 1, 2007 DST changes in the US? If not, what is needed? TIA. Ed
On Tuesday 06 February 2007 13:16, Ed L. wrote:
> From the FAQ:
>
> ====================
> 1.14) Will PostgreSQL handle recent daylight saving time changes
> in various countries?
>
> PostgreSQL versions prior to 8.0 use the operating system's
> timezone database for daylight saving information. All current
> versions of PostgreSQL 8.0 and later contain up-to-date timezone
> information.
> ====================
>
> In this context, assuming we have applied the relevant OS patches
> (RHEL, Debian, HPUX 11.11, 11.23), does the phrase "contain
> up-to-date timezone information" mean that all 7.[234].x and 8.x
> installations are prepared to properly handle the March 1, 2007
> DST changes in the US? If not, what is needed?
>
If you are running pre-8.0 versions you need to update your operating system
(as you indicated). If you running an any 8.x version, you need to be on the
most current corresponding 8.x.y release.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Ed L. wrote:
>
> >From the FAQ:
>
> ====================
> 1.14) Will PostgreSQL handle recent daylight saving time changes
> in various countries?
>
> PostgreSQL versions prior to 8.0 use the operating system's
> timezone database for daylight saving information. All current
> versions of PostgreSQL 8.0 and later contain up-to-date timezone
> information.
> ====================
>
> In this context, assuming we have applied the relevant OS patches
> (RHEL, Debian, HPUX 11.11, 11.23), does the phrase "contain
> up-to-date timezone information" mean that all 7.[234].x and 8.x
> installations are prepared to properly handle the March 1, 2007
> DST changes in the US? If not, what is needed?
I was trying to avoid getting into the gory details of which releases
had which timezone fixes, but it seems I can't avoid it. The new FAQ
item has the details:
USA daylight saving time changes are included in PostgreSQL release
8.0.[4+], and all later major releases, e.g. 8.1. Canada and Western
Australia changes are included in 8.0.[10+], 8.1.[6+], and all later
major releases. PostgreSQL releases prior to 8.0 use the operating
system's timezone database for daylight saving information.
If this is unclear, please let me know.
--
Bruce Momjian bruce@momjian.us
Homepage http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Steve Crawford wrote: > > I was trying to avoid getting into the gory details of which releases > > had which timezone fixes, but it seems I can't avoid it. The new FAQ > > item has the details: > > > > USA daylight saving time changes are included in PostgreSQL release > > 8.0.[4+], and all later major releases, e.g. 8.1. Canada and Western > > Australia changes are included in 8.0.[10+], 8.1.[6+], and all later > > major releases. PostgreSQL releases prior to 8.0 use the operating > > system's timezone database for daylight saving information. > > > > If this is unclear, please let me know. > > > > Don't know if this was asked/answered elsewhere but: > > 1. What, exactly, was the point of moving away from the system zoneinfo > files and requiring PG admins to maintain yet another apparently > identical set of files? It seemed to work fine as it was and for me this > change just adds more work and chance of error. We needed more control of how to query that database for timestamp support. > 2. Is there a build option to put it back to the old way? No. > 3. If 2.answer=no then would there be any problem just symlinking the PG > timezone directory to the system zoneinfo directory? Uh, I think you could do that, yea, assuming the same binaries could be used. Remember, it doesn't read the text files but binary representations. -- Bruce Momjian bruce@momjian.us Homepage http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Steve Crawford wrote:
>> 1. What, exactly, was the point of moving away from the system zoneinfo
>> files and requiring PG admins to maintain yet another apparently
>> identical set of files?
The fact that not all systems use the zic database. We were tired of
random system-to-system variations in the timezone behavior ... plus we
needed functionality not exposed by the bog-standard C library API.
>> 3. If 2.answer=no then would there be any problem just symlinking the PG
>> timezone directory to the system zoneinfo directory?
> Uh, I think you could do that, yea, assuming the same binaries could be
> used. Remember, it doesn't read the text files but binary representations.
I've been thinking of doing that on the Red Hat distro, but the problem
is that we are now behind the curve --- we need to sync with the
upstream zic code's recently added support for 64-bit timezone files
before we can be sure of working with current system-provided databases.
I'd like to get that done for 8.3 ...
regards, tom lane
Bruce Momjian wrote: > USA daylight saving time changes are included in PostgreSQL > release 8.0.[4+], and all later major releases, e.g. 8.1. Canada and > Western Australia changes are included in 8.0.[10+], 8.1.[6+], and > all later major releases. PostgreSQL releases prior to 8.0 use the > operating system's timezone database for daylight saving information. > > If this is unclear, please let me know. Perhaps ">= 8.0.10" would clearer than "8.0.[10+]". -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Bruce Momjian wrote: > > USA daylight saving time changes are included in PostgreSQL > > release 8.0.[4+], and all later major releases, e.g. 8.1. Canada and > > Western Australia changes are included in 8.0.[10+], 8.1.[6+], and > > all later major releases. PostgreSQL releases prior to 8.0 use the > > operating system's timezone database for daylight saving information. > > > > If this is unclear, please let me know. > > Perhaps ">= 8.0.10" would clearer than "8.0.[10+]". Yea, I thought about that, but because we do branch selection earlier in the paragraph, I was afraid people would think that 8.1.1 didn't have the fix. -- Bruce Momjian bruce@momjian.us Homepage http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> I was trying to avoid getting into the gory details of which releases > had which timezone fixes, but it seems I can't avoid it. The new FAQ > item has the details: > > USA daylight saving time changes are included in PostgreSQL release > 8.0.[4+], and all later major releases, e.g. 8.1. Canada and Western > Australia changes are included in 8.0.[10+], 8.1.[6+], and all later > major releases. PostgreSQL releases prior to 8.0 use the operating > system's timezone database for daylight saving information. > > If this is unclear, please let me know. > Don't know if this was asked/answered elsewhere but: 1. What, exactly, was the point of moving away from the system zoneinfo files and requiring PG admins to maintain yet another apparently identical set of files? It seemed to work fine as it was and for me this change just adds more work and chance of error. 2. Is there a build option to put it back to the old way? 3. If 2.answer=no then would there be any problem just symlinking the PG timezone directory to the system zoneinfo directory? Cheers, Steve
Robert Treat wrote: > If you are running pre-8.0 versions you need to update your operating system > (as you indicated). If you running an any 8.x version, you need to be on the > most current corresponding 8.x.y release. > So what happens if you have an old os with a new postgresql install? Will CURRENT_TIMESTAMP always return the correct value even if the system 'date' command is showing the wrong time?
Joseph Shraibman wrote: > Robert Treat wrote: > > > If you are running pre-8.0 versions you need to update your operating system > > (as you indicated). If you running an any 8.x version, you need to be on the > > most current corresponding 8.x.y release. > > > So what happens if you have an old os with a new postgresql install? > Will CURRENT_TIMESTAMP always return the correct value even if the > system 'date' command is showing the wrong time? Yes. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Joseph Shraibman wrote:
>> So what happens if you have an old os with a new postgresql install?
>> Will CURRENT_TIMESTAMP always return the correct value even if the
>> system 'date' command is showing the wrong time?
> Yes.
Unless the user changes the system's clock setting (ie, its notion of
the current UTC time) so that what he sees from "date" looks right.
Also, what about Windows? I'm not clear whether Windows tries to keep
system time in UTC or not. Even if it does, it'd be vulnerable to the
same pilot-error problem.
regards, tom lane
> >> So what happens if you have an old os with a new postgresql install? > >> Will CURRENT_TIMESTAMP always return the correct value even if the > >> system 'date' command is showing the wrong time? > > > Yes. > > Unless the user changes the system's clock setting (ie, its notion of > the current UTC time) so that what he sees from "date" looks right. > > Also, what about Windows? I'm not clear whether Windows tries to keep > system time in UTC or not. Even if it does, it'd be vulnerable to the > same pilot-error problem. It does. /Magnus
Joseph Shraibman <jks@selectacast.net> writes: > Robert Treat wrote: > >> If you are running pre-8.0 versions you need to update your operating system >> (as you indicated). If you running an any 8.x version, you need to be on >> the most current corresponding 8.x.y release. >> > So what happens if you have an old os with a new postgresql install? Will > CURRENT_TIMESTAMP always return the correct value even if the system 'date' > command is showing the wrong time? This is something that isn't always good... If your country changes the rules to when DST starts / ends and PostgreSQL doesn't release a new version with the changes (or a patch or...) then you would have to change this in two places: your OS rules and PG's rules for timezones / DST / whatever. -- Jorge Godoy <jgodoy@gmail.com>
We have a 7.4.6 cluster which has been running on an HP B.11.00
box for quite sometime. The IT group applied daylight savings
patches to the OS, but the cluster is still showing the
incorrect timezone:
$ psql -c "select now()"
now
-------------------------------
2007-03-12 13:46:11.369583-05
$ date
Mon Mar 12 14:46:18 EDT 2007
It appears that newly initialized 7.4.6 clusters get the proper
timezone. I didn't see anything in the FAQ about restarting.
Does this mean that we need to restart these clusters in order to
get the timezone updates from the OS? Are they cached in the
postmaster?
TIA.
Ed
On Monday March 12 2007 1:07 pm, Ed L. wrote: > Does this mean that we need to restart these clusters in order > to get the timezone updates from the OS? Are they cached in > the postmaster? Nevermind. I just found it via googling. Would I be correct in understanding that every pre-8.0 cluster must be restarted in order for the OS changes to take affect?!? Perhaps it should be a part of the FAQ answer at http://www.postgresql.org/docs/faqs.FAQ.html#item1.14 Ed
On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: > Would I be correct in understanding that every pre-8.0 cluster > must be restarted in order for the OS changes to take affect?!? Possibly, I imagine many C libraries would cache the timezone data over a fork and might not motice the changes... 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.
Attachment
On Monday March 12 2007 4:08 pm, Martijn van Oosterhout wrote: > On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: > > Would I be correct in understanding that every pre-8.0 > > cluster must be restarted in order for the OS changes to > > take affect?!? > > Possibly, I imagine many C libraries would cache the timezone > data over a fork and might not motice the changes... That was a nasty little surprise. Thanks, Ed
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote:
>> Would I be correct in understanding that every pre-8.0 cluster
>> must be restarted in order for the OS changes to take affect?!?
> Possibly, I imagine many C libraries would cache the timezone data over
> a fork and might not motice the changes...
Postgres is hardly the only app that behaves like that. I saw a bunch
of traffic on the Red Hat lists yesterday/today about cron, syslogd,
etc needing restarts in various releases.
regards, tom lane
I'm running a production database on Linux (select version() = "PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3") I read that all 8.1.* versions are DST-compliant, and sure enough, my development server, which runs 8.1.0, switched fine, as did my 8.2.3 database at home. The production database was upgraded a while ago to 8.1.3 from 7.*. I'm ready to upgrade to 8.2.3 to get the the benefit of all the development since then, but before doing that I'd like to find out what's the problem with the DST not taking. The timezone is 'EST5EDT', and SELECT CASE WHEN timestamptz '20070401 0100' + interval '1 hour' >= '20070401 0300' THEN 'Wrong' ELSE 'Right' END; returns 'Wrong'. Are there perhaps timezone definition files that may not have been added when upgrading from 7.*? Thanks Jaime *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
On Wed, 2007-03-14 at 11:16 -0400, Jaime Silvela wrote: > I'm running a production database on Linux (select version() = > "PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3") > I read that all 8.1.* versions are DST-compliant, and sure enough, my > development server, which runs 8.1.0, switched fine, as did my 8.2.3 > database at home. > The production database was upgraded a while ago to 8.1.3 from 7.*. I'm > ready to upgrade to 8.2.3 to get the the benefit of all the development > since then, but before doing that I'd like to find out what's the > problem with the DST not taking. > > The timezone is 'EST5EDT', and > > SELECT CASE WHEN > timestamptz '20070401 0100' > + interval '1 hour' > >= '20070401 0300' > THEN 'Wrong' ELSE 'Right' END; > > returns 'Wrong'. > That should have been wrong. 1+1=2 not 3 or more EST5EDT change is : 2nd Sunday of March @02:00:00 +01:00:00 and 1st Sunday of November @02:00:00 -01:00:00 Were you not aware that your current President legislated the changes to Daylight Savings. As a result most of North and South America has had to legislate the changes to alleviate what could only have been chaos figuring out what the time would be in different parts of the Americas. > Are there perhaps timezone definition files that may not have been added > when upgrading from 7.*? > > Thanks > Jaime > > > *********************************************************************** > Bear Stearns is not responsible for any recommendation, solicitation, > offer or agreement or any information about any transaction, customer > account or account activity contained in this communication. > > Bear Stearns does not provide tax, legal or accounting advice. You > should consult your own tax, legal and accounting advisors before > engaging in any transaction. In order for Bear Stearns to comply with > Internal Revenue Service Circular 230 (if applicable), you are notified > that any discussion of U.S. federal tax issues contained or referred to > herein is not intended or written to be used, and cannot be used, for > the purpose of: (A) avoiding penalties that may be imposed under the > Internal Revenue Code; nor (B) promoting, marketing or recommending to > another party any transaction or matter addressed herein. > *********************************************************************** > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787
Jaime Silvela <JSilvela@Bear.com> writes:
> ... before doing that I'd like to find out what's the
> problem with the DST not taking.
> The timezone is 'EST5EDT', and
Is it really 'EST5EDT', or 'est5edt' ? 8.1 is case sensitive about this.
regards, tom lane
On Wed, 2007-03-14 at 12:12 -0400, Tom Lane wrote: > Jaime Silvela <JSilvela@Bear.com> writes: > > ... before doing that I'd like to find out what's the > > problem with the DST not taking. > > > The timezone is 'EST5EDT', and > > Is it really 'EST5EDT', or 'est5edt' ? 8.1 is case sensitive about this. > /usr/local/share/postgresql/timezone/EST5EDT /usr/local/share/postgresql/timezone/SystemV/EST5EDT /usr/share/zoneinfo/EST5EDT /usr/share/zoneinfo/SystemV/EST5EDT > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Guy Fraser <guy@incentre.net> writes:
> On Wed, 2007-03-14 at 12:12 -0400, Tom Lane wrote:
>> Is it really 'EST5EDT', or 'est5edt' ? 8.1 is case sensitive about this.
> /usr/local/share/postgresql/timezone/EST5EDT
> /usr/local/share/postgresql/timezone/SystemV/EST5EDT
> /usr/share/zoneinfo/EST5EDT
> /usr/share/zoneinfo/SystemV/EST5EDT
Yeah, that's why it makes a difference. 'est5edt' doesn't match any of
those and so it gets taken as a POSIX-rule timezone spec.
regression=# set timezone = 'EST5EDT';
SET
regression=# select now();
now
-------------------------------
2007-03-14 12:52:41.798348-04
(1 row)
regression=# set timezone = 'est5edt';
SET
regression=# select now();
now
------------------------------
2007-03-14 11:52:46.21214-05
(1 row)
regression=#
regards, tom lane
Thanks. Yes, I do confirm it was EST5EDT and I ran your regression to confirm. I looked for those timezone files, and they're missing on my production installation. Probably the upgrade from 7.* to 8.1 was a quick&dirty one. I don't even have a 'timezone' folder in the share directory. Would it be dangerous to add one until I can upgrade the production installation? Thanks again. Tom Lane wrote: > Guy Fraser <guy@incentre.net> writes: > >> On Wed, 2007-03-14 at 12:12 -0400, Tom Lane wrote: >> >>> Is it really 'EST5EDT', or 'est5edt' ? 8.1 is case sensitive about this. >>> > > >> /usr/local/share/postgresql/timezone/EST5EDT >> /usr/local/share/postgresql/timezone/SystemV/EST5EDT >> /usr/share/zoneinfo/EST5EDT >> /usr/share/zoneinfo/SystemV/EST5EDT >> > > Yeah, that's why it makes a difference. 'est5edt' doesn't match any of > those and so it gets taken as a POSIX-rule timezone spec. > > regression=# set timezone = 'EST5EDT'; > SET > regression=# select now(); > now > ------------------------------- > 2007-03-14 12:52:41.798348-04 > (1 row) > > regression=# set timezone = 'est5edt'; > SET > regression=# select now(); > now > ------------------------------ > 2007-03-14 11:52:46.21214-05 > (1 row) > > regression=# > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************
Jaime Silvela <JSilvela@bear.com> writes:
> I looked for those timezone files, and they're missing on my production
> installation. Probably the upgrade from 7.* to 8.1 was a quick&dirty one.
> I don't even have a 'timezone' folder in the share directory.
Oh, that's your problem then --- 'EST5EDT' is being taken as a Posix
spec for lack of any matching file.
> Would it
> be dangerous to add one until I can upgrade the production installation?
Should work to just copy over the timezone directory tree from a correct
installation on the same machine architecture (I can't recall right now
if the file format is machine-dependent or not). You might have to
restart the postmaster too, before it starts behaving entirely sanely.
regards, tom lane
On Mar 12, 2007, at 6:08 PM, Martijn van Oosterhout wrote: > On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: >> Would I be correct in understanding that every pre-8.0 cluster >> must be restarted in order for the OS changes to take affect?!? > > Possibly, I imagine many C libraries would cache the timezone data > over > a fork and might not motice the changes... You also need to restart cron, possibly syslog, etc. Much easier to reboot than chase down every long running app just to update the date library's cached zone info.
Attachment
On Mar 14, 2007, at 2:00 PM, Tom Lane wrote: > Should work to just copy over the timezone directory tree from a > correct > installation on the same machine architecture (I can't recall right > now > if the file format is machine-dependent or not). You might have to > restart the postmaster too, before it starts behaving entirely sanely. The compiled zone info files are compatible across time and space. I just copied the correct US Eastern time zone file from an amd64 FreeBSD 6.1 system onto a NSLU2 running a hacked embedded linux using an arm chip (I use it as an NFS file server) and it did the right thing.
Attachment
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 03/14/07 15:23, Vivek Khera wrote: > > On Mar 12, 2007, at 6:08 PM, Martijn van Oosterhout wrote: > >> On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: >>> Would I be correct in understanding that every pre-8.0 cluster >>> must be restarted in order for the OS changes to take affect?!? >> >> Possibly, I imagine many C libraries would cache the timezone data over >> a fork and might not motice the changes... > > You also need to restart cron, possibly syslog, etc. Much easier to > reboot than chase down every long running app just to update the date > library's cached zone info. Debian has the python script /usr/sbin/checkrestart which loos for such things. If you upgrade glibc, for example, checkrestart determines which processes need to be restarted. > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF+F1VS9HxQb37XmcRArPCAKDEHVdS9AUZPTWTTWH3ry4KTrkUIQCfT2xn yiBAS9i70NVjwdUyLi61s08= =y0v0 -----END PGP SIGNATURE-----
I copied the files over, restarted, and everything's fine. Tom Lane wrote: > Jaime Silvela <JSilvela@bear.com> writes: > >> I looked for those timezone files, and they're missing on my production >> installation. Probably the upgrade from 7.* to 8.1 was a quick&dirty one. >> I don't even have a 'timezone' folder in the share directory. >> > > Oh, that's your problem then --- 'EST5EDT' is being taken as a Posix > spec for lack of any matching file. > > >> Would it >> be dangerous to add one until I can upgrade the production installation? >> > > Should work to just copy over the timezone directory tree from a correct > installation on the same machine architecture (I can't recall right now > if the file format is machine-dependent or not). You might have to > restart the postmaster too, before it starts behaving entirely sanely. > > regards, tom lane > > *********************************************************************** Bear Stearns is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. Bear Stearns does not provide tax, legal or accounting advice. You should consult your own tax, legal and accounting advisors before engaging in any transaction. In order for Bear Stearns to comply with Internal Revenue Service Circular 230 (if applicable), you are notified that any discussion of U.S. federal tax issues contained or referred to herein is not intended or written to be used, and cannot be used, for the purpose of: (A) avoiding penalties that may be imposed under the Internal Revenue Code; nor (B) promoting, marketing or recommending to another party any transaction or matter addressed herein. ***********************************************************************