public inbox for [email protected]
help / color / mirror / Atom feedFrom: Baji Shaik <[email protected]>
To: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
Date: Fri, 3 Jul 2026 18:54:16 -0500
Message-ID: <CA+fm-RPAMxA_+h5q=_TShnQ9dooD8OLsebeBsSnsH2GOi+HtTw@mail.gmail.com> (raw)
In-Reply-To: <CAN4CZFOupdhpOwOJBKpG2F4tJ6WgmoNHMXSAwOZXV=6SpVb_yw@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
<CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@mail.gmail.com>
<CAD21AoBpxjp3wjQ4dia0c76Bp8E=Q4UOgBTU4ZOn3Sj6k=-SsA@mail.gmail.com>
<CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com>
<CAN4CZFP9XsTXijqxtK7v9Hgad3jQN-m+wBve0azdi3QkheJv7Q@mail.gmail.com>
<CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@mail.gmail.com>
<[email protected]>
<CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com>
<CAD21AoDAx5ArZ_Fpp1u6VmPS26GsqBB_PEaf9yF4U9U+fd3yBQ@mail.gmail.com>
<CA+fm-RM5sNuGrufK7cMstQu=3BJrXxLzsMYp6GGiKBOBn2cE4g@mail.gmail.com>
<CAN4CZFOupdhpOwOJBKpG2F4tJ6WgmoNHMXSAwOZXV=6SpVb_yw@mail.gmail.com>
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.
Thanks,
Baji Shaik
Attachments:
[application/octet-stream] v5-0002-Reject-out-of-range-timestamps-in-uuidv7-interval.patch (6.0K, ../CA+fm-RPAMxA_+h5q=_TShnQ9dooD8OLsebeBsSnsH2GOi+HtTw@mail.gmail.com/3-v5-0002-Reject-out-of-range-timestamps-in-uuidv7-interval.patch)
download | inline diff:
From af263cbe6632fe1adef0238b9d7b5ea5ef0d6e0d Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 3 Jul 2026 18:47:55 -0500
Subject: [PATCH v5 2/2] Reject out-of-range timestamps in uuidv7(interval)
uuidv7() with a large negative or positive interval silently produced
UUIDs whose timestamp was outside the range representable by UUID
version 7's 48-bit millisecond field.
Fix by pre-computing the valid timestamp window in PostgreSQL-epoch
units (UUIDV7_MIN_TIMESTAMP and UUIDV7_MAX_TIMESTAMP) and validating
the shifted TimestampTz before converting to Unix epoch. This avoids
overflow concerns entirely and consolidates the lower-bound and
upper-bound checks into a single condition.
Also document the valid timestamp range for the shift parameter.
Author: Baji Shaik <[email protected]>
Discussion:
---
doc/src/sgml/func/func-uuid.sgml | 5 +++++
src/backend/utils/adt/uuid.c | 34 ++++++++++++++++++++++++++++--
src/test/regress/expected/uuid.out | 12 +++++++++++
src/test/regress/sql/uuid.sql | 9 ++++++++
4 files changed, 58 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/func/func-uuid.sgml b/doc/src/sgml/func/func-uuid.sgml
index cfd42433a95..89fd5781bcc 100644
--- a/doc/src/sgml/func/func-uuid.sgml
+++ b/doc/src/sgml/func/func-uuid.sgml
@@ -83,6 +83,11 @@
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 ba3c30b8ebc..26b8bfeecf6 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -33,6 +33,25 @@
#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.
+ */
+#define UUIDV7_EPOCH_OFFSET \
+ ((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. UUID v7 uses a 48-bit unsigned millisecond field relative
+ * to the Unix epoch, so the representable window is [1970-01-01, ~10889].
+ *
+ * By pre-computing these bounds in PostgreSQL-epoch units we can validate
+ * the shifted TimestampTz directly, avoiding overflow during conversion.
+ */
+#define UUIDV7_MIN_TIMESTAMP (-UUIDV7_EPOCH_OFFSET)
+#define UUIDV7_MAX_TIMESTAMP \
+ (((INT64CONST(1) << 48) - 1) * US_PER_MS - UUIDV7_EPOCH_OFFSET)
+
/*
* 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
@@ -712,8 +731,19 @@ uuidv7_interval(PG_FUNCTION_ARGS)
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 + UUIDV7_EPOCH_OFFSET;
/* Generate an UUIDv7 */
uuid = generate_uuidv7(us / US_PER_MS, (us % US_PER_MS) * NS_PER_US + ns % NS_PER_US);
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 0935651f1eb..ae4a059736d 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -275,6 +275,18 @@ 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: large future intervals are rejected
+SELECT uuidv7('292230 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.
-- 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 9ee64a5fa9c..57bfca3a4fe 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -147,6 +147,15 @@ SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts;
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: large future intervals are rejected
+SELECT uuidv7('292230 years'::interval);
+
-- extract functions
-- version
--
2.50.1 (Apple Git-155)
[application/octet-stream] v5-0001-Reject-infinite-intervals-in-uuidv7-interval.patch (3.2K, ../CA+fm-RPAMxA_+h5q=_TShnQ9dooD8OLsebeBsSnsH2GOi+HtTw@mail.gmail.com/4-v5-0001-Reject-infinite-intervals-in-uuidv7-interval.patch)
download | inline diff:
From 837580f5d5f90548a5baf9d90d7a0656969bc342 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 3 Jul 2026 18:47:46 -0500
Subject: [PATCH v5 1/2] Reject infinite intervals in uuidv7(interval)
uuidv7('infinity'::interval) caused integer overflow during the epoch
conversion, producing garbage UUIDs. Reject infinite intervals up
front, before any timestamp arithmetic is performed.
Also mention in the documentation that infinite interval values are
not accepted.
Author: Baji Shaik <[email protected]>
Discussion:
---
doc/src/sgml/func/func-uuid.sgml | 1 +
src/backend/utils/adt/uuid.c | 7 +++++++
src/test/regress/expected/uuid.out | 7 +++++++
src/test/regress/sql/uuid.sql | 4 ++++
4 files changed, 19 insertions(+)
diff --git a/doc/src/sgml/func/func-uuid.sgml b/doc/src/sgml/func/func-uuid.sgml
index 2638e2bf855..cfd42433a95 100644
--- a/doc/src/sgml/func/func-uuid.sgml
+++ b/doc/src/sgml/func/func-uuid.sgml
@@ -82,6 +82,7 @@
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.
</para>
<para>
<literal>uuidv7()</literal>
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 2d33ba2640b..ba3c30b8ebc 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -690,6 +690,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
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index cd7cd8b711c..0935651f1eb 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -268,6 +268,13 @@ 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.
-- 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 d0481a15022..9ee64a5fa9c 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -143,6 +143,10 @@ 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);
+
-- extract functions
-- version
--
2.50.1 (Apple Git-155)
view thread (18+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
In-Reply-To: <CA+fm-RPAMxA_+h5q=_TShnQ9dooD8OLsebeBsSnsH2GOi+HtTw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox