On Sat, Jan 30, 2016 at 11:06:10PM -0800, Vitaly Burovoy wrote:
> Hackers,
>
> I've just found a little bug: extracting "epoch" from the last 30
> years before Postgres' "+Infinity" leads an integer overflow:
>
> postgres=# SELECT x::timestamptz, extract(epoch FROM x::timestamptz)
> postgres-# FROM
> postgres-# (VALUES
> postgres(# ('294247-01-10 04:00:54.775805'),
> postgres(# ('294247-01-10 04:00:55.775806'),
> postgres(# ('294277-01-09 04:00:54.775806'), -- the last value before 'Inf'
> postgres(# ('294277-01-09 04:00:54.775807') -- we've discussed, it
> should be fixed
> postgres(# ) as t(x);
> x | date_part
> ---------------------------------+-------------------
> 294247-01-10 04:00:54.775805+00 | 9223372036854.78
> 294247-01-10 04:00:55.775806+00 | -9223372036853.78
> 294277-01-09 04:00:54.775806+00 | -9222425352054.78
> infinity | Infinity
> (4 rows)
>
> With the attached patch it becomes positive:
> x | date_part
> ---------------------------------+------------------
> 294247-01-10 04:00:54.775805+00 | 9223372036854.78
> 294247-01-10 04:00:55.775806+00 | 9223372036855.78
> 294277-01-09 04:00:54.775806+00 | 9224318721654.78
> infinity | Infinity
> (4 rows)
FYI, in 9.6 this will return an error:
test=> SELECT x::timestamptz, extract(epoch FROM x::timestamptz)FROM(VALUES('294247-01-10
04:00:54.775805'),('294247-01-1004:00:55.775806'),('294277-01-09 04:00:54.775806'), -- the last value before
'Inf'('294277-01-0904:00:54.775807') -- we've discussed, it) as t(x);ERROR: timestamp out of range: "294277-01-09
04:00:54.775806"
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +