Re: [HACKERS] regression failure - horology - Mailing list pgsql-patches

> Joe Conway <mail@joeconway.com> writes:
> > I'm seeing a regression failure on the horology test on two different
> > machines. I'd venture a guess that it is related to this change:
> >    http://archives.postgresql.org/pgsql-committers/2003-02/msg00166.php
>
> It seems to be a problem with signed vs unsigned 8-byte integer
> timestamps.  Now that I look at it, the patch actually advertises this:
>
>      When timestamps are stored as eight-byte integers (a compile-time
>      option), microsecond precision is available over the full range of
>      values. However eight-byte integer timestamps have a reduced range
>      of dates from 4713 BC up to 294276 AD.
>
> which seems to make it rather foolish to include horology tests for
> dates past 294276 AD.
>
> John, you need to rethink this.  Regression tests that fail with
> --enable-integer-datetimes will not do.  Is it even a good idea to claim
> a range of dates up to 5874897 AD?  The 8-byte-int representation is
> probably the wave of the future.  As such, anything it can't do is
> not something we want to commit to supporting.
>
>             regards, tom lane

I would suggest reducing the documented range of timestamps to what is
available using integer timestamps. The actual code changes to j2date()
and date2j() are still correct. The problem in regression checks lies in
the fact that they tested for the 1st time the advertised range in timestamps.
Prior to my patch the advertised range for timestamps was from 4713BC to
1465001AD, but the regression checks never came close to either limit.
After, my patch the new limit was increased to 5874897 AD and I adjusted
the regression checks to actually test the new limit.

The regression failure using integer timestamps would still have happened
if the regression checks tested for timestamps near 1465001AD.

My suggestion is to reduce the range of timestamps tested for the regression
checks to what is capable for integer timestamps. I would also suggest
using a different format for integer timestamps from the current representation
of number of microseconds from Jan 1, 2000 to an alternative represtation of
the number of microseconds from Jan 1, 287000. The alternative epoch offset
would allow the integer timestamps to represent all values from
4713BC to 579277AD instead of the current range of 4713BC to 294276 AD.

The reason for the current epoch of 2000AD for floating point timestamps
is to retain as many bits of precision to allow for representing microsecond
timestamps for the recent past and near future. Using a IEEE-754 double
precision floating point (53 bits of mantissa), we have microsecond
precision for the interval ranging from 1821AD to 2179AD. As we go outside
that range, the available precision drops.

Back out the changes to the regression tests, but leave the code and
documentation changes alone.