On 2/2/16, Tom Lane wrote:
> [ Please use a useful Subject: line in your posts. ]
I'm so sorry, it was the first time I had forgotten to look at the
"Subject" field before I pressed the "Send" button.
> Vitaly Burovoy writes:
>> I've just found a little bug: extracting "epoch" from the last 30
>> years before Postgres' "+Infinity" leads an integer overflow:
>
> Hmm. I do not like the proposed patch much: it looks like it's
> throwing away precision too soon, although given that the result of
> SetEpochTimestamp can be cast to float exactly, maybe it doesn't matter.
>
> More importantly, I seriously doubt that this is the only issue
> for timestamps very close to the INT64_MAX boundary. An example is
> that we're not rejecting values that would correspond to DT_NOBEGIN
> or DT_NOEND:
>
> regression=# set timezone = 'PST8PDT';
> SET
> regression=# select '294277-01-08 20:00:54.775806-08'::timestamptz;
>timestamptz
> -
> 294277-01-08 20:00:54.775806-08
> (1 row)
>
> regression=# select '294277-01-08 20:00:54.775807-08'::timestamptz;
> timestamptz
> -
> infinity
> (1 row)
>
> regression=# select '294277-01-08 20:00:54.775808-08'::timestamptz;
> timestamptz
> -
> -infinity
> (1 row)
>
> regression=# select '294277-01-08 20:00:54.775809-08'::timestamptz;
> ERROR: timestamp out of range
>
> Worse yet, that last error is coming from timestamptz_out, not
> timestamptz_in; we're accepting a value we cannot store properly.
> The converted value has actually overflowed to be equal to
> INT64_MIN+1, and then timestamptz_out barfs because it's before
> Julian day 0. Other operations would incorrectly interpret it
> as a date in the very far past. timestamptz_in doesn't throw an
> error until several hours later than this; it looks like the
> problem is that tm2timestamp() worries about overflow in initially
> calculating the converted value, but not about overflow in the
> dt2local() rotation, and in any case it doesn't worry about not
> producing DT_NOEND.
It is clear why it happens, and it was in my plans to insert checks
there according to the thread[1].
> I'm inclined to think that a good solution would be to create an
> artificial restriction to not accept years beyond, say, 10 AD.
Well... We can limit it to the boundaries described at the
documentation page[2]: [4713 BC, 294276 AD].
It allows us be sure we will not break stamps that are stored (for any
reason) according to the documentation (in meaning of infinity, but
not exactly as 'infinity'::timestamptz).
Currently boundaries for timestamp[tz] are [4714-11-24+00 BC,
294277-01-09 04:00:54.775806+00]. One month to the lower boundary and
9 days to the upper one should be enough to represent it into int64
before applying time zone (+-15 hours) and check for boundaries
without an overflow.
> That would leave us with a lot of daylight to not have to worry
> about corner-case overflows in timestamp arithmetic.
Great! It was my next question because I desperated to find a solution
for finding a good corner-case for internal version of the
"to_timestamp" function (my not published yet WIP patch) that supports
+-Infinity::float8 as input to be symmertric with current
"extract('epoch'..."[3].
The exact value for current allowed maximal value ("294277-01-09
04:00:54.775806+00") should be 9224318721654.775806, but it cannot be
represented as float8. My experiments show there is "big" gap in
miliseconds (~0.002, but not 0.01):
src |representation
--+
9224318721654.774414 | 9224318721654.7734375
9224318721654.774415 | 9224318721654.775390625
9224318721654.776367 | 9224318721654.775390625
9224318721654.776368 | 9224318721654.77734375
9224318721654.778320 | 9224318721654.77734375
9224318721654.778321 | 9224318721654.779296875
So if it is possible to set an upper limit exact by a year boundary it
solves a lot of nerves.
> I'm not sure
> though where we'd need to enforce such a restriction; certainly in
> timestamp[tz]_in, but where else?
>
> regards, tom lane
What to do with dates: [4713 BC, 5874897 AD]? Limit them to stamps
boundaries or leave them as is and forbid a conversion if they don't
fit into stamps?
There is also trouble with intervals. Currently it is impossible to do
(even without the overflow on extracting):
postgres=# select extract (epoch from '294277-01-09
04:00:54.775806+00'::timestamptz);
date_part
--
9224318721654.78
(1 row)
postgres=# select to_timestamp(9224318721654.775390625); -- because of
..654.78 is rounded up; but we know the exact value
to_timestamp
-
294277-01-09 04:00:54.77539+00
(1 row)
... you get the error:
postgres=# select to_timestamp(9224318721654.775390625);
ERROR: interval out of range
CONTEXT: SQL function "to_timestamp" statement 1
... at the operation "$1 * '1 second'::pg_ca
