Hello hackers,
I've been testing various edge-cases of timestamptz and related types
and noticed that despite being a 16-byte wide type, interval overflows
for some timestamptz (8-byte) subtractions (timestamp_mi).
A simple example of this would be:
select timestamptz'294276-12-31 23:59:59 UTC' - timestamptz'1582-10-15
00:00:00 UTC';
Yielding:
interval'-106599615 days -08:01:50.551616'
This makes sense from the implementation point of view, since both
timestamptz and Interval->TimeOffset are int64.
The patch attached simply throws an error when an overflow is
detected. However I'm not sure this is a reasonable approach for a
code path that could be very hot in some workloads. Another
consideration is that regardless of the values of the timestamps, the
absolute value of the difference can be stored in a uint64. However
that observation has little practical value.
That being said I'm willing to work on a fix that makes sense and
making it commit ready (or step aside if someone else wants to take
over) but I'd also understand if this is marked as "not worth fixing".
Regards,
Nick
From ba326276f79cf847fd1127d0edaf7d7f99a18c8e Mon Sep 17 00:00:00 2001
From: Nick Babadzhanian <pgni...@gmail.com>
Date: Wed, 15 Feb 2023 08:42:00 +0100
Subject: [PATCH] Fix silent interval overflow
---
src/backend/utils/adt/timestamp.c | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 47e059a409..88fb4160cb 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -2713,7 +2713,11 @@ timestamp_mi(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("cannot subtract infinite timestamps")));
- result->time = dt1 - dt2;
+ /* Subtract dt1 and dt2 with overflow detection */
+ if (unlikely(pg_sub_s64_overflow(dt1, dt2, &result->time)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERVAL_FIELD_OVERFLOW),
+ errmsg("interval field out of range")));
result->month = 0;
result->day = 0;
--
2.39.1
