On Fri, Jul 3, 2026 at 4:54 PM Baji Shaik <[email protected]> wrote:
>
> On Fri, Jul 3, 2026 at 2:54 PM Zsolt Parragi <[email protected]>
> wrote:
>>
>> There's no overflow anymore with the current patch, so that comment is
>> somewhat stale. Maybe it could be simply "large future intervals are
>> rejected"?
>
>
> Thanks Zsolt! Good catch. Updated the test comment in v5 attached.
>
Thank you for updating the patch!
I've reviewed the patch and have some comments:
+#define UUIDV7_EPOCH_OFFSET \
+ ((int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY
* USECS_PER_SEC)
I guess we don't need to name it with the prefix UUIDV7 as the offset
value is actually not limited to UUIDv7. How about renaming it to
PG_UNIX_EPOCH_OFFSET_US?
---
+-- uuidv7: large future intervals are rejected
+SELECT uuidv7('292230 years'::interval);
Since the highest year of a timestamp value is 294276AD, this test
will fail in the year 2047. Given that the upper-bound rejection is
tested by uuidv7('9000 years'::interval), it seems redundant to me.
Instead, how about adding a test verifying that a large but in-range
forward shift is accepted?
+-- uuidv7: a large but in-range forward shift is accepted
+SELECT uuid_extract_timestamp(uuidv7('1000 years'::interval)) > now()
+ '999 years'::interval;
I've merged two patches into one and made some cosmetic changes and
the above suggestions. I'm going to push it, barring objections.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From a891ff3c36a9dfff48d954c6e4fbfb5ddae181f6 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 3 Jul 2026 18:47:46 -0500
Subject: [PATCH v6] Reject infinite and out-of-range interval shifts in
uuidv7().
uuidv7(interval) shifts the current time by the given interval before
encoding it into the 48-bit Unix-millisecond timestamp field of the
generated UUID. Two cases were mishandled:
An infinite interval ('infinity' or '-infinity') produced an infinite
timestamp, which overflowed during the conversion to Unix-epoch
microseconds and yielded a garbage UUID. Reject infinite intervals up
front, before any timestamp arithmetic.
A shift that moved the timestamp outside the range representable by
the 48-bit field was silently accepted. Timestamps before the Unix
epoch wrapped when cast to unsigned, and timestamps beyond
approximately year 10889 overflowed the field; both produced UUIDs
with bogus timestamps that break sort ordering. Reject any shifted
timestamp outside the supported range.
Also document that infinite intervals and out-of-range shifts are
rejected.
Although raising a new error changes behavior in a stable branch, this
is back-patched to 18 (where uuidv7(interval) was introduced) because
the previous behavior can silently corrupt data. Failing loudly is far
safer than silently accepting the wraparound; otherwise users may not
discover that their UUIDv7 values are no longer sortable until years
later, when recovery is painful. It also matches how PostgreSQL
already handles timestamp + interval overflow, which raises an
error. The change only affects applications passing an interval large
enough to push the result outside the representable range.
Backpatch to 18, where uuidv7(interval) was introduced.
Reported-by: Christophe Pettus <[email protected]>
Author: Baji Shaik <[email protected]>
Reviewed-by: Masahiko Sawada <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Reviewed-by: Tristan Partin <[email protected]>
Reviewed-by: Kyotaro Horiguchi <[email protected]>
Discussion: https://www.postgresql.org/message-id/[email protected]
Backpatch-through: 18
---
doc/src/sgml/func/func-uuid.sgml | 6 ++++
src/backend/utils/adt/uuid.c | 45 ++++++++++++++++++++++++++----
src/test/regress/expected/uuid.out | 22 +++++++++++++++
src/test/regress/sql/uuid.sql | 13 +++++++++
4 files changed, 80 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/func/func-uuid.sgml b/doc/src/sgml/func/func-uuid.sgml
index 2638e2bf85..89fd5781bc 100644
--- a/doc/src/sgml/func/func-uuid.sgml
+++ b/doc/src/sgml/func/func-uuid.sgml
@@ -82,6 +82,12 @@
sub-millisecond timestamp + random. The optional
parameter <parameter>shift</parameter> will shift the computed
timestamp by the given <type>interval</type>.
+ Infinite interval values are not accepted.
+ The shifted timestamp must fall within the range supported by
+ UUID version 7's 48-bit millisecond timestamp field: from
+ 1970-01-01 00:00:00 UTC to approximately year 10889.
+ An error is raised if the resulting timestamp is outside this
+ range.
</para>
<para>
<literal>uuidv7()</literal>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 2d33ba2640..a9edceabab 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -33,6 +33,23 @@
#define NS_PER_US INT64CONST(1000)
#define US_PER_MS INT64CONST(1000)
+/*
+ * The offset between the PostgreSQL epoch (2000-01-01) and the Unix epoch
+ * (1970-01-01) in microseconds. Subtract this from a Unix-epoch microseconds
+ * to get a TimestampTz.
+ */
+#define PG_UNIX_EPOCH_OFFSET_US \
+ ((int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC)
+
+/*
+ * Valid timestamp range for UUID version 7, expressed in PostgreSQL-epoch
+ * microseconds. UUIDv7 uses a 48-bit unsigned millisecond field relative
+ * to the Unix epoch, so the representable window is [1970-01-01, ~10889].
+ */
+#define UUIDV7_MIN_TIMESTAMP (-PG_UNIX_EPOCH_OFFSET_US)
+#define UUIDV7_MAX_TIMESTAMP \
+ (((INT64CONST(1) << 48) - 1) * US_PER_MS - PG_UNIX_EPOCH_OFFSET_US)
+
/*
* UUID version 7 uses 12 bits in "rand_a" to store 1/4096 (or 2^12) fractions of
* sub-millisecond. While most Unix-like platforms provide nanosecond-precision
@@ -690,6 +707,13 @@ uuidv7_interval(PG_FUNCTION_ARGS)
int64 ns = get_real_time_ns_ascending();
int64 us;
+ /* Reject infinite intervals before any arithmetic */
+ if (INTERVAL_NOT_FINITE(shift))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range for UUID version 7"),
+ errdetail("UUID version 7 does not support infinite intervals.")));
+
/*
* Shift the current timestamp by the given interval. To calculate time
* shift correctly, we convert the UNIX epoch to TimestampTz and use
@@ -697,16 +721,26 @@ uuidv7_interval(PG_FUNCTION_ARGS)
* precision.
*/
- ts = (TimestampTz) (ns / NS_PER_US) -
- (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ ts = (TimestampTz) (ns / NS_PER_US) - PG_UNIX_EPOCH_OFFSET_US;
/* Compute time shift */
ts = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
TimestampTzGetDatum(ts),
IntervalPGetDatum(shift)));
- /* Convert a TimestampTz value back to an UNIX epoch timestamp */
- us = ts + (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ /*
+ * Reject timestamps outside the range representable by UUID version 7's
+ * 48-bit millisecond field. We compare in PostgreSQL-epoch units so that
+ * the subsequent conversion to Unix-epoch microseconds cannot overflow.
+ */
+ if (ts < UUIDV7_MIN_TIMESTAMP || ts > UUIDV7_MAX_TIMESTAMP)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range for UUID version 7"),
+ errdetail("UUID version 7 supports timestamps from 1970-01-01 to approximately year 10889.")));
+
+ /* Convert the TimestampTz value to a Unix-epoch timestamp in usec */
+ us = ts + PG_UNIX_EPOCH_OFFSET_US;
/* Generate an UUIDv7 */
uuid = generate_uuidv7(us / US_PER_MS, (us % US_PER_MS) * NS_PER_US + ns % NS_PER_US);
@@ -766,8 +800,7 @@ uuid_extract_timestamp(PG_FUNCTION_ARGS)
+ (((uint64) uuid->data[0]) << 40);
/* convert ms to us, then adjust */
- ts = (TimestampTz) (tms * US_PER_MS) -
- (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ ts = (TimestampTz) (tms * US_PER_MS) - PG_UNIX_EPOCH_OFFSET_US;
PG_RETURN_TIMESTAMPTZ(ts);
}
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index cd7cd8b711..36d54d2f33 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -268,6 +268,28 @@ SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts;
---+----+---------
(0 rows)
+-- uuidv7: infinite intervals are rejected
+SELECT uuidv7('infinity'::interval);
+ERROR: interval out of range for UUID version 7
+DETAIL: UUID version 7 does not support infinite intervals.
+SELECT uuidv7('-infinity'::interval);
+ERROR: interval out of range for UUID version 7
+DETAIL: UUID version 7 does not support infinite intervals.
+-- uuidv7: timestamps before Unix epoch are rejected
+SELECT uuidv7('-1000 years'::interval);
+ERROR: timestamp out of range for UUID version 7
+DETAIL: UUID version 7 supports timestamps from 1970-01-01 to approximately year 10889.
+-- uuidv7: timestamps beyond 48-bit ms field (~year 10889) are rejected
+SELECT uuidv7('9000 years'::interval);
+ERROR: timestamp out of range for UUID version 7
+DETAIL: UUID version 7 supports timestamps from 1970-01-01 to approximately year 10889.
+-- uuidv7: a large but in-range forward shift is accepted
+SELECT uuid_extract_timestamp(uuidv7('1000 years'::interval)) > now() + '999 years'::interval;
+ ?column?
+----------
+ t
+(1 row)
+
-- extract functions
-- version
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index d0481a1502..944be956df 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -143,6 +143,19 @@ WITH uuidts AS (
)
SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts;
+-- uuidv7: infinite intervals are rejected
+SELECT uuidv7('infinity'::interval);
+SELECT uuidv7('-infinity'::interval);
+
+-- uuidv7: timestamps before Unix epoch are rejected
+SELECT uuidv7('-1000 years'::interval);
+
+-- uuidv7: timestamps beyond 48-bit ms field (~year 10889) are rejected
+SELECT uuidv7('9000 years'::interval);
+
+-- uuidv7: a large but in-range forward shift is accepted
+SELECT uuid_extract_timestamp(uuidv7('1000 years'::interval)) > now() + '999 years'::interval;
+
-- extract functions
-- version
--
2.54.0
