public inbox for [email protected]
help / color / mirror / Atom feedFrom: Baji Shaik <[email protected]>
To: Masahiko Sawada <[email protected]>
Cc: Christophe Pettus <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
Date: Fri, 12 Jun 2026 17:34:59 -0500
Message-ID: <CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com> (raw)
In-Reply-To: <CAD21AoBpxjp3wjQ4dia0c76Bp8E=Q4UOgBTU4ZOn3Sj6k=-SsA@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>
On Thu, Jun 11, 2026 at 2:20 PM Masahiko Sawada <[email protected]>
wrote:
> I think we should go ahead and add both upper and lower bound checks,
> barring objections.
>
Thanks Masahiko. Here's a patch series that adds both boundary
checks along with the infinity check from my earlier patch:
0001 - Reject timestamps before the Unix epoch (lower bound)
0002 - Reject infinite intervals
0003 - Reject timestamps beyond the 48-bit field limit (upper bound)
Christophe's original v1 covered the pre-epoch case; 0001 is
essentially the same fix with slightly different wording. I have
included it here so the series is self-contained and applies
cleanly on HEAD. Happy to drop it in favor of Christophe's
version if you prefer that.
The infinity check (0002) goes before the epoch conversion so
that uuidv7('infinity'::interval) gets a clear "infinite timestamps"
message rather than falling through to the pre-epoch check
with a confusing detail.
All three use ERRCODE_DATETIME_VALUE_OUT_OF_RANGE with errdetail.
Thanks,
Baji Shaik.
Attachments:
[application/octet-stream] 0001-Fix-uuidv7-with-pre-epoch-interval-silently-producin.patch (3.0K, 3-0001-Fix-uuidv7-with-pre-epoch-interval-silently-producin.patch)
download | inline diff:
From 22c0770e6e41c7914f3df80baaff846b6c9f589a Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 12 Jun 2026 17:30:04 -0500
Subject: [PATCH 1/3] Fix uuidv7() with pre-epoch interval silently producing
misordered UUID
uuidv7(interval) with a negative interval large enough to shift the
timestamp before the Unix epoch (1970-01-01) silently produces a UUID
whose encoded timestamp wraps around via unsigned integer overflow.
Fix by rejecting timestamps before the Unix epoch with a clear error.
This aligns with RFC 9562 Section 6.2.
---
src/backend/utils/adt/uuid.c | 11 +++++++++++
src/test/regress/expected/uuid.out | 4 ++++
src/test/regress/sql/uuid.sql | 3 +++
3 files changed, 18 insertions(+)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 6ee3752ac78..c44858b6391 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -690,6 +690,17 @@ uuidv7_interval(PG_FUNCTION_ARGS)
/* Convert a TimestampTz value back to an UNIX epoch timestamp */
us = ts + (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
+ /*
+ * UUID version 7 does not support timestamps before the Unix epoch.
+ * A negative value here would wrap when cast to uint64, producing a UUID
+ * with a bogus far-future timestamp that breaks sort ordering.
+ */
+ if (us < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range for UUID version 7"),
+ errdetail("UUID version 7 does not support timestamps before the Unix epoch.")));
+
/* 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 9c5dda9e9ab..ea3c1adf412 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -318,6 +318,10 @@ SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
(1 row)
+-- uuidv7(interval) rejects timestamps before the Unix epoch
+SELECT uuidv7('-1000 years'::interval);
+ERROR: timestamp out of range for UUID version 7
+DETAIL: UUID version 7 does not support timestamps before the Unix epoch.
-- casts
SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
bytea
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 8cc2ad40614..15442f5f9e7 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -155,6 +155,9 @@ SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday
SELECT uuid_extract_timestamp(gen_random_uuid()); -- null
SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
+-- uuidv7(interval) rejects timestamps before the Unix epoch
+SELECT uuidv7('-1000 years'::interval);
+
-- casts
SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
--
2.50.1 (Apple Git-155)
[application/octet-stream] 0002-Fix-uuidv7-with-infinite-interval-causing-integer-ov.patch (3.2K, 4-0002-Fix-uuidv7-with-infinite-interval-causing-integer-ov.patch)
download | inline diff:
From fd0656023b900e73475cccad255ffef7d472e00f Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 12 Jun 2026 17:30:24 -0500
Subject: [PATCH 2/3] Fix uuidv7() with infinite interval causing integer
overflow
uuidv7('infinity'::interval) overflows int64 during the epoch
conversion and produces a UUID with an incorrect timestamp.
Fix by adding a TIMESTAMP_NOT_FINITE check after
timestamptz_pl_interval(), which catches both infinity and
-infinity before any arithmetic on the timestamp value.
---
src/backend/utils/adt/uuid.c | 11 +++++++++++
src/test/regress/expected/uuid.out | 7 +++++++
src/test/regress/sql/uuid.sql | 4 ++++
3 files changed, 22 insertions(+)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index c44858b6391..3269bfd922c 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -687,6 +687,17 @@ uuidv7_interval(PG_FUNCTION_ARGS)
TimestampTzGetDatum(ts),
IntervalPGetDatum(shift)));
+ /*
+ * Reject infinite intervals. timestamptz_pl_interval() can produce an
+ * infinite timestamp when the input interval is infinite, and converting
+ * that to a Unix epoch value would overflow.
+ */
+ if (TIMESTAMP_NOT_FINITE(ts))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range for UUID version 7"),
+ errdetail("UUID version 7 does not support infinite timestamps.")));
+
/* Convert a TimestampTz value back to an UNIX epoch timestamp */
us = ts + (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY * USECS_PER_SEC;
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index ea3c1adf412..17693f20639 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -322,6 +322,13 @@ SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
SELECT uuidv7('-1000 years'::interval);
ERROR: timestamp out of range for UUID version 7
DETAIL: UUID version 7 does not support timestamps before the Unix epoch.
+-- uuidv7(interval) rejects infinite intervals
+SELECT uuidv7('infinity'::interval);
+ERROR: timestamp out of range for UUID version 7
+DETAIL: UUID version 7 does not support infinite timestamps.
+SELECT uuidv7('-infinity'::interval);
+ERROR: timestamp out of range for UUID version 7
+DETAIL: UUID version 7 does not support infinite timestamps.
-- casts
SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
bytea
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 15442f5f9e7..e88696ee793 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -158,6 +158,10 @@ SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111'); -- null
-- uuidv7(interval) rejects timestamps before the Unix epoch
SELECT uuidv7('-1000 years'::interval);
+-- uuidv7(interval) rejects infinite intervals
+SELECT uuidv7('infinity'::interval);
+SELECT uuidv7('-infinity'::interval);
+
-- casts
SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
--
2.50.1 (Apple Git-155)
[application/octet-stream] 0003-Fix-uuidv7-with-far-future-interval-silently-overflo.patch (3.1K, 5-0003-Fix-uuidv7-with-far-future-interval-silently-overflo.patch)
download | inline diff:
From 4cf381e8ad3604c00c2e506bc89d2fa46625df63 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Fri, 12 Jun 2026 17:30:47 -0500
Subject: [PATCH 3/3] Fix uuidv7() with far-future interval silently
overflowing 48-bit timestamp
uuidv7(interval) with a large positive interval that pushes the
timestamp beyond approximately year 10889 silently produces a UUID
with a truncated timestamp. The UUID version 7 timestamp field is
48 bits wide; when the millisecond value exceeds 2^48, the upper
bits are silently discarded, and the resulting UUID breaks sort ordering.
Fix by rejecting timestamps that would overflow the 48-bit field.
---
src/backend/utils/adt/uuid.c | 11 +++++++++++
src/test/regress/expected/uuid.out | 4 ++++
src/test/regress/sql/uuid.sql | 3 +++
3 files changed, 18 insertions(+)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 3269bfd922c..4bec3de1ad2 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -712,6 +712,17 @@ uuidv7_interval(PG_FUNCTION_ARGS)
errmsg("timestamp out of range for UUID version 7"),
errdetail("UUID version 7 does not support timestamps before the Unix epoch.")));
+ /*
+ * The UUID version 7 timestamp field is 48 bits wide, storing
+ * milliseconds since the Unix epoch. Reject timestamps that would
+ * overflow this field (dates beyond approximately year 10889).
+ */
+ if (us / US_PER_MS > (int64) 0xFFFFFFFFFFFF)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range for UUID version 7"),
+ errdetail("UUID version 7 does not support timestamps beyond approximately year 10889.")));
+
/* 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 17693f20639..589f0b6ea42 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -329,6 +329,10 @@ DETAIL: UUID version 7 does not support infinite timestamps.
SELECT uuidv7('-infinity'::interval);
ERROR: timestamp out of range for UUID version 7
DETAIL: UUID version 7 does not support infinite timestamps.
+-- uuidv7(interval) rejects timestamps that overflow the 48-bit field
+SELECT uuidv7('8920 years'::interval);
+ERROR: timestamp out of range for UUID version 7
+DETAIL: UUID version 7 does not support timestamps beyond approximately year 10889.
-- casts
SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
bytea
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index e88696ee793..3fcc68110bf 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -162,6 +162,9 @@ SELECT uuidv7('-1000 years'::interval);
SELECT uuidv7('infinity'::interval);
SELECT uuidv7('-infinity'::interval);
+-- uuidv7(interval) rejects timestamps that overflow the 48-bit field
+SELECT uuidv7('8920 years'::interval);
+
-- casts
SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
--
2.50.1 (Apple Git-155)
view thread (5+ 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], [email protected], [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
In-Reply-To: <CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@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