Thread: Re: Bug#311533: Invalid timestamp returned because of timezone
Hi Guillaume, hi PostgreSQL developers! Guillaume, thanks for the great research you did! PostgreSQL developers, the details of this are at [1], but I quote the important bits here. Guillaume Beaudoin [2005-06-01 11:57 -0400]: >=20 > Package: postgresql > Version: 7.4.7-6sarge1 >=20 > When changing the time zone of a session, postgresql start to respond > with erronous date such as 1979-06-06 09:42:49.854158-227814:06. >=20 >The following : > >SELECT NOW(); >SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE; >SELECT NOW(); > >output those two dates : > >2005-06-02 09:05:58.991119-04 >1979-06-07 06:59:03.246652-227814:06 > > Is there a hope that a fix will be included in the upcoming sarge > release? What can I do to fix the problem if not? No, at that time Sarge was in icecold freeze. :-) > As per conversation in #postgresql in freenode, it has been found that > this seems to manifest on pgsql compiled using integer-datetime; > float-datetime version does not have this problem. I just tried to do the proposed change, however, it is not possible to start the new postmaster on an already existing cluster. You had to dump all clusters with the old postmaster, install the new one and recreate the clusters, which is a hell of an upgrade (so it's definitively nothing for Sarge, even less for sarge-proposed updates). So I can't apply that change for now. The cleanest one would obviously be to fix integer timestamps, or if that is not possible, at least support selecting integer or float time stamps at runtime (maybe as a postmaster option). Can this be done in any way? Thanks in advance for any thought and have a nice day! Martin [1] http://bugs.debian.org/311533 --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org
On 2005-06-10, Martin Pitt <martin@piware.de> wrote: >> As per conversation in #postgresql in freenode, it has been found that >> this seems to manifest on pgsql compiled using integer-datetime; >> float-datetime version does not have this problem. > > I just tried to do the proposed change, however, it is not possible to > start the new postmaster on an already existing cluster. You had to > dump all clusters with the old postmaster, install the new one and > recreate the clusters, which is a hell of an upgrade (so it's > definitively nothing for Sarge, even less for sarge-proposed updates). > So I can't apply that change for now. Out of curiosity, why was it using the integer-datetimes option at all? It's not the default in the distributed source, and it's had a series of bugs found in it, this being merely the latest. > The cleanest one would obviously be to fix integer timestamps, or if > that is not possible, at least support selecting integer or float time > stamps at runtime (maybe as a postmaster option). Can this be done in > any way? Since changing the option affects how every single timestamp value in the database is stored, it's hard to see how it could be made switchable at runtime. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Hi! Andrew - Supernews [2005-06-10 23:29 -0000]: > On 2005-06-10, Martin Pitt <martin@piware.de> wrote: > >> As per conversation in #postgresql in freenode, it has been found that > >> this seems to manifest on pgsql compiled using integer-datetime; > >> float-datetime version does not have this problem. > > > > I just tried to do the proposed change, however, it is not possible to > > start the new postmaster on an already existing cluster. You had to > > dump all clusters with the old postmaster, install the new one and > > recreate the clusters, which is a hell of an upgrade (so it's > > definitively nothing for Sarge, even less for sarge-proposed updates). > > So I can't apply that change for now. >=20 > Out of curiosity, why was it using the integer-datetimes option at all? > It's not the default in the distributed source, and it's had a series of > bugs found in it, this being merely the latest. It was enabled ages ago; I can't tell you the reason since I have only maintained the package for the last 1.5 years. But since then we had to drag this setting to not break each and every database out there. :-( > > The cleanest one would obviously be to fix integer timestamps, or if > > that is not possible, at least support selecting integer or float time > > stamps at runtime (maybe as a postmaster option). Can this be done in > > any way? >=20 > Since changing the option affects how every single timestamp value in the > database is stored, it's hard to see how it could be made switchable at > runtime. Maybe I did not express myself clearly: I don't ask to switch the _database_ layout at runtime, but the postmaster behavior at startup time. The idea: would be: - Compile new versions with float timestamps (but with support for integer timstamps, too). - Create new clusters with float timestamps. - If starting the postmaster on an already existing cluster fails because of different timestamps (postmaster can detect this), start the postmaster on the cluster with something like=20 "postmaster --integer-timestamps". This would require that support for both int and float timestamps is present in the postmaster, but wouldn't require an immediate dump and reload of all databases. Would that be possible in any way? If not, does anybody have any other idea? Martin --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org
Martin Pitt <martin@piware.de> writes:
> This would require that support for both int and float timestamps is
> present in the postmaster, but wouldn't require an immediate dump and
> reload of all databases. Would that be possible in any way?
Given infinite amounts of work, anything is possible. However, I'd rate
the chances of the above actually happening as about nil. The chances
of it happening in an existing stable release branch are flat-out zero.
We don't make major feature additions in stable branches; that's why
they are stable ;-)
I'm not sure why you are asking for this anyway: it wouldn't do anything
to solve the immediate problem for existing users. What *would* do
something for them is to apply the patch already committed to our CVS.
2005-06-04 21:48 tgl
* src/backend/commands/: variable.c (REL7_3_STABLE), variable.c
(REL7_4_STABLE), variable.c (REL8_0_STABLE), variable.c: Code for
SET/SHOW TIME ZONE with a fixed-interval timezone was not prepared
for HAVE_INT64_TIMESTAMP. Per report from Guillaume Beaudoin.
regards, tom lane
Hi Tom! Tom Lane [2005-06-11 13:13 -0400]: > Martin Pitt <martin@piware.de> writes: > > This would require that support for both int and float timestamps is > > present in the postmaster, but wouldn't require an immediate dump and > > reload of all databases. Would that be possible in any way? >=20 > Given infinite amounts of work, anything is possible. However, I'd rate > the chances of the above actually happening as about nil. OK, I don't have any idea how complicated this would be. > The chances of it happening in an existing stable release branch are > flat-out zero. That's reasonable and shouldn't happen. I did not intend to fix that in stable Debian/Ubuntu releases as well. :-) > I'm not sure why you are asking for this anyway: it wouldn't do > anything to solve the immediate problem for existing users.=20=20 I just thought about how to provide a transition path if the long-term solution really was to get rid of integer time stamps. The proposal would have helped to be able to convert existing clusters to float timestamps without having to rely on old versions of the postmaster (which is really difficult to achieve in a packaging system). > What *would* do > something for them is to apply the patch already committed to our CVS. >=20 > 2005-06-04 21:48 tgl >=20 > * src/backend/commands/: variable.c (REL7_3_STABLE), variable.c > (REL7_4_STABLE), variable.c (REL8_0_STABLE), variable.c: Code for > SET/SHOW TIME ZONE with a fixed-interval timezone was not prepared > for HAVE_INT64_TIMESTAMP. Per report from Guillaume Beaudoin. Actually fixing that bug is indeed the most preferred solution. :-) Thanks a lot for this patch. I just got the impression that integer timestamps were doomed... Thanks and have a nice day, Martin --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org
Martin Pitt <martin@piware.de> writes:
> I just thought about how to provide a transition path if the long-term
> solution really was to get rid of integer time stamps.
I don't think anyone has suggested doing that. In fact, there's a camp
that wishes they'd become the default. I don't see that happening soon
either, in part because it seems there are still too many bugs to be
flushed out :-(. But *somebody* has to exercise the code in order to
flush out the bugs, and Debian has elected to be the pioneers here.
regards, tom lane