Re: Bug#311533: Invalid timestamp returned because of timezone

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Bug#311533: Invalid timestamp returned because of timezone

Martin Pitt-2
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]:

>
> Package: postgresql
> Version: 7.4.7-6sarge1
>
> 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.
>
>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

--
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Bug#311533: Invalid timestamp returned because of timezone

Andrew - Supernews
On 2005-06-10, Martin Pitt <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Bug#311533: Invalid timestamp returned because of timezone

Martin Pitt-2
Hi!

Andrew - Supernews [2005-06-10 23:29 -0000]:

> On 2005-06-10, Martin Pitt <[hidden email]> 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.
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?
>
> 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
   "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

--
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Bug#311533: Invalid timestamp returned because of timezone

Tom Lane-2
Martin Pitt <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Bug#311533: Invalid timestamp returned because of timezone

Martin Pitt-2
Hi Tom!

Tom Lane [2005-06-11 13:13 -0400]:
> Martin Pitt <[hidden email]> 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.

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.  

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

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

--
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Bug#311533: Invalid timestamp returned because of timezone

Tom Lane-2
Martin Pitt <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend