PG Bug reporting form <noreply@postgresql.org> writes:
> The SQL script below uses an anchor date (2000-01-01) in a table and tries
> to subtract an interval (in days) from it.
> Considering the timestamp range, the maximum number of days that can be
> successfully subtracted should be 2451545. All higher values are then
> expected to trigger an exception.
> However, when subtracting 2483590, no error is triggered. This is true up to
> 2539738.
> Subtracting 2539739 again shows an error message.
> Is this the intended behavior?
Nope. Looks like we need to check for an out-of-range Julian date
when subtracting intervals from timestamps, more or less as attached.
regards, tom lane
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 866fdd5af8..c38f88dba7 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3120,8 +3120,16 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
- /* Add days by converting to and from Julian */
- julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
+ /*
+ * Add days by converting to and from Julian. We need an overflow
+ * check here since j2date expects a non-negative integer input.
+ */
+ julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
+ if (pg_add_s32_overflow(julian, span->day, &julian) ||
+ julian < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
if (tm2timestamp(tm, fsec, NULL, ×tamp) != 0)
@@ -3256,8 +3264,19 @@ timestamptz_pl_interval_internal(TimestampTz timestamp,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
- /* Add days by converting to and from Julian */
- julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
+ /*
+ * Add days by converting to and from Julian. We need an overflow
+ * check here since j2date expects a non-negative integer input.
+ * In practice though, it will give correct answers for small
+ * negative Julian dates; we should allow -1 to avoid
+ * timezone-dependent failures, as discussed in timestamp.h.
+ */
+ julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
+ if (pg_add_s32_overflow(julian, span->day, &julian) ||
+ julian < -1)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
tz = DetermineTimeZoneOffset(tm, attimezone);
