8000 Support +/- infinity in the interval data type. · postgres/postgres@519fc1b · GitHub
[go: up one dir, main page]
Skip to content

Commit 519fc1b

Browse files
deanrasheeddeanrasheed
committed
Support +/- infinity in the interval data type.
This adds support for infinity to the interval data type, using the same input/output representation as the other date/time data types that support infinity. This allows various arithmetic operations on infinite dates, timestamps and intervals. The new values are represented by setting all fields of the interval to INT32/64_MIN for -infinity, and INT32/64_MAX for +infinity. This ensures that they compare as less/greater than all other interval values, without the need for any special-case comparison code. Note that, since those 2 values were formerly accepted as legal finite intervals, pg_upgrade and dump/restore from an old database will turn them from finite to infinite intervals. That seems OK, since those exact values should be extremely rare in practice, and they are outside the documented range supported by the interval type, which gives us a certain amount of leeway. Bump catalog version. Joseph Koshakow, Jian He, and Ashutosh Bapat, reviewed by me. Discussion: https://postgr.es/m/CAAvxfHea4%2BsPybKK7agDYOMo9N-Z3J6ZXf3BOM79pFsFNcRjwA%40mail.gmail.com
1 parent b41b1a7 commit 519fc1b

File tree

25 files changed

+2541
-297
lines changed

25 files changed

+2541
-297
lines changed

‎contrib/btree_gin/btree_gin.c

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -306,9 +306,8 @@ leftmostvalue_interval(void)
306306
{
307307
Interval *v = palloc(sizeof(Interval));
308308

309-
v->time = PG_INT64_MIN;
310-
v->day = PG_INT32_MIN;
311-
v->month = PG_INT32_MIN;
309+
INTERVAL_NOBEGIN(v);
310+
312311
return IntervalPGetDatum(v);
313312
}
314313

‎doc/src/sgml/datatype.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2328,12 +2328,12 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
23282328
</row>
23292329
<row>
23302330
<entry><literal>infinity</literal></entry>
2331-
<entry><type>date</type>, <type>timestamp</type></entry>
2331+
<entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
23322332
<entry>later than all other time stamps</entry>
23332333
</row>
23342334
<row>
23352335
<entry><literal>-infinity</literal></entry>
2336-
<entry><type>date</type>, <type>timestamp</type></entry>
2336+
<entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
23372337
<entry>earlier than all other time stamps</entry>
23382338
</row>
23392339
<row>

‎doc/src/sgml/func.sgml

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9565,7 +9565,7 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
95659565
<returnvalue>boolean</returnvalue>
95669566
</para>
95679567
<para>
9568-
Test for finite interval (currently always true)
9568+
Test for finite interval (not +/-infinity)
95699569
</para>
95709570
<para>
95719571
<literal>isfinite(interval '4 hours')</literal>
@@ -10462,7 +10462,11 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
1046210462
When the input value is +/-Infinity, <function>extract</function> returns
1046310463
+/-Infinity for monotonically-increasing fields (<literal>epoch</literal>,
1046410464
<literal>julian</literal>, <literal>year</literal>, <literal>isoyear</literal>,
10465-
<literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>).
10465+
<literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>
10466+
for <type>timestamp</type> inputs; <literal>epoch</literal>, <literal>hour</literal>,
10467+
<literal>day</literal>, <literal>year</literal>, <literal>decade</literal>,
10468+
<literal>century</literal>, and <literal>millennium</literal> for
10469+
<type>interval</type> inputs).
1046610470
For other fields, NULL is returned. <productname>PostgreSQL</productname>
1046710471
versions before 9.6 returned zero for all cases of infinite input.
1046810472
</para>

‎src/backend/utils/adt/date.c

Lines changed: 47 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include "access/xact.h"
2525
#include "catalog/pg_type.h"
2626
#include "common/hashfn.h"
27+
#include "common/int.h"
2728
#include "libpq/pqformat.h"
2829
#include "miscadmin.h"
2930
#include "nodes/supportnodes.h"
@@ -2013,6 +2014,11 @@ interval_time(PG_FUNCTION_ARGS)
20132014
Interval *span = PG_GETARG_INTERVAL_P(0);
20142015
TimeADT result;
20152016

2017+
if (INTERVAL_NOT_FINITE(span))
2018+
ereport(ERROR,
2019+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2020+
errmsg("cannot convert infinite interval to time")));
2021+
20162022
result = span->time % USECS_PER_DAY;
20172023
if (result < 0)
20182024
result += USECS_PER_DAY;
@@ -2049,6 +2055,11 @@ time_pl_interval(PG_FUNCTION_ARGS)
20492055
Interval *span = PG_GETARG_INTERVAL_P(1);
20502056
TimeADT result;
20512057

2058+
if (INTERVAL_NOT_FINITE(span))
2059+
ereport(ERROR,
2060+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2061+
errmsg("cannot add infinite interval to time")));
2062+
20522063
result = time + span->time;
20532064
result -= result / USECS_PER_DAY * USECS_PER_DAY;
20542065
if (result < INT64CONST(0))
@@ -2067,6 +2078,11 @@ time_mi_interval(PG_FUNCTION_ARGS)
20672078
Interval *span = PG_GETARG_INTERVAL_P(1);
20682079
TimeADT result;
20692080

2081+
if (INTERVAL_NOT_FINITE(span))
2082+
ereport(ERROR,
2083+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2084+
errmsg("cannot subtract infinite interval from time")));
2085+
20702086
result = time - span->time;
20712087
result -= result / USECS_PER_DAY * USECS_PER_DAY;
20722088
if (result < INT64CONST(0))
@@ -2090,7 +2106,8 @@ in_range_time_interval(PG_FUNCTION_ARGS)
20902106

20912107
/*
20922108
* Like time_pl_interval/time_mi_interval, we disregard the month and day
2093-
* fields of the offset. So our test for negative should too.
2109+
* fields of the offset. So our test for negative should too. This also
2110+
* catches -infinity, so we only need worry about +infinity below.
20942111
*/
20952112
if (offset->time < 0)
20962113
ereport(ERROR,
@@ -2100,13 +2117,14 @@ in_range_time_interval(PG_FUNCTION_ARGS)
21002117
/*
21012118
* We can't use time_pl_interval/time_mi_interval here, because their
21022119
* wraparound behavior would give wrong (or at least undesirable) answers.
2103-
* Fortunately the equivalent non-wrapping behavior is trivial, especially
2104-
* since we don't worry about integer overflow.
2120+
* Fortunately the equivalent non-wrapping behavior is trivial, except
2121+
* that adding an infinite (or very large) interval might cause integer
2122+
* overflow. Subtraction cannot overflow here.
21052123
*/
21062124
if (sub)
21072125
sum = base - offset->time;
2108-
else
2109-
sum = base + offset->time;
2126+
else if (pg_add_s64_overflow(base, offset->time, &sum))
2127+
PG_RETURN_BOOL(less);
21102128

21112129
if (less)
21122130
PG_RETURN_BOOL(val <= sum);
@@ -2581,6 +2599,11 @@ timetz_pl_interval(PG_FUNCTION_ARGS)
25812599
Interval *span = PG_GETARG_INTERVAL_P(1);
25822600
TimeTzADT *result;
25832601

2602+
if (INTERVAL_NOT_FINITE(span))
2603+
ereport(ERROR,
2604+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2605+
errmsg("cannot add infinite interval to time")));
2606+
25842607
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
25852608

25862609
result->time = time->time + span->time;
@@ -2603,6 +2626,11 @@ timetz_mi_interval(PG_FUNCTION_ARGS)
26032626
Interval *span = PG_GETARG_INTERVAL_P(1);
26042627
TimeTzADT *result;
26052628

2629+
if (INTERVAL_NOT_FINITE(span))
2630+
ereport(ERROR,
2631+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2632+
errmsg("cannot subtract infinite interval from time")));
2633+
26062634
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
26072635

26082636
result->time = time->time - span->time;
@@ -2630,7 +2658,8 @@ in_range_timetz_interval(PG_FUNCTION_ARGS)
26302658

26312659
/*
26322660
* Like timetz_pl_interval/timetz_mi_interval, we disregard the month and
2633-
* day fields of the offset. So our test for negative should too.
2661+
* day fields of the offset. So our test for negative should too. This
2662+
* also catches -infinity, so we only need worry about +infinity below.
26342663
*/
26352664
if (offset->time < 0)
26362665
ereport(ERROR,
@@ -2640,13 +2669,14 @@ in_range_timetz_interval(PG_FUNCTION_ARGS)
26402669
/*
26412670
* We can't use timetz_pl_interval/timetz_mi_interval here, because their
26422671
* wraparound behavior would give wrong (or at least undesirable) answers.
2643-
* Fortunately the equivalent non-wrapping behavior is trivial, especially
2644-
* since we don't worry about integer overflow.
2672+
* Fortunately the equivalent non-wrapping behavior is trivial, except
2673+
* that adding an infinite (or very large) interval might cause integer
2674+
* overflow. Subtraction cannot overflow here.
26452675
*/
26462676
if (sub)
26472677
sum.time = base->time - offset->time;
2648-
else
2649-
sum.time = base->time + offset->time;
2678+
else if (pg_add_s64_overflow(base->time, offset->time, &sum.time))
2679+
PG_RETURN_BOOL(less);
26502680
sum.zone = base->zone;
26512681

26522682
if (less)
@@ -3096,6 +3126,13 @@ timetz_izone(PG_FUNCTION_ARGS)
30963126
TimeTzADT *result;
30973127
int tz;
30983128

3129+
if (INTERVAL_NOT_FINITE(zone))
3130+
ereport(ERROR,
3131+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3132+
errmsg("interval time zone \"%s\" must be finite",
3133+
DatumGetCString(DirectFunctionCall1(interval_out,
3134+
PointerGetDatum(zone))))));
3135+
30993136
if (zone->month != 0 || zone->day != 0)
31003137
ereport(ERROR,
31013138
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

‎src/backend/utils/adt/datetime.c

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3271,6 +3271,9 @@ ClearPgItmIn(struct pg_itm_in *itm_in)
32713271
*
32723272
* Allow ISO-style time span, with implicit units on number of days
32733273
* preceding an hh:mm:ss field. - thomas 1998-04-30
3274+
*
3275+
* itm_in remains undefined for infinite interval values for which dtype alone
3276+
* suffices.
32743277
*/
32753278
int
32763279
DecodeInterval(char **field, int *ftype, int nf, int range,
@@ -3574,6 +3577,8 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
35743577
if (parsing_unit_val)
35753578
return DTERR_BAD_FORMAT;
35763579
type = DecodeUnits(i, field[i], &uval);
3580+
if (type == UNKNOWN_FIELD)
3581+
type = DecodeSpecial(i, field[i], &uval);
35773582
if (type == IGNORE_DTF)
35783583
continue;
35793584

@@ -3597,6 +3602,27 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
35973602
type = uval;
35983603
break;
35993604

3605+
case RESERV:
3606+
tmask = (DTK_DATE_M | DTK_TIME_M);
3607+
3608+
/*
3609+
* Only reserved words corresponding to infinite
3610+
* intervals are accepted.
3611+
*/
3612+
if (uval != DTK_LATE && uval != DTK_EARLY)
3613+
return DTERR_BAD_FORMAT;
3614+
3615+
/*
3616+
* Infinity cannot be followed by anything else. We
3617+
* could allow "ago" to reverse the sign of infinity
3618+
* but using signed infinity is more intuitive.
3619+
*/
3620+
if (i != nf - 1)
3621+
return DTERR_BAD_FORMAT;
3622+
3623+
*dtype = uval;
3624+
break;
3625+
36003626
default:
36013627
return DTERR_BAD_FORMAT;
36023628
}

‎src/backend/utils/adt/formatting.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4127,7 +4127,7 @@ interval_to_char(PG_FUNCTION_ARGS)
41274127
struct pg_itm tt,
41284128
*itm = &tt;
41294129

4130-
if (VARSIZE_ANY_EXHDR(fmt) <= 0)
4130+
if (VARSIZE_ANY_EXHDR(fmt) <= 0 || INTERVAL_NOT_FINITE(it))
41314131
PG_RETURN_NULL();
41324132

41334133
ZERO_tmtc(&tmtc);

‎src/backend/utils/adt/selfuncs.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4802,6 +4802,10 @@ convert_timevalue_to_scalar(Datum value, Oid typid, bool *failure)
48024802
* Convert the month part of Interval to days using assumed
48034803
* average month length of 365.25/12.0 days. Not too
48044804
* accurate, but plenty good enough for our purposes.
4805+
*
4806+
* This also works for infinite intervals, which just have all
4807+
* fields set to INT_MIN/INT_MAX, and so will produce a result
4808+
* smaller/larger than any finite interval.
48054809
*/
48064810
return interval->time + interval->day * (double) USECS_PER_DAY +
48074811
interval->month * ((DAYS_PER_YEAR / (double) MONTHS_PER_YEAR) * USECS_PER_DAY);

0 commit comments

Comments
 (0)
0