Horton

Thread

  1. Integer datetimes

    Neil Conway <neilc@samurai.com> — 2007-05-05T07:22:31Z

    What is the reasoning behind having two different implementations of the
datetime types, with slightly different behavior? Do we intend to keep
supporting both FP- and integer-based datetimes indefinitely?

Clearly, there are some costs associated with maintaining two different
implementations:

(1) It means we need to maintain two sets of code, with a corresponding
increase in the maintenance burden, the probability of introducing bugs,
etc., and making datetime behavior more difficult to test.

(2) In general, I think it is a fundamentally *bad* idea to have the
semantics of a builtin data type differ subtly depending on the value of
a configure parameter. It makes writing portable applications more
difficult, and can introduce hard-to-fix bugs.

So, are there any corresponding benefits to providing both FP and
integer datetimes? AFAIK the following differences in user-visible
behavior exist:

    * integer timestamps have the same precision over their entire range
      (microsecond precision), whereas FP timestamps do not. This is
      clearly an advantage for integer timestamps.

    * integer timestamps have a smaller range than FP timestamps
      (294276 AD vs. 5874897 AD). Are there actually applications
      that use timestamps larger than 300,000 AD?

Unless there are lots of applications that need timestamps over such a
large range, ISTM integer datetimes are the better long-term approach,
and I don't see how the FP-based datetime code justifies the maintenance
burden. Notably, the FP datetime code doesn't depend on having a
functional int64 type, but in 2007, are there really any platforms we
care about that don't have such a type?

Therefore, I propose that we make integer datetimes the default (perhaps
for 8.4), and then eventually remove the floating-point datetime code.

Comments?

-Neil

P.S. One thing to verify is that the performance of integer datetimes is
no worse than the perf. of FP datetimes. I'd intuitively expect this to
be true, but it would be worth investigating.

  2. Re: Integer datetimes

    Peter Eisentraut <peter_e@gmx.net> — 2007-05-05T08:46:27Z

    Neil Conway wrote:
> Notably, the FP datetime code doesn't depend on having a
> functional int64 type, but in 2007, are there really any platforms we
> care about that don't have such a type?

That is really the only question, AFAIR.  The integer datetimes 
implementation on a 32-bit type would have a range of about 1 hour (or 
about 1 month, if you reduce it to millisecond precision), which would 
make it totally useless.

If we wanted to move toward requiring a 64-bit type, we should put some 
big warning into configure now that yells at the user if they don't 
have that type.  And if no one complains, we can make it a requirement 
in a later release.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

  3. Re: Integer datetimes

    Andrew Dunstan <andrew@dunslane.net> — 2007-05-05T10:41:37Z

    Peter Eisentraut wrote:
> Neil Conway wrote:
>> Notably, the FP datetime code doesn't depend on having a
>> functional int64 type, but in 2007, are there really any platforms we
>> care about that don't have such a type?
>
> That is really the only question, AFAIR.  The integer datetimes
> implementation on a 32-bit type would have a range of about 1 hour (or
> about 1 month, if you reduce it to millisecond precision), which would
> make it totally useless.
>
> If we wanted to move toward requiring a 64-bit type, we should put some
> big warning into configure now that yells at the user if they don't
> have that type.  And if no one complains, we can make it a requirement
> in a later release.
>


Can we discover anything useful from existing configure logs? If so, maybe
we can survey the buildfarm database.

Incidentally, use of integer datetimes has been in the default config set
on the buildfarm from day one, because it seems to me far saner, in
principle, to use fixed precision for them, so I cerainly agree with
Neil's goal.

cheers

andrew

  4. Re: Integer datetimes

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-05-05T15:03:37Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> Neil Conway wrote:
>> Notably, the FP datetime code doesn't depend on having a
>> functional int64 type, but in 2007, are there really any platforms we
>> care about that don't have such a type?

> That is really the only question, AFAIR.

We've so far managed to avoid having any hard dependency on a working
int64 type, but this would certainly be one.  I don't really think the
code-size-reduction argument is strong enough to justify that.  The
datetime code seems relatively stable at this point, so the maintenance
overhead of the code as it stands is not high.

I'm not necessarily opposed to changing the default configure selection,
but I am opposed to removing the FP code entirely.

			regards, tom lane

  5. Re: Integer datetimes

    Neil Conway <neilc@samurai.com> — 2007-05-05T15:38:07Z

    On Sat, 2007-05-05 at 11:03 -0400, Tom Lane wrote:
> We've so far managed to avoid having any hard dependency on a working
> int64 type, but this would certainly be one.  I don't really think the
> code-size-reduction argument is strong enough to justify that.

What benefit do we get from avoiding this dependency? Can we really
avoid a dependency on a 64-bit integral type in the long run?

> I'm not necessarily opposed to changing the default configure selection,
> but I am opposed to removing the FP code entirely.

I would be satisfied with changing the default to integer and
deprecating the FP code (but keeping it around as a configure option).
Are there any objections to doing this for 8.3?

-Neil

  6. Re: Integer datetimes

    Zdenek Kotala <zdenek.kotala@sun.com> — 2007-05-05T16:42:20Z

    Neil Conway wrote:

> So, are there any corresponding benefits to providing both FP and
> integer datetimes? AFAIK the following differences in user-visible
> behavior exist:
> 

There should be also problem with floating point implementation on 
client and server side. For example if somebody use floating point 
optimalization (-fast switch in Sun Studio) for server compilation and 
client will be connected from another machine with standard floating 
point behavior. Result could be wrong.

> 
> P.S. One thing to verify is that the performance of integer datetimes is
> no worse than the perf. of FP datetimes. I'd intuitively expect this to
> be true, but it would be worth investigating.

Some multi core/thread CPUs has only one FPU (e.g. Niagara).


		Zdenek

  7. Re: Integer datetimes

    Bruce Momjian <bruce@momjian.us> — 2007-05-06T00:52:02Z

    Zdenek Kotala wrote:
> Neil Conway wrote:
> 
> > So, are there any corresponding benefits to providing both FP and
> > integer datetimes? AFAIK the following differences in user-visible
> > behavior exist:
> > 
> 
> There should be also problem with floating point implementation on 
> client and server side. For example if somebody use floating point 
> optimalization (-fast switch in Sun Studio) for server compilation and 
> client will be connected from another machine with standard floating 
> point behavior. Result could be wrong.

What?  We don't pass float as a binary to clients.  The client can be
any OS.

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

  8. Re: Integer datetimes

    Neil Conway <neilc@samurai.com> — 2007-05-06T01:10:54Z

    On Sat, 2007-05-05 at 20:52 -0400, Bruce Momjian wrote:
> What?  We don't pass float as a binary to clients.

Sure we do, if the client is sending or receiving data in binary format.

-Neil

  9. Re: Integer datetimes

    Bruce Momjian <bruce@momjian.us> — 2007-05-06T01:28:30Z

    Neil Conway wrote:
> On Sat, 2007-05-05 at 20:52 -0400, Bruce Momjian wrote:
> > What?  We don't pass float as a binary to clients.
> 
> Sure we do, if the client is sending or receiving data in binary format.

But in those cases, we assume the client and server have the same
configuration, right?

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

  10. Re: Integer datetimes

    Andrew Dunstan <andrew@dunslane.net> — 2007-05-06T01:49:54Z

    
Bruce Momjian wrote:
> Neil Conway wrote:
>   
>> On Sat, 2007-05-05 at 20:52 -0400, Bruce Momjian wrote:
>>     
>>> What?  We don't pass float as a binary to clients.
>>>       
>> Sure we do, if the client is sending or receiving data in binary format.
>>     
>
> But in those cases, we assume the client and server have the same
> configuration, right?
>
>   

Certainly the client and server must have the same notion of the binary 
format.

cheers

andrew

  11. Re: Integer datetimes

    Decibel! <decibel@decibel.org> — 2007-05-06T14:39:42Z

    On May 5, 2007, at 10:38 AM, Neil Conway wrote:
> On Sat, 2007-05-05 at 11:03 -0400, Tom Lane wrote:
>> I'm not necessarily opposed to changing the default configure  
>> selection,
>> but I am opposed to removing the FP code entirely.
>
> I would be satisfied with changing the default to integer and
> deprecating the FP code (but keeping it around as a configure option).
> Are there any objections to doing this for 8.3?

One question... I've always assumed that FP date times suffers from  
the inexact math issues that floats do; is that true?

The only use I can think of for huge date values would be astronomy.  
I know they deal with huge numbers, so maybe huge times as well. If  
there is that kind of demand perhaps we'd want to continue supporting  
FP dates... maybe via contrib, or as a different base data type.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

  12. Re: Integer datetimes

    Bruce Momjian <bruce@momjian.us> — 2007-05-06T17:09:40Z

    Jim Nasby wrote:
> On May 5, 2007, at 10:38 AM, Neil Conway wrote:
> > On Sat, 2007-05-05 at 11:03 -0400, Tom Lane wrote:
> >> I'm not necessarily opposed to changing the default configure  
> >> selection,
> >> but I am opposed to removing the FP code entirely.
> >
> > I would be satisfied with changing the default to integer and
> > deprecating the FP code (but keeping it around as a configure option).
> > Are there any objections to doing this for 8.3?
> 
> One question... I've always assumed that FP date times suffers from  
> the inexact math issues that floats do; is that true?
> 
> The only use I can think of for huge date values would be astronomy.  
> I know they deal with huge numbers, so maybe huge times as well. If  
> there is that kind of demand perhaps we'd want to continue supporting  
> FP dates... maybe via contrib, or as a different base data type.

Also, are we sure we can load a dump that used the float format?  What
happens for a date out of int8 range?

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

  13. Re: Integer datetimes

    Neil Conway <neilc@samurai.com> — 2007-05-06T18:31:53Z

    On Sun, 2007-06-05 at 13:09 -0400, Bruce Momjian wrote:
> Also, are we sure we can load a dump that used the float format?  What
> happens for a date out of int8 range?

AFAIK we should always be able to reload timestamp values that are in
the legal range for an int8-based timestamp. For values outside that
range, the restore will fail, just as it would if you tried to move an
application from PG 8.2 with float timestamps to PG 8.2 with integer
timestamps. The user can always reconfigure with
--disable-integer-datetimes.

-Neil

  14. Re: Integer datetimes

    Zdenek Kotala <zdenek.kotala@sun.com> — 2007-05-07T06:28:40Z

    Bruce Momjian wrote:
> Neil Conway wrote:
>> On Sat, 2007-05-05 at 20:52 -0400, Bruce Momjian wrote:
>>> What?  We don't pass float as a binary to clients.
>> Sure we do, if the client is sending or receiving data in binary format.
> 
> But in those cases, we assume the client and server have the same
> configuration, right?
> 

It is correct assumption, but I did not find it in documentation and if 
you look on floating data type description there is mention about non 
IEEE 754 platform, but nothing about this assumption.

I think IEEE 754 compliance must be required on all platforms.


		Zdenek

  15. Re: Integer datetimes

    Bruce Momjian <bruce@momjian.us> — 2007-05-16T15:25:01Z

    Are we agreed to wait for 8.4 for this?

---------------------------------------------------------------------------

Neil Conway wrote:
> What is the reasoning behind having two different implementations of the
> datetime types, with slightly different behavior? Do we intend to keep
> supporting both FP- and integer-based datetimes indefinitely?
> 
> Clearly, there are some costs associated with maintaining two different
> implementations:
> 
> (1) It means we need to maintain two sets of code, with a corresponding
> increase in the maintenance burden, the probability of introducing bugs,
> etc., and making datetime behavior more difficult to test.
> 
> (2) In general, I think it is a fundamentally *bad* idea to have the
> semantics of a builtin data type differ subtly depending on the value of
> a configure parameter. It makes writing portable applications more
> difficult, and can introduce hard-to-fix bugs.
> 
> So, are there any corresponding benefits to providing both FP and
> integer datetimes? AFAIK the following differences in user-visible
> behavior exist:
> 
>     * integer timestamps have the same precision over their entire range
>       (microsecond precision), whereas FP timestamps do not. This is
>       clearly an advantage for integer timestamps.
> 
>     * integer timestamps have a smaller range than FP timestamps
>       (294276 AD vs. 5874897 AD). Are there actually applications
>       that use timestamps larger than 300,000 AD?
> 
> Unless there are lots of applications that need timestamps over such a
> large range, ISTM integer datetimes are the better long-term approach,
> and I don't see how the FP-based datetime code justifies the maintenance
> burden. Notably, the FP datetime code doesn't depend on having a
> functional int64 type, but in 2007, are there really any platforms we
> care about that don't have such a type?
> 
> Therefore, I propose that we make integer datetimes the default (perhaps
> for 8.4), and then eventually remove the floating-point datetime code.
> 
> Comments?
> 
> -Neil
> 
> P.S. One thing to verify is that the performance of integer datetimes is
> no worse than the perf. of FP datetimes. I'd intuitively expect this to
> be true, but it would be worth investigating.
> 
> 
> 
> ---------------------------(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

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

  16. Re: Integer datetimes

    Neil Conway <neilc@samurai.com> — 2007-05-16T15:40:17Z

    On Wed, 2007-16-05 at 11:25 -0400, Bruce Momjian wrote:
> Are we agreed to wait for 8.4 for this?

Yes.

-Neil

  17. Re: Integer datetimes

    Bruce Momjian <bruce@momjian.us> — 2007-05-16T15:46:11Z

    This has been saved for the 8.4 release:

	http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Neil Conway wrote:
> What is the reasoning behind having two different implementations of the
> datetime types, with slightly different behavior? Do we intend to keep
> supporting both FP- and integer-based datetimes indefinitely?
> 
> Clearly, there are some costs associated with maintaining two different
> implementations:
> 
> (1) It means we need to maintain two sets of code, with a corresponding
> increase in the maintenance burden, the probability of introducing bugs,
> etc., and making datetime behavior more difficult to test.
> 
> (2) In general, I think it is a fundamentally *bad* idea to have the
> semantics of a builtin data type differ subtly depending on the value of
> a configure parameter. It makes writing portable applications more
> difficult, and can introduce hard-to-fix bugs.
> 
> So, are there any corresponding benefits to providing both FP and
> integer datetimes? AFAIK the following differences in user-visible
> behavior exist:
> 
>     * integer timestamps have the same precision over their entire range
>       (microsecond precision), whereas FP timestamps do not. This is
>       clearly an advantage for integer timestamps.
> 
>     * integer timestamps have a smaller range than FP timestamps
>       (294276 AD vs. 5874897 AD). Are there actually applications
>       that use timestamps larger than 300,000 AD?
> 
> Unless there are lots of applications that need timestamps over such a
> large range, ISTM integer datetimes are the better long-term approach,
> and I don't see how the FP-based datetime code justifies the maintenance
> burden. Notably, the FP datetime code doesn't depend on having a
> functional int64 type, but in 2007, are there really any platforms we
> care about that don't have such a type?
> 
> Therefore, I propose that we make integer datetimes the default (perhaps
> for 8.4), and then eventually remove the floating-point datetime code.
> 
> Comments?
> 
> -Neil
> 
> P.S. One thing to verify is that the performance of integer datetimes is
> no worse than the perf. of FP datetimes. I'd intuitively expect this to
> be true, but it would be worth investigating.
> 
> 
> 
> ---------------------------(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

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

  18. Re: Integer datetimes

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-03-20T23:05:35Z

    Neil Conway wrote:

> Therefore, I propose that we make integer datetimes the default (perhaps
> for 8.4), and then eventually remove the floating-point datetime code.

Neil, you're on the loop for changing the default in configure.  Want to
do the honors?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

  19. Re: Integer datetimes

    Neil Conway <neilc@samurai.com> — 2008-03-20T23:07:27Z

    On Thu, 2008-03-20 at 20:05 -0300, Alvaro Herrera wrote:
> Neil, you're on the loop for changing the default in configure.  Want to
> do the honors?

Sure -- I sent in a patch earlier, but I'll post an updated version
shortly.

-Neil

  20. Re: Integer datetimes

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-03-20T23:41:57Z

    Neil Conway wrote:
> On Thu, 2008-03-20 at 20:05 -0300, Alvaro Herrera wrote:
> > Neil, you're on the loop for changing the default in configure.  Want to
> > do the honors?
> 
> Sure -- I sent in a patch earlier, but I'll post an updated version
> shortly.

Hmm, I mean just switching the default value in configure.in ... is
there anything else that needs doing at this point?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

  21. Re: Integer datetimes

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-03-21T00:09:10Z

    Alvaro Herrera <alvherre@commandprompt.com> writes:
> Neil Conway wrote:
>> Sure -- I sent in a patch earlier, but I'll post an updated version
>> shortly.

> Hmm, I mean just switching the default value in configure.in ... is
> there anything else that needs doing at this point?

Well, that's hardly a one-liner.  If nothing else there are probably
multiple places in the documentation that need adjustment.

			regards, tom lane

  22. Re: Integer datetimes

    Zdenek Kotala <zdenek.kotala@sun.com> — 2008-03-21T09:36:34Z

    Tom Lane napsal(a):
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Neil Conway wrote:
>>> Sure -- I sent in a patch earlier, but I'll post an updated version
>>> shortly.
> 
>> Hmm, I mean just switching the default value in configure.in ... is
>> there anything else that needs doing at this point?
> 
> Well, that's hardly a one-liner.  If nothing else there are probably
> multiple places in the documentation that need adjustment.
> 

Does we keep same OID for this type? I prefer to redefine it as new type. It 
helps to determine which type is it for upgrade purpose. I think best solution 
is to have both code floating/int active and configure or initdb only perform 
correct linking in BKI file to preferred variant.

The result will be two datatypes datetime and timestamp_int or timestamp_float.
Or maybe better is to have two datatypes timestamp_int timestamp_float and one 
domain timestamp which points to preferred type.


		Zdenek

  23. Re: Integer datetimes

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-03-21T15:18:50Z

    Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> The result will be two datatypes datetime and timestamp_int or timestamp_float.

This is not happening, at least not without 100 times more work than
anyone has shown willingness to put into the issue.  It seems fairly
clear that everyone thinks the int64 datatypes will be the mainstream
usage, and the float implementation will be interesting only on very
old platforms or for very specialized applications.  I cannot see us
putting in the effort needed to refactor the code as two
simultaneously-available datatypes.

			regards, tom lane

  24. Re: Integer datetimes

    Zdenek Kotala <zdenek.kotala@sun.com> — 2008-03-24T21:19:45Z

    Tom Lane napsal(a):
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> The result will be two datatypes datetime and timestamp_int or timestamp_float.
> 
> This is not happening, at least not without 100 times more work than
> anyone has shown willingness to put into the issue.  It seems fairly
> clear that everyone thinks the int64 datatypes will be the mainstream
> usage, and the float implementation will be interesting only on very
> old platforms or for very specialized applications.  I cannot see us
> putting in the effort needed to refactor the code as two
> simultaneously-available datatypes.

I understand your arguments, but it is important for in-place upgrade. If you 
will start new postgreSQL on e.g 8.3 database cluster, you need method how to 
process floating point timestamp. Minimal we need to have cast from floating 
point timestamp to integer timestamp. I put it on my ToDo with low priority.

		Zdenek

  25. Re: Integer datetimes

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-03-24T22:02:35Z

    Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> Tom Lane napsal(a):
>> This is not happening, at least not without 100 times more work than
>> anyone has shown willingness to put into the issue.

> I understand your arguments, but it is important for in-place upgrade.

No, it is not, you merely need to be sure the new version is configured
the same as the old ... which is a requirement anyway.

			regards, tom lane

  26. Re: Integer datetimes

    Zdenek Kotala <zdenek.kotala@sun.com> — 2008-03-25T08:15:37Z

    Tom Lane napsal(a):
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> Tom Lane napsal(a):
>>> This is not happening, at least not without 100 times more work than
>>> anyone has shown willingness to put into the issue.
> 
>> I understand your arguments, but it is important for in-place upgrade.
> 
> No, it is not, you merely need to be sure the new version is configured
> the same as the old ... which is a requirement anyway.

It depends. For example, currently postgreSQL allows only collation setting per 
database, but somebody will develop collation per database or column. After that 
the situation will be different. Upgrade have to transfer collation from control 
file to catalog and so on. Configuration will be different but there will be 
possible transition.

Probably all packager will delivery 8.4 and newer with integer datetime, but 
people who will use in-place upgrade they will not use official package. I think 
it is not good idea.

		Zdenek