Hi Joseph,
I stumbled upon this requirement a few times. So I started working on
this support in my spare time as a hobby project to understand
horology code in PostgreSQL. This was sitting in my repositories for
more than an year. Now that I have someone else showing an interest,
it's time for it to face the world. Rebased it, fixed conflicts.
PFA patch implementing infinite interval. It's still WIP, there are
TODOs in the code and also the commit message lists things that are
known to be incomplete. You might want to assess expected output
carefully
On Sun, Dec 11, 2022 at 12:51 AM Joseph Koshakow <kosh...@gmail.com> wrote:>
> The proposed design from the most recent thread was to reserve
> INT32_MAX months for infinity and INT32_MIN months for negative
> infinity. As pointed out in the thread, these are currently valid
> non-infinite intervals, but they are out of the documented range.
The patch uses both months and days together to avoid this problem.
Please feel free to complete the patch, work on review comments etc. I
will help as and when I find time.
--
Best Wishes,
Ashutosh Bapat
From 09a8fef224a3682059fe1adf42957f693a41d242 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.ba...@2ndquadrant.com>
Date: Thu, 30 Apr 2020 10:06:49 +0530
Subject: [PATCH] Support infinite interval
This is WIP.
Following things are supported
1. Accepts '+/-infinity' as a valid string input for interval type.
2. Support interval_pl, interval_div
3. Tests in interval.sql for comparison operators working fine.
TODOs
1. Various TODOs in code
2. interval_pl: how to handle infinite values with opposite signs
3. timestamp, timestamptz, date and time arithmetic
4. Fix horology test.
Ashutosh Bapat
---
src/backend/utils/adt/datetime.c | 2 +
src/backend/utils/adt/timestamp.c | 166 ++++++++++++++++++++++++-
src/test/regress/expected/interval.out | 80 ++++++++++--
src/test/regress/sql/interval.sql | 8 ++
4 files changed, 242 insertions(+), 14 deletions(-)
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index b5b117a8ca..1e98c6dc78 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -3634,6 +3634,8 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
case DTK_STRING:
case DTK_SPECIAL:
type = DecodeUnits(i, field[i], &uval);
+ if (type == UNKNOWN_FIELD)
+ type = DecodeSpecial(i, field[i], &uval);
if (type == IGNORE_DTF)
continue;
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 3f2508c0c4..0c7286b06e 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -79,6 +79,12 @@ static bool AdjustIntervalForTypmod(Interval *interval, int32 typmod,
static TimestampTz timestamp2timestamptz(Timestamp timestamp);
static Timestamp timestamptz2timestamp(TimestampTz timestamp);
+static void EncodeSpecialInterval(Interval *interval, char *str);
+static void interval_noend(Interval *interval);
+static bool interval_is_noend(Interval *interval);
+static void interval_nobegin(Interval *interval);
+static bool interval_is_nobegin(Interval *interval);
+static bool interval_not_finite(Interval *interval);
/* common code for timestamptypmodin and timestamptztypmodin */
static int32
@@ -943,6 +949,14 @@ interval_in(PG_FUNCTION_ARGS)
errmsg("interval out of range")));
break;
+ case DTK_LATE:
+ interval_noend(result);
+ break;
+
+ case DTK_EARLY:
+ interval_nobegin(result);
+ break;
+
default:
elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",
dtype, str);
@@ -965,8 +979,13 @@ interval_out(PG_FUNCTION_ARGS)
*itm = &tt;
char buf[MAXDATELEN + 1];
- interval2itm(*span, itm);
- EncodeInterval(itm, IntervalStyle, buf);
+ if (interval_not_finite(span))
+ EncodeSpecialInterval(span, buf);
+ else
+ {
+ interval2itm(*span, itm);
+ EncodeInterval(itm, IntervalStyle, buf);
+ }
result = pstrdup(buf);
PG_RETURN_CSTRING(result);
@@ -1352,6 +1371,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod,
INT64CONST(0)
};
+ /*
+ * Infinite interval after being subjected to typmod conversion remains
+ * infinite.
+ */
+ if (interval_not_finite(interval))
+ return;
+
/*
* Unspecified range and precision? Then not necessary to adjust. Setting
* typmod to -1 is the convention for all data types.
@@ -1545,6 +1571,17 @@ EncodeSpecialTimestamp(Timestamp dt, char *str)
elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
}
+static void
+EncodeSpecialInterval(Interval *interval, char *str)
+{
+ if (interval_is_nobegin(interval))
+ strcpy(str, EARLY);
+ else if (interval_is_noend(interval))
+ strcpy(str, LATE);
+ else /* shouldn't happen */
+ elog(ERROR, "invalid argument for EncodeSpecialInterval");
+}
+
Datum
now(PG_FUNCTION_ARGS)
{
@@ -2080,10 +2117,12 @@ timestamp_finite(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(!TIMESTAMP_NOT_FINITE(timestamp));
}
+/* TODO: modify this to check finite-ness */
Datum
interval_finite(PG_FUNCTION_ARGS)
{
- PG_RETURN_BOOL(true);
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+ PG_RETURN_BOOL(!interval_not_finite(interval));
}
@@ -2926,8 +2965,27 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Timestamp result;
+ /*
+ * Adding finite interval to an infinite time is going to be infinite in
+ * the same direction. Adding infinte interval to infinite timestamp in the
+ * same direction results in an infinite timestamp in the same direction.
+ * Adding infinite interval to an infinite timestamp with opposite
+ * direction is not going to yield 0 but some infinity. Since we are adding
+ * interval to the timestamp the resultant timestamp is an infinity
+ * preserving the direction.
+ */
if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
+ else if (interval_not_finite(span))
+ {
+ if (interval_is_nobegin(span))
+ TIMESTAMP_NOBEGIN(result);
+ else
+ {
+ Assert(interval_is_noend(span));
+ TIMESTAMP_NOEND(result);
+ }
+ }
else
{
if (span->month != 0)
@@ -3032,8 +3090,27 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
TimestampTz result;
int tz;
+ /*
+ * Adding finite interval to an infinite time is going to be infinite in
+ * the same direction. Adding infinte interval to infinite timestamp in the
+ * same direction results in an infinite timestamp in the same direction.
+ * Adding infinite interval to an infinite timestamp with opposite
+ * direction is not going to yield 0 but some infinity. Since we are adding
+ * interval to the timestamp the resultant timestamp is an infinity
+ * preserving the direction.
+ */
if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
+ else if (interval_not_finite(span))
+ {
+ if (interval_is_nobegin(span))
+ TIMESTAMP_NOBEGIN(result);
+ else
+ {
+ Assert(interval_is_noend(span));
+ TIMESTAMP_NOEND(result);
+ }
+ }
else
{
if (span->month != 0)
@@ -3192,6 +3269,21 @@ interval_pl(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * TODO: What if both span1 and span2 are infinite and in oppposite
+ * direction?
+ */
+ if (interval_not_finite(span1))
+ {
+ memcpy(result, span1, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (interval_not_finite(span2))
+ {
+ memcpy(result, span2, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = span1->month + span2->month;
/* overflow check copied from int4pl */
if (SAMESIGN(span1->month, span2->month) &&
@@ -3362,6 +3454,14 @@ interval_div(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ /* Dividing infinite interval by finite number keeps it infinite. */
+ /* TODO: Do we change the sign of infinity if factor is negative? */
+ if (interval_not_finite(span))
+ {
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = (int32) (span->month / factor);
result->day = (int32) (span->day / factor);
@@ -5955,3 +6055,63 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * TODO: possibly we should move these to a place along with other interval_*
+ * functions.
+ */
+
+/* Set the given interval to indicate infinite interval in the future. */
+static void
+interval_noend(Interval *interval)
+{
+ interval->time = DT_NOEND;
+ interval->day = PG_INT32_MAX;
+ interval->month = PG_INT32_MAX;
+}
+
+/* Does the given interval indicate infinite interval in the future? */
+static bool
+interval_is_noend(Interval *interval)
+{
+ /*
+ * TODO: Possibly it makes sense to just check one of the fields to reduce
+ * the number of instructions here. But it's safer to check all the three
+ * fields.
+ */
+ return interval->time == DT_NOEND &&
+ interval->day == PG_INT32_MAX &&
+ interval->month == PG_INT32_MAX;
+}
+
+/* Set the given interval to indicate infinite interval in the past. */
+static void
+interval_nobegin(Interval *interval)
+{
+ interval->time = DT_NOBEGIN;
+ interval->day = PG_INT32_MIN;
+ interval->month = PG_INT32_MIN;
+}
+
+/* Does the given interval indicate infinite interval in the past? */
+static bool
+interval_is_nobegin(Interval *interval)
+{
+ /*
+ * TODO: Possibly it makes sense to just check one of the fields to reduce
+ * the number of instructions here. But it's safer to check all the three
+ * fields.
+ */
+ return interval->time == DT_NOBEGIN &&
+ interval->day == PG_INT32_MIN &&
+ interval->month == PG_INT32_MIN;
+}
+
+/* Is the given interval infinite? */
+/* TODO: probably a material for a macro. */
+static bool
+interval_not_finite(Interval *interval)
+{
+ return interval_is_nobegin(interval) || interval_is_noend(interval);
+}
+
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 579e92e7b3..8242d28945 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -52,6 +52,19 @@ SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
9 years 1 mon -12 days +13:14:00
(1 row)
+SELECT INTERVAL 'infinity' AS "eternity";
+ eternity
+----------
+ infinity
+(1 row)
+
+SELECT INTERVAL '-infinity' AS "beginning of time";
+ beginning of time
+-------------------
+ -infinity
+(1 row)
+
+--TODO: Add tests for operators etc. by looking at the other tests below
CREATE TABLE INTERVAL_TBL (f1 interval);
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -63,6 +76,8 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
ERROR: invalid input syntax for type interval: "badly formatted interval"
@@ -72,6 +87,10 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
ERROR: invalid input syntax for type interval: "@ 30 eons ago"
LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
^
+INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
+ERROR: invalid input syntax for type interval: "+infinity"
+LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
+ ^
-- Test non-error-throwing API
SELECT pg_input_is_valid('1.5 weeks', 'interval');
pg_input_is_valid
@@ -117,7 +136,9 @@ SELECT * FROM INTERVAL_TBL;
6 years
5 mons
5 mons 12:00:00
-(10 rows)
+ infinity
+ -infinity
+(12 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
@@ -132,7 +153,9 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(9 rows)
+ infinity
+ -infinity
+(11 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
@@ -141,7 +164,8 @@ SELECT * FROM INTERVAL_TBL
00:01:00
05:00:00
-00:00:14
-(3 rows)
+ -infinity
+(4 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
@@ -150,7 +174,8 @@ SELECT * FROM INTERVAL_TBL
00:01:00
05:00:00
-00:00:14
-(3 rows)
+ -infinity
+(4 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
@@ -168,7 +193,8 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(5 rows)
+ infinity
+(6 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
@@ -183,7 +209,8 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(9 rows)
+ infinity
+(10 rows)
SELECT r1.*, r2.*
FROM INTERVAL_TBL r1, INTERVAL_TBL r2
@@ -191,27 +218,35 @@ SELECT r1.*, r2.*
ORDER BY r1.f1, r2.f1;
f1 | f1
-----------------+-----------------
+ -00:00:14 | -infinity
+ 00:01:00 | -infinity
00:01:00 | -00:00:14
+ 05:00:00 | -infinity
05:00:00 | -00:00:14
05:00:00 | 00:01:00
+ 1 day 02:03:04 | -infinity
1 day 02:03:04 | -00:00:14
1 day 02:03:04 | 00:01:00
1 day 02:03:04 | 05:00:00
+ 10 days | -infinity
10 days | -00:00:14
10 days | 00:01:00
10 days | 05:00:00
10 days | 1 day 02:03:04
+ 3 mons | -infinity
3 mons | -00:00:14
3 mons | 00:01:00
3 mons | 05:00:00
3 mons | 1 day 02:03:04
3 mons | 10 days
+ 5 mons | -infinity
5 mons | -00:00:14
5 mons | 00:01:00
5 mons | 05:00:00
5 mons | 1 day 02:03:04
5 mons | 10 days
5 mons | 3 mons
+ 5 mons 12:00:00 | -infinity
5 mons 12:00:00 | -00:00:14
5 mons 12:00:00 | 00:01:00
5 mons 12:00:00 | 05:00:00
@@ -219,6 +254,7 @@ SELECT r1.*, r2.*
5 mons 12:00:00 | 10 days
5 mons 12:00:00 | 3 mons
5 mons 12:00:00 | 5 mons
+ 6 years | -infinity
6 years | -00:00:14
6 years | 00:01:00
6 years | 05:00:00
@@ -227,6 +263,7 @@ SELECT r1.*, r2.*
6 years | 3 mons
6 years | 5 mons
6 years | 5 mons 12:00:00
+ 34 years | -infinity
34 years | -00:00:14
34 years | 00:01:00
34 years | 05:00:00
@@ -236,7 +273,18 @@ SELECT r1.*, r2.*
34 years | 5 mons
34 years | 5 mons 12:00:00
34 years | 6 years
-(45 rows)
+ infinity | -infinity
+ infinity | -00:00:14
+ infinity | 00:01:00
+ infinity | 05:00:00
+ infinity | 1 day 02:03:04
+ infinity | 10 days
+ infinity | 3 mons
+ infinity | 5 mons
+ infinity | 5 mons 12:00:00
+ infinity | 6 years
+ infinity | 34 years
+(66 rows)
-- Test intervals that are large enough to overflow 64 bits in comparisons
CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
@@ -386,12 +434,20 @@ SELECT * FROM INTERVAL_TBL;
@ 6 years
@ 5 mons
@ 5 mons 12 hours
-(10 rows)
+ infinity
+ -infinity
+(12 rows)
-- test avg(interval), which is somewhat fragile since people have been
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
select avg(f1) from interval_tbl;
+ avg
+----------
+ infinity
+(1 row)
+
+select avg(f1) from interval_tbl where isfinite(f1);
avg
-------------------------------------------------
@ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
@@ -820,8 +876,8 @@ SELECT interval '1 2:03:04.5678' minute to second(2);
SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
(f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years"
FROM interval_tbl;
- f1 | minutes | years
------------------+-----------------+----------
+ f1 | minutes | years
+-----------------+-----------------+-----------
00:01:00 | 00:01:00 | 00:00:00
05:00:00 | 05:00:00 | 00:00:00
10 days | 10 days | 00:00:00
@@ -832,7 +888,9 @@ SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
6 years | 6 years | 6 years
5 mons | 5 mons | 00:00:00
5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
-(10 rows)
+ infinity | infinity | infinity
+ -infinity | -infinity | -infinity
+(12 rows)
-- test inputting and outputting SQL standard interval literals
SET IntervalStyle TO sql_standard;
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 0517b5b82b..92f60ad005 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -14,6 +14,10 @@ SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
SELECT INTERVAL '1.5 months' AS "One month 15 days";
SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
+SELECT INTERVAL 'infinity' AS "eternity";
+SELECT INTERVAL '-infinity' AS "beginning of time";
+
+--TODO: Add tests for operators etc. by looking at the other tests below
CREATE TABLE INTERVAL_TBL (f1 interval);
@@ -27,10 +31,13 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
-- Test non-error-throwing API
SELECT pg_input_is_valid('1.5 weeks', 'interval');
@@ -141,6 +148,7 @@ SELECT * FROM INTERVAL_TBL;
-- updating pg_aggregate.agginitval
select avg(f1) from interval_tbl;
+select avg(f1) from interval_tbl where isfinite(f1);
-- test long interval input
select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
--
2.25.1
