daylight savings patches needed?

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. +

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

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. +

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/

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