public inbox for [email protected]
help / color / mirror / Atom feedFrom: Baji Shaik <[email protected]>
To: Masahiko Sawada <[email protected]>
Cc: Kyotaro Horiguchi <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
Date: Fri, 3 Jul 2026 11:34:51 -0500
Message-ID: <CA+fm-RM5sNuGrufK7cMstQu=3BJrXxLzsMYp6GGiKBOBn2cE4g@mail.gmail.com> (raw)
In-Reply-To: <CAD21AoDAx5ArZ_Fpp1u6VmPS26GsqBB_PEaf9yF4U9U+fd3yBQ@mail.gmail.com>
References: <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>
On Mon, Jun 29, 2026 at 6:18 PM Masahiko Sawada <[email protected]>
wrote:
> It does the range validation on the Unix-epoch value, after converting
> back from the shifted PostgreSQL-epoch timestamp. Can we compare
> pre-compute the Unix-epoch timestamp resentable window in
> PostgreSQL-epoch units against the shifted PostgreSQL-epoch timestamp
> before converting it back to the Unix epoch? That way, we don't need
> to worry about the overflow.
>
> Let's merge these two if statements as they use the same error message.
>
Thanks for the review. Attached v4 addressing your feedback:
- 0002 now pre-computes the valid timestamp range in PostgreSQL-epoch
units (UUIDV7_MIN_TIMESTAMP / UUIDV7_MAX_TIMESTAMP) and validates
the shifted TimestampTz directly, before converting to Unix epoch.
This eliminates the overflow concern entirely.
- The pre-epoch and upper-bound checks are merged into a single if
statement.
0001 (infinite intervals) is unchanged from v3.
Thanks,
Baji Shaik
Attachments:
[application/octet-stream] v4-0002-Reject-out-of-range-timestamps-in-uuidv7-interval.patch (6.0K, ../CA+fm-RM5sNuGrufK7cMstQu=3BJrXxLzsMYp6GGiKBOBn2cE4g@mail.gmail.com/3-v4-0002-Reject-out-of-range-timestamps-in-uuidv7-interval.patch)
download | inline diff:
From fc874e6f69c9bb05ffc836ce64bcfbd208662afd Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 3 Jul 2026 10:47:14 -0500
Subject: [PATCH v4 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..286507101d8 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 that overflow epoch conversion 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..525ecb1a946 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 that overflow epoch conversion are rejected
+SELECT uuidv7('292230 years'::interval);
+
-- extract functions
-- version
--
2.50.1 (Apple Git-155)
[application/octet-stream] v4-0001-Reject-infinite-intervals-in-uuidv7-interval.patch (3.2K, ../CA+fm-RM5sNuGrufK7cMstQu=3BJrXxLzsMYp6GGiKBOBn2cE4g@mail.gmail.com/4-v4-0001-Reject-infinite-intervals-in-uuidv7-interval.patch)
download | inline diff:
From 49aa75da34e7549f900c7f1f641e6178cbcc8d43 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 3 Jul 2026 10:44:47 -0500
Subject: [PATCH v4 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) latest in thread
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], [email protected], [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
In-Reply-To: <CA+fm-RM5sNuGrufK7cMstQu=3BJrXxLzsMYp6GGiKBOBn2cE4g@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