Integer datetimes

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.

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/

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

Peter Eisentraut <peter_e(at)gmx(dot)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

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

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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

Yes.

-Neil

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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.

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

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

Alvaro Herrera <alvherre(at)commandprompt(dot)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

Tom Lane napsal(a):
> Alvaro Herrera <alvherre(at)commandprompt(dot)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

Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)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

Tom Lane napsal(a):
> Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)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

Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)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

Tom Lane napsal(a):
> Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)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