Be more careful about out-of-range dates and timestamps.

Tighten the semantics of boundary-case timestamptz so that we allow
timestamps >= '4714-11-24 00:00+00 BC' and < 'ENDYEAR-01-01 00:00+00 AD'
exactly, no more and no less, but it is allowed to enter timestamps
within that range using non-GMT timezone offsets (which could make the
nominal date 4714-11-23 BC or ENDYEAR-01-01 AD).  This eliminates
dump/reload failure conditions for timestamps near the endpoints.
To do this, separate checking of the inputs for date2j() from the
final range check, and allow the Julian date code to handle a range
slightly wider than the nominal range of the datatypes.

Also add a bunch of checks to detect out-of-range dates and timestamps
that formerly could be returned by operations such as date-plus-integer.
All C-level functions that return date, timestamp, or timestamptz should
now be proof against returning a value that doesn't pass IS_VALID_DATE()
or IS_VALID_TIMESTAMP().

Vitaly Burovoy, reviewed by Anastasia Lubennikova, and substantially
whacked around by me

4 files changed, 196 insertions, 33 deletions

diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 332db7e9c00..420f383a804 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -160,6 +160,7 @@ date_in(PG_FUNCTION_ARGS)
 			break;
 	}
 
+	/* Prevent overflow in Julian-day routines */
 	if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -167,6 +168,12 @@ date_in(PG_FUNCTION_ARGS)
 
 	date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
 
+	/* Now check for just-out-of-range dates */
+	if (!IS_VALID_DATE(date))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("date out of range: \"%s\"", str)));
+
 	PG_RETURN_DATEADT(date);
 }
 
@@ -209,8 +216,7 @@ date_recv(PG_FUNCTION_ARGS)
 	/* Limit to the same range that date_in() accepts. */
 	if (DATE_NOT_FINITE(result))
 		 /* ok */ ;
-	else if (result < -POSTGRES_EPOCH_JDATE ||
-			 result >= JULIAN_MAX - POSTGRES_EPOCH_JDATE)
+	else if (!IS_VALID_DATE(result))
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 				 errmsg("date out of range")));
@@ -258,6 +264,7 @@ make_date(PG_FUNCTION_ARGS)
 				 errmsg("date field value out of range: %d-%02d-%02d",
 						tm.tm_year, tm.tm_mon, tm.tm_mday)));
 
+	/* Prevent overflow in Julian-day routines */
 	if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -266,6 +273,13 @@ make_date(PG_FUNCTION_ARGS)
 
 	date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
 
+	/* Now check for just-out-of-range dates */
+	if (!IS_VALID_DATE(date))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("date out of range: %d-%02d-%02d",
+						tm.tm_year, tm.tm_mon, tm.tm_mday)));
+
 	PG_RETURN_DATEADT(date);
 }
 
@@ -427,11 +441,21 @@ date_pli(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	int32		days = PG_GETARG_INT32(1);
+	DateADT		result;
 
 	if (DATE_NOT_FINITE(dateVal))
-		days = 0;				/* can't change infinity */
+		PG_RETURN_DATEADT(dateVal);		/* can't change infinity */
+
+	result = dateVal + days;
+
+	/* Check for integer overflow and out-of-allowed-range */
+	if ((days >= 0 ? (result < dateVal) : (result > dateVal)) ||
+		!IS_VALID_DATE(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("date out of range")));
 
-	PG_RETURN_DATEADT(dateVal + days);
+	PG_RETURN_DATEADT(result);
 }
 
 /* Subtract a number of days from a date, giving a new date.
@@ -441,11 +465,21 @@ date_mii(PG_FUNCTION_ARGS)
 {
 	DateADT		dateVal = PG_GETARG_DATEADT(0);
 	int32		days = PG_GETARG_INT32(1);
+	DateADT		result;
 
 	if (DATE_NOT_FINITE(dateVal))
-		days = 0;				/* can't change infinity */
+		PG_RETURN_DATEADT(dateVal);		/* can't change infinity */
+
+	result = dateVal - days;
+
+	/* Check for integer overflow and out-of-allowed-range */
+	if ((days >= 0 ? (result > dateVal) : (result < dateVal)) ||
+		!IS_VALID_DATE(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("date out of range")));
 
-	PG_RETURN_DATEADT(dateVal - days);
+	PG_RETURN_DATEADT(result);
 }
 
 /*
@@ -464,14 +498,18 @@ date2timestamp(DateADT dateVal)
 		TIMESTAMP_NOEND(result);
 	else
 	{
-#ifdef HAVE_INT64_TIMESTAMP
-		/* date is days since 2000, timestamp is microseconds since same... */
-		result = dateVal * USECS_PER_DAY;
-		/* Date's range is wider than timestamp's, so check for overflow */
-		if (result / USECS_PER_DAY != dateVal)
+		/*
+		 * Date's range is wider than timestamp's, so check for boundaries.
+		 * Since dates have the same minimum values as timestamps, only upper
+		 * boundary need be checked for overflow.
+		 */
+		if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
 			ereport(ERROR,
 					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 					 errmsg("date out of range for timestamp")));
+#ifdef HAVE_INT64_TIMESTAMP
+		/* date is days since 2000, timestamp is microseconds since same... */
+		result = dateVal * USECS_PER_DAY;
 #else
 		/* date is days since 2000, timestamp is seconds since same... */
 		result = dateVal * (double) SECS_PER_DAY;
@@ -495,6 +533,16 @@ date2timestamptz(DateADT dateVal)
 		TIMESTAMP_NOEND(result);
 	else
 	{
+		/*
+		 * Date's range is wider than timestamp's, so check for boundaries.
+		 * Since dates have the same minimum values as timestamps, only upper
+		 * boundary need be checked for overflow.
+		 */
+		if (dateVal >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("date out of range for timestamp")));
+
 		j2date(dateVal + POSTGRES_EPOCH_JDATE,
 			   &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
 		tm->tm_hour = 0;
@@ -504,14 +552,18 @@ date2timestamptz(DateADT dateVal)
 
 #ifdef HAVE_INT64_TIMESTAMP
 		result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
-		/* Date's range is wider than timestamp's, so check for overflow */
-		if ((result - tz * USECS_PER_SEC) / USECS_PER_DAY != dateVal)
-			ereport(ERROR,
-					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
-					 errmsg("date out of range for timestamp")));
 #else
 		result = dateVal * (double) SECS_PER_DAY + tz;
 #endif
+
+		/*
+		 * Since it is possible to go beyond allowed timestamptz range because
+		 * of time zone, check for allowed timestamp range after adding tz.
+		 */
+		if (!IS_VALID_TIMESTAMP(result))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("date out of range for timestamp")));
 	}
 
 	return result;
@@ -1053,7 +1105,17 @@ abstime_date(PG_FUNCTION_ARGS)
 
 		default:
 			abstime2tm(abstime, &tz, tm, NULL);
+			/* Prevent overflow in Julian-day routines */
+			if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
+				ereport(ERROR,
+						(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+						 errmsg("abstime out of range for date")));
 			result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
+			/* Now check for just-out-of-range dates */
+			if (!IS_VALID_DATE(result))
+				ereport(ERROR,
+						(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+						 errmsg("abstime out of range for date")));
 			break;
 	}
 
@@ -1678,7 +1740,13 @@ datetime_timestamp(PG_FUNCTION_ARGS)
 
 	result = date2timestamp(date);
 	if (!TIMESTAMP_NOT_FINITE(result))
+	{
 		result += time;
+		if (!IS_VALID_TIMESTAMP(result))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+	}
 
 	PG_RETURN_TIMESTAMP(result);
 }
@@ -2550,11 +2618,29 @@ datetimetz_timestamptz(PG_FUNCTION_ARGS)
 		TIMESTAMP_NOEND(result);
 	else
 	{
+		/*
+		 * Date's range is wider than timestamp's, so check for boundaries.
+		 * Since dates have the same minimum values as timestamps, only upper
+		 * boundary need be checked for overflow.
+		 */
+		if (date >= (TIMESTAMP_END_JULIAN - POSTGRES_EPOCH_JDATE))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("date out of range for timestamp")));
 #ifdef HAVE_INT64_TIMESTAMP
 		result = date * USECS_PER_DAY + time->time + time->zone * USECS_PER_SEC;
 #else
 		result = date * (double) SECS_PER_DAY + time->time + time->zone;
 #endif
+
+		/*
+		 * Since it is possible to go beyond allowed timestamptz range because
+		 * of time zone, check for allowed timestamp range after adding tz.
+		 */
+		if (!IS_VALID_TIMESTAMP(result))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("date out of range for timestamp")));
 	}
 
 	PG_RETURN_TIMESTAMP(result);
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index cdbf72cf699..2ea21b7028a 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -280,14 +280,16 @@ strtoi(const char *nptr, char **endptr, int base)
  *	and calendar date for all non-negative Julian days
  *	(i.e. from Nov 24, -4713 on).
  *
- * These routines will be used by other date/time packages
- * - thomas 97/02/25
- *
  * Rewritten to eliminate overflow problems. This now allows the
  * routines to work correctly for all Julian day counts from
  * 0 to 2147483647	(Nov 24, -4713 to Jun 3, 5874898) assuming
  * a 32-bit integer. Longer types should also work to the limits
  * of their precision.
+ *
+ * Actually, date2j() will work sanely, in the sense of producing
+ * valid negative Julian dates, significantly before Nov 24, -4713.
+ * We rely on it to do so back to Nov 1, -4713; see IS_VALID_JULIAN()
+ * and associated commentary in timestamp.h.
  */
 
 int
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 49567643199..2b5622a9ee0 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -3525,6 +3525,7 @@ to_date(PG_FUNCTION_ARGS)
 
 	do_to_timestamp(date_txt, fmt, &tm, &fsec);
 
+	/* Prevent overflow in Julian-day routines */
 	if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
@@ -3533,6 +3534,13 @@ to_date(PG_FUNCTION_ARGS)
 
 	result = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
 
+	/* Now check for just-out-of-range dates */
+	if (!IS_VALID_DATE(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("date out of range: \"%s\"",
+						text_to_cstring(date_txt))));
+
 	PG_RETURN_DATEADT(result);
 }
 
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index c4f556a3ffa..3f013e3f5bb 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -261,7 +261,8 @@ timestamp_recv(PG_FUNCTION_ARGS)
 	/* rangecheck: see if timestamp_out would like it */
 	if (TIMESTAMP_NOT_FINITE(timestamp))
 		 /* ok */ ;
-	else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+	else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0 ||
+			 !IS_VALID_TIMESTAMP(timestamp))
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 				 errmsg("timestamp out of range")));
@@ -645,6 +646,14 @@ make_timestamp_internal(int year, int month, int day,
 	result = date * SECS_PER_DAY + time;
 #endif
 
+	/* final range check catches just-out-of-range timestamps */
+	if (!IS_VALID_TIMESTAMP(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
+						year, month, day,
+						hour, min, sec)));
+
 	return result;
 }
 
@@ -702,6 +711,7 @@ make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
 	int32		min = PG_GETARG_INT32(4);
 	float8		sec = PG_GETARG_FLOAT8(5);
 	text	   *zone = PG_GETARG_TEXT_PP(6);
+	TimestampTz result;
 	Timestamp	timestamp;
 	struct pg_tm tt;
 	int			tz;
@@ -717,7 +727,14 @@ make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
 
 	tz = parse_sane_timezone(&tt, zone);
 
-	PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(timestamp, -tz));
+	result = dt2local(timestamp, -tz);
+
+	if (!IS_VALID_TIMESTAMP(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
+	PG_RETURN_TIMESTAMPTZ(result);
 }
 
 /* timestamptz_out()
@@ -778,7 +795,8 @@ timestamptz_recv(PG_FUNCTION_ARGS)
 	/* rangecheck: see if timestamptz_out would like it */
 	if (TIMESTAMP_NOT_FINITE(timestamp))
 		 /* ok */ ;
-	else if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+	else if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0 ||
+			 !IS_VALID_TIMESTAMP(timestamp))
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 				 errmsg("timestamp out of range")));
@@ -1925,7 +1943,7 @@ tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result)
 	TimeOffset	date;
 	TimeOffset	time;
 
-	/* Julian day routines are not correct for negative Julian days */
+	/* Prevent overflow in Julian-day routines */
 	if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
 	{
 		*result = 0;			/* keep compiler quiet */
@@ -1957,6 +1975,13 @@ tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result)
 	if (tzp != NULL)
 		*result = dt2local(*result, -(*tzp));
 
+	/* final range check catches just-out-of-range timestamps */
+	if (!IS_VALID_TIMESTAMP(*result))
+	{
+		*result = 0;			/* keep compiler quiet */
+		return -1;
+	}
+
 	return 0;
 }
 
@@ -2982,6 +3007,12 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
 		}
 
 		timestamp += span->time;
+
+		if (!IS_VALID_TIMESTAMP(timestamp))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
 		result = timestamp;
 	}
 
@@ -3086,6 +3117,12 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
 		}
 
 		timestamp += span->time;
+
+		if (!IS_VALID_TIMESTAMP(timestamp))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
 		result = timestamp;
 	}
 
@@ -4397,6 +4434,7 @@ timestamp_part(PG_FUNCTION_ARGS)
 	text	   *units = PG_GETARG_TEXT_PP(0);
 	Timestamp	timestamp = PG_GETARG_TIMESTAMP(1);
 	float8		result;
+	Timestamp	epoch;
 	int			type,
 				val;
 	char	   *lowunits;
@@ -4575,10 +4613,15 @@ timestamp_part(PG_FUNCTION_ARGS)
 		switch (val)
 		{
 			case DTK_EPOCH:
+				epoch = SetEpochTimestamp();
 #ifdef HAVE_INT64_TIMESTAMP
-				result = (timestamp - SetEpochTimestamp()) / 1000000.0;
+				/* try to avoid precision loss in subtraction */
+				if (timestamp < (PG_INT64_MAX + epoch))
+					result = (timestamp - epoch) / 1000000.0;
+				else
+					result = ((float8) timestamp - epoch) / 1000000.0;
 #else
-				result = timestamp - SetEpochTimestamp();
+				result = timestamp - epoch;
 #endif
 				break;
 
@@ -4611,6 +4654,7 @@ timestamptz_part(PG_FUNCTION_ARGS)
 	text	   *units = PG_GETARG_TEXT_PP(0);
 	TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
 	float8		result;
+	Timestamp	epoch;
 	int			tz;
 	int			type,
 				val;
@@ -4792,10 +4836,15 @@ timestamptz_part(PG_FUNCTION_ARGS)
 		switch (val)
 		{
 			case DTK_EPOCH:
+				epoch = SetEpochTimestamp();
 #ifdef HAVE_INT64_TIMESTAMP
-				result = (timestamp - SetEpochTimestamp()) / 1000000.0;
+				/* try to avoid precision loss in subtraction */
+				if (timestamp < (PG_INT64_MAX + epoch))
+					result = (timestamp - epoch) / 1000000.0;
+				else
+					result = ((float8) timestamp - epoch) / 1000000.0;
 #else
-				result = timestamp - SetEpochTimestamp();
+				result = timestamp - epoch;
 #endif
 				break;
 
@@ -5107,9 +5156,8 @@ timestamp_zone(PG_FUNCTION_ARGS)
 			tz = DetermineTimeZoneOffset(&tm, tzp);
 			if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
 				ereport(ERROR,
-						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-						 errmsg("could not convert to time zone \"%s\"",
-								tzname)));
+						(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+						 errmsg("timestamp out of range")));
 		}
 		else
 		{
@@ -5120,6 +5168,11 @@ timestamp_zone(PG_FUNCTION_ARGS)
 		}
 	}
 
+	if (!IS_VALID_TIMESTAMP(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
 	PG_RETURN_TIMESTAMPTZ(result);
 }
 
@@ -5198,6 +5251,11 @@ timestamp_izone(PG_FUNCTION_ARGS)
 
 	result = dt2local(timestamp, tz);
 
+	if (!IS_VALID_TIMESTAMP(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
 	PG_RETURN_TIMESTAMPTZ(result);
 }	/* timestamp_izone() */
 
@@ -5337,9 +5395,8 @@ timestamptz_zone(PG_FUNCTION_ARGS)
 						 errmsg("timestamp out of range")));
 			if (tm2timestamp(&tm, fsec, NULL, &result) != 0)
 				ereport(ERROR,
-						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-						 errmsg("could not convert to time zone \"%s\"",
-								tzname)));
+						(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+						 errmsg("timestamp out of range")));
 		}
 		else
 		{
@@ -5350,6 +5407,11 @@ timestamptz_zone(PG_FUNCTION_ARGS)
 		}
 	}
 
+	if (!IS_VALID_TIMESTAMP(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
 	PG_RETURN_TIMESTAMP(result);
 }
 
@@ -5383,6 +5445,11 @@ timestamptz_izone(PG_FUNCTION_ARGS)
 
 	result = dt2local(timestamp, tz);
 
+	if (!IS_VALID_TIMESTAMP(result))
+		ereport(ERROR,
+				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+				 errmsg("timestamp out of range")));
+
 	PG_RETURN_TIMESTAMP(result);
 }