public inbox for [email protected]
help / color / mirror / Atom feedRe: uuidv7 improperly accepts dates before 1970-01-01
18+ messages / 6 participants
[nested] [flat]
* Re: uuidv7 improperly accepts dates before 1970-01-01
@ 2026-04-27 22:51 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Christophe Pettus @ 2026-04-27 22:51 UTC (permalink / raw)
To: Andrey Borodin <[email protected]>; +Cc: [email protected]
Hi, Andrey,
Thanks for the response! I'm moving it to -hackers since it's not really a bug related conversation at this point. (resending with the right list this time!)
> On Apr 25, 2026, at 05:26, Andrey Borodin <[email protected]> wrote:
> We consulted with RFC authors
> about this feature, and they confirmed that shifting time is compliant with RFC wording.
Time shifting doesn't automatically imply allowing a pre-epoch input time to construct a UUIDv7, though, just that you can construct a UUIDv7 with something other than wall-clock time.
> We wrote the specific test that ensures vast space for shift, but not unlimited.
That's another problem: the API gives the impression of a much larger space than actually exists.
# select uuidv7('100000 years'::interval); # ~11.2 x total time range in a UUID v7.
uuidv7
--------------------------------------
37b45c74-469d-7e1b-9397-1a971a99ab2b
(1 row)
At a minimum, it should reject a shift that creates a time later than a UUID v7 can represent.
> Time shifting would become a footgun if we throw an exception when overflown.
I don't understand why. If the concern is that someone will pick a value that's close to the maximum, and get a surprising exception when the time overflows that, the right answer is to caution them not to do that rather than permit the wraparound.
And is anyone actually doing this? Using a very large interval with a large enough number of shards that wraparound is a real possibility? (In that case, I'd argue they should construct the 48 bit field directly rather than kind of dancing around it by using a time shift.)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
@ 2026-05-28 00:02 ` Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-05-28 00:02 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: Andrey Borodin <[email protected]>; [email protected]
Hi,
On Mon, Apr 27, 2026 at 3:51 PM Christophe Pettus <[email protected]> wrote:
>
> > We wrote the specific test that ensures vast space for shift, but not unlimited.
>
> That's another problem: the API gives the impression of a much larger space than actually exists.
>
> # select uuidv7('100000 years'::interval); # ~11.2 x total time range in a UUID v7.
> uuidv7
> --------------------------------------
> 37b45c74-469d-7e1b-9397-1a971a99ab2b
> (1 row)
>
Fair point.
> At a minimum, it should reject a shift that creates a time later than a UUID v7 can represent.
I think that if we add a lower-bound check as the proposed patch does
an upper-bound check should also be added.
>
> > Time shifting would become a footgun if we throw an exception when overflown.
>
> I don't understand why. If the concern is that someone will pick a value that's close to the maximum, and get a surprising exception when the time overflows that, the right answer is to caution them not to do that rather than permit the wraparound.
I guess that monotonicity could easily be violated depending on how
users shift the wall-clock. Taking Andrey's example, if they use
something like uuidv7('-10 years' * shard_id), the monotonicity would
be broken with just 6 shards.
I guess it would be safer to raise an error in such cases rather than
silently allowing wraparound. Otherwise, users might only realize that
their UUIDv7 values are no longer sortable years down the road, which
would be disastrous. Moreover, raising an error would be consistent
with how PostgreSQL natively handles timestamp + interval overflows.
That said, while I am leaning toward introducing boundary checks, we
should carefully consider this change since it could potentially break
existing applications that rely on the current behavior of
uuidv7(interval).
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
@ 2026-05-28 01:00 ` Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Baji Shaik @ 2026-05-28 01:00 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Andrey Borodin <[email protected]>; [email protected]
Hello Masahiko,
On Wed, May 27, 2026 at 7:02 PM Masahiko Sawada <[email protected]>
wrote:
> I guess it would be safer to raise an error in such cases rather than
> silently allowing wraparound. Otherwise, users might only realize that
> their UUIDv7 values are no longer sortable years down the road, which
> would be disastrous. Moreover, raising an error would be consistent
> with how PostgreSQL natively handles timestamp + interval overflows.
>
+1. I ran into the same issue while testing, specifically,
uuidv7('infinity'::interval) overflows int64 during the epoch
conversion and produces a UUID with an incorrect timestamp.
There's no valid use case for infinity as a shift offset.
I have a small patch that adds a TIMESTAMP_NOT_FINITE check after
timestamptz_pl_interval(), which catches both infinity and
-infinity. Happy to extend it to also cover the 48-bit upper/lower
bound checks if we agree on the direction.
Thanks,
Baji Shaik
Attachments:
[application/octet-stream] 0001-Fix-uuidv7-with-infinite-interval-causing-integer-ov.patch (2.9K, ../../CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@mail.gmail.com/3-0001-Fix-uuidv7-with-infinite-interval-causing-integer-ov.patch)
download | inline diff:
From dc28a08b2fe17990051454f4db935a0ef4d023d3 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Wed, 27 May 2026 19:43:15 -0500
Subject: [PATCH] Fix uuidv7() with infinite interval causing integer overflow
uuidv7('infinity'::interval) and uuidv7('-infinity'::interval) cause
integer overflow when converting the infinite TimestampTz back to a
Unix epoch microsecond value. timestamptz_pl_interval() returns the
special infinity/negative-infinity TimestampTz values, and the
subsequent addition of the epoch offset overflows int64, producing a
garbage timestamp.
Fix by checking TIMESTAMP_NOT_FINITE(ts) immediately after the
interval arithmetic and rejecting with a clear error.
---
src/backend/utils/adt/uuid.c | 7 +++++++
src/test/regress/expected/uuid.out | 5 +++++
src/test/regress/sql/uuid.sql | 4 ++++
3 files changed, 16 insertions(+)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 6ee3752ac78..2bba07bb09c 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -687,6 +687,13 @@ uuidv7_interval(PG_FUNCTION_ARGS)
TimestampTzGetDatum(ts),
IntervalPGetDatum(shift)));
+
+ /* Reject infinite timestamps */
+ if (TIMESTAMP_NOT_FINITE(ts))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range for UUID version 7")));
+
/* 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 9c5dda9e9ab..4388739f462 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -280,6 +280,11 @@ SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
----------------------
(1 row)
+-- uuidv7(interval) rejects infinite intervals
+SELECT uuidv7('infinity'::interval);
+ERROR: timestamp out of range for UUID version 7
+SELECT uuidv7('-infinity'::interval);
+ERROR: timestamp out of range for UUID version 7
SELECT uuid_extract_version(uuidv4()); -- 4
uuid_extract_version
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 8cc2ad40614..d9f0401880b 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -146,6 +146,10 @@ SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts;
SELECT uuid_extract_version('11111111-1111-5111-8111-111111111111'); -- 5
SELECT uuid_extract_version(gen_random_uuid()); -- 4
SELECT uuid_extract_version('11111111-1111-1111-1111-111111111111'); -- null
+
+-- uuidv7(interval) rejects infinite intervals
+SELECT uuidv7('infinity'::interval);
+SELECT uuidv7('-infinity'::interval);
SELECT uuid_extract_version(uuidv4()); -- 4
SELECT uuid_extract_version(uuidv7()); -- 7
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-11 19:20 ` Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-06-11 19:20 UTC (permalink / raw)
To: Baji Shaik <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Andrey Borodin <[email protected]>; [email protected]
On Wed, May 27, 2026 at 6:01 PM Baji Shaik <[email protected]> wrote:
>
> Hello Masahiko,
>
> On Wed, May 27, 2026 at 7:02 PM Masahiko Sawada <[email protected]> wrote:
>>
>> I guess it would be safer to raise an error in such cases rather than
>> silently allowing wraparound. Otherwise, users might only realize that
>> their UUIDv7 values are no longer sortable years down the road, which
>> would be disastrous. Moreover, raising an error would be consistent
>> with how PostgreSQL natively handles timestamp + interval overflows.
>
>
> +1. I ran into the same issue while testing, specifically,
> uuidv7('infinity'::interval) overflows int64 during the epoch
> conversion and produces a UUID with an incorrect timestamp.
> There's no valid use case for infinity as a shift offset.
Yeah, I think we should reject such cases at least.
> I have a small patch that adds a TIMESTAMP_NOT_FINITE check after
> timestamptz_pl_interval(), which catches both infinity and
> -infinity. Happy to extend it to also cover the 48-bit upper/lower
> bound checks if we agree on the direction.
I think we should go ahead and add both upper and lower bound checks,
barring objections.
The current behavior silently produces UUIDs with nonsensical
timestamps when the interval causes the result to go before the Unix
epoch or beyond what 48 bits can represent. Regarding backward
compatibility: this change would affect applications using
uuidv7(interval) with values that cause the resulting timestamp to
fall outside the representable range. But I guess that in practice,
any such application is likely already getting incorrect results due
to wraparound, so the risk of breaking working use cases seems very
low.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
@ 2026-06-12 22:34 ` Baji Shaik <[email protected]>
2026-06-24 15:25 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 17:46 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-24 17:58 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Tristan Partin <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
0 siblings, 4 replies; 18+ messages in thread
From: Baji Shaik @ 2026-06-12 22:34 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Andrey Borodin <[email protected]>; [email protected]
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, ../../CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com/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, ../../CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com/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, ../../CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com/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)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-24 15:25 ` Baji Shaik <[email protected]>
3 siblings, 0 replies; 18+ messages in thread
From: Baji Shaik @ 2026-06-24 15:25 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Andrey Borodin <[email protected]>; [email protected]
On Fri, Jun 12, 2026 at 5:34 PM Baji Shaik <[email protected]> wrote:
> 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)
>
Added to the commitfest: https://commitfest.postgresql.org/patch/6935/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-24 17:46 ` Masahiko Sawada <[email protected]>
2026-06-24 20:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
3 siblings, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-06-24 17:46 UTC (permalink / raw)
To: Baji Shaik <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Andrey Borodin <[email protected]>; [email protected]
On Fri, Jun 12, 2026 at 3:35 PM Baji Shaik <[email protected]> wrote:
>
> 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.
Thank you for creating the patches!
Here are some review comments:
+ /*
+ * 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.")));
+
I think we can do this check earlier, like before shifting the
timestamp, and we can mention in the doc that we don't accept
'infinity' and '-infinity' values.
---
+ /*
+ * 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.")));
Please use INT64CONST() instead.
---
I think we need to mention in the doc that timestamp shifting beyond
the range UUIDv7 can support is not accepted.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 17:46 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
@ 2026-06-24 20:02 ` Christophe Pettus <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Christophe Pettus @ 2026-06-24 20:02 UTC (permalink / raw)
To: Baji Shaik <[email protected]>; +Cc: Andrey Borodin <[email protected]>; [email protected]; Masahiko Sawada <[email protected]>
> On Jun 24, 2026, at 10:46, Masahiko Sawada <[email protected]> wrote:
> I think we need to mention in the doc that timestamp shifting beyond
> the range UUIDv7 can support is not accepted.
I believe the doc patch in my original patch included that admonition. Please feel free to grab it if you'd like.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-24 17:58 ` Tristan Partin <[email protected]>
3 siblings, 0 replies; 18+ messages in thread
From: Tristan Partin @ 2026-06-24 17:58 UTC (permalink / raw)
To: Baji Shaik <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Andrey Borodin <[email protected]>; [email protected]; Masahiko Sawada <[email protected]>
On Fri Jun 12, 2026 at 10:35 PM UTC, Baji Shaik wrote:
> 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.
> +-- 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.
You might want to steal the test in my patch. You never know if Postgres
will be around in 3070, and then this test will start to fail. Slightly
joking... lol
> +-- 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.
I think it might better to derive the interval similar to what I am
suggesting in my first comment.
Neither comment should block merging, and feel free to completely ignore
them. Just personal preference on my part.
Good work. Great minds think alike!
--
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-24 20:31 ` Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
3 siblings, 1 reply; 18+ messages in thread
From: Zsolt Parragi @ 2026-06-24 20:31 UTC (permalink / raw)
To: [email protected]
Hello!
`us` can overflow and while it still results in an error, it is in the
wrong direction:
SELECT uuidv7('292230 years'::interval);
ERROR: timestamp out of range for UUID version 7
DETAIL: UUID version 7 does not support timestamps before the Unix epoch.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
@ 2026-06-24 23:22 ` Baji Shaik <[email protected]>
2026-06-25 02:19 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Baji Shaik @ 2026-06-24 23:22 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: [email protected]
Thank you Masahiko, Tristan, Christophe, and Zsolt for the reviews and
feedback. Addressing the feedback in a single email.
Attached v2 addressing the feedback:
- Moved infinity check before timestamp arithmetic [Masahiko]
- Used INT64CONST() for the 48-bit constant [Masahiko]
- Added documentation for the valid timestamp range [Masahiko, Christophe]
- Added a test for '292230 years' to cover the overflow path
caught by pg_add_s64_overflow() [Zsolt Parragi]
0001 - Reject infinite intervals
0002 - Reject pre-epoch timestamps (with overflow-safe epoch conversion)
0003 - Reject timestamps beyond the 48-bit limit
Let me know if I have missed anything.
Thanks,
Baji Shaik.
Attachments:
[application/octet-stream] v2-0001-Reject-infinite-intervals-in-uuidv7-interval.patch (3.3K, ../../CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@mail.gmail.com/3-v2-0001-Reject-infinite-intervals-in-uuidv7-interval.patch)
download | inline diff:
From 1be5d674037a5c90b972e06f99d4a4b5a2c3c485 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Wed, 24 Jun 2026 18:04:27 -0500
Subject: [PATCH v2 1/3] 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: https://postgr.es/m/18F007D6-1A33-48C8-BA51-E7A858DE0C89%40thebuild.com
---
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 6ee3752ac78..1197cb6c027 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -672,6 +672,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 9c5dda9e9ab..28706d77abc 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -261,6 +261,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 8cc2ad40614..01bbc8558fc 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -140,6 +140,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)
[application/octet-stream] v2-0003-Reject-timestamps-beyond-48-bit-limit-in-uuidv7-i.patch (3.1K, ../../CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@mail.gmail.com/4-v2-0003-Reject-timestamps-beyond-48-bit-limit-in-uuidv7-i.patch)
download | inline diff:
From 620b2a4398e2820b8b750d99f41d75b45066e2ff Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Wed, 24 Jun 2026 18:08:44 -0500
Subject: [PATCH v2 3/3] Reject timestamps beyond 48-bit limit in
uuidv7(interval)
uuidv7() with a large positive interval silently produced UUIDs whose
timestamp overflowed the 48-bit field, wrapping around and producing
incorrect time-ordering. UUID version 7's timestamp field can
represent dates up to approximately 10889-08-02 05:31:50.655 UTC.
Reject shifted timestamps that exceed this maximum. Use INT64CONST() for the 48-bit constant.
Author: Baji Shaik <[email protected]>
Discussion: https://postgr.es/m/18F007D6-1A33-48C8-BA51-E7A858DE0C89%40thebuild.com
---
src/backend/utils/adt/uuid.c | 7 +++++++
src/test/regress/expected/uuid.out | 4 ++++
src/test/regress/sql/uuid.sql | 3 +++
3 files changed, 14 insertions(+)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index ca5dbca8ac3..5fa7cd72761 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -715,6 +715,13 @@ 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 (1970-01-01 00:00:00 UTC).")));
+ /* Reject timestamps beyond the 48-bit millisecond field maximum */
+ if (us / US_PER_MS > (INT64CONST(1) << 48) - 1)
+ 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 72730825cda..1fe95454dcf 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -276,6 +276,10 @@ DETAIL: UUID version 7 does not support timestamps before the Unix epoch (1970-
SELECT uuidv7('292230 years'::interval);
ERROR: timestamp out of range for UUID version 7
DETAIL: The shifted timestamp overflows the representable range.
+-- 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 does not support timestamps beyond 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 eb1a12949c5..2bd2fc51620 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -150,6 +150,9 @@ SELECT uuidv7('-1000 years'::interval);
-- uuidv7: large future intervals that overflow epoch conversion are rejected
SELECT uuidv7('292230 years'::interval);
+-- uuidv7: timestamps beyond 48-bit ms field (~year 10889) are rejected
+SELECT uuidv7('9000 years'::interval);
+
-- extract functions
-- version
--
2.50.1 (Apple Git-155)
[application/octet-stream] v2-0002-Reject-pre-epoch-timestamps-in-uuidv7-interval.patch (5.1K, ../../CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@mail.gmail.com/5-v2-0002-Reject-pre-epoch-timestamps-in-uuidv7-interval.patch)
download | inline diff:
From c6c5bf80a4988af23caaf4e8f620cb65c119b3b5 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Wed, 24 Jun 2026 18:07:03 -0500
Subject: [PATCH v2 2/3] Reject pre-epoch timestamps in uuidv7(interval)
uuidv7() with a large negative interval silently produced UUIDs whose
timestamp wrapped around to the far future, since UUID version 7 uses
an unsigned 48-bit millisecond field that cannot represent dates before
the Unix epoch.
Fix by using pg_add_s64_overflow() for the epoch conversion (so that
very large intervals that overflow int64 produce a clear error instead
of wrapping to the wrong sign), then rejecting negative results.
Also document the valid timestamp range for the shift parameter.
Author: Baji Shaik <[email protected]>
Discussion: https://postgr.es/m/18F007D6-1A33-48C8-BA51-E7A858DE0C89%40thebuild.com
---
doc/src/sgml/func/func-uuid.sgml | 6 ++++++
src/backend/utils/adt/uuid.c | 22 ++++++++++++++++++++--
src/test/regress/expected/uuid.out | 8 ++++++++
src/test/regress/sql/uuid.sql | 6 ++++++
4 files changed, 40 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/func/func-uuid.sgml b/doc/src/sgml/func/func-uuid.sgml
index cfd42433a95..41fc5739c31 100644
--- a/doc/src/sgml/func/func-uuid.sgml
+++ b/doc/src/sgml/func/func-uuid.sgml
@@ -83,6 +83,12 @@
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 between the Unix epoch
+ (1970-01-01 00:00:00 UTC) and approximately
+ 10889-08-02 05:31:50.655 UTC, which is the range representable
+ by UUID version 7's 48-bit millisecond timestamp field.
+ 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 1197cb6c027..ca5dbca8ac3 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -17,6 +17,7 @@
#include <time.h> /* for clock_gettime() */
#include "common/hashfn.h"
+#include "common/int.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
#include "port/pg_bswap.h"
@@ -694,8 +695,25 @@ 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;
+ /*
+ * Convert a TimestampTz value back to a UNIX epoch timestamp. Use
+ * overflow-safe addition since large intervals can exceed int64 range.
+ */
+ if (pg_add_s64_overflow(ts,
+ (int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
+ SECS_PER_DAY * USECS_PER_SEC,
+ &us))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range for UUID version 7"),
+ errdetail("The shifted timestamp overflows the representable range.")));
+
+ /* UUID v7 uses an unsigned 48-bit millisecond field; reject pre-epoch */
+ 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 (1970-01-01 00:00:00 UTC).")));
/* 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 28706d77abc..72730825cda 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -268,6 +268,14 @@ 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 does not support timestamps before the Unix epoch (1970-01-01 00:00:00 UTC).
+-- 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: The shifted timestamp overflows the representable range.
-- 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 01bbc8558fc..eb1a12949c5 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -144,6 +144,12 @@ 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: large future intervals that overflow epoch conversion are rejected
+SELECT uuidv7('292230 years'::interval);
+
-- extract functions
-- version
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-25 02:19 ` Kyotaro Horiguchi <[email protected]>
2026-06-25 13:47 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Kyotaro Horiguchi @ 2026-06-25 02:19 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; [email protected]
Hello,
At Wed, 24 Jun 2026 18:22:20 -0500, Baji Shaik <[email protected]> wrote in
> Let me know if I have missed anything.
The checks themselves seem reasonable to me. However, I do have one
comment about the error reporting.
The checks themselves seem reasonable to me. However, I'm not sure the
various cases need separate error messages.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-25 02:19 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
@ 2026-06-25 13:47 ` Baji Shaik <[email protected]>
2026-06-26 04:57 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
2026-06-29 23:18 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Baji Shaik @ 2026-06-25 13:47 UTC (permalink / raw)
To: Kyotaro Horiguchi <[email protected]>; +Cc: [email protected]; [email protected]
On Wed, Jun 24, 2026 at 9:19 PM Kyotaro Horiguchi <[email protected]>
wrote:
> From a user's perspective, it seems sufficient to know that the
> shifted timestamp falls outside the range supported by UUID v7. As a
> translator, I'm not particularly enthusiastic about adding more
> message variants when the distinction is not particularly useful to
> users.
>
Thanks for the feedback, Kyotaro. Good point. Attached v3 with all boundary
checks
using a single shared errdetail:
"UUID version 7 supports timestamps from 1970-01-01 to approximately year
10889."
0001 - Reject infinite intervals
0002 - Reject pre-epoch timestamps (with overflow-safe epoch conversion)
0003 - Reject timestamps beyond the 48-bit limit
I prefer keeping them as 3 patches since each addresses a distinct
failure mode and is easier to review/bisect independently. That said,
since 0002 and 0003 now share the same errdetail and are logically
the same validation (timestamp outside valid range), I'm happy to
merge them into one patch for v4 if preferred.
Thanks,
Baji Shaik.
Attachments:
[application/octet-stream] v3-0002-Reject-pre-epoch-timestamps-in-uuidv7-interval.patch (5.0K, ../../CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com/3-v3-0002-Reject-pre-epoch-timestamps-in-uuidv7-interval.patch)
download | inline diff:
From 692477b18b5e1130f15b931a59e5432e6352669b Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Thu, 25 Jun 2026 08:39:08 -0500
Subject: [PATCH v3 2/3] Reject pre-epoch timestamps in uuidv7(interval)
uuidv7() with a large negative interval silently produced UUIDs whose
timestamp wrapped around, since UUID version 7 uses an unsigned 48-bit
millisecond field that cannot represent dates before the Unix epoch.
Fix by using pg_add_s64_overflow() for the epoch conversion (so that
very large intervals that overflow int64 produce a clear error instead
of wrapping to the wrong sign), then rejecting negative results.
Also document the valid timestamp range for the shift parameter.
Author: Baji Shaik <[email protected]>
Discussion: https://postgr.es/m/18F007D6-1A33-48C8-BA51-E7A858DE0C89%40thebuild.com
---
doc/src/sgml/func/func-uuid.sgml | 5 +++++
src/backend/utils/adt/uuid.c | 22 ++++++++++++++++++++--
src/test/regress/expected/uuid.out | 8 ++++++++
src/test/regress/sql/uuid.sql | 6 ++++++
4 files changed, 39 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 1197cb6c027..35f727a13ec 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -17,6 +17,7 @@
#include <time.h> /* for clock_gettime() */
#include "common/hashfn.h"
+#include "common/int.h"
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
#include "port/pg_bswap.h"
@@ -694,8 +695,25 @@ 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;
+ /*
+ * Convert a TimestampTz value back to a UNIX epoch timestamp. Use
+ * overflow-safe addition since large intervals can exceed int64 range.
+ */
+ if (pg_add_s64_overflow(ts,
+ (int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
+ SECS_PER_DAY * USECS_PER_SEC,
+ &us))
+ 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.")));
+
+ /* UUID v7 uses an unsigned 48-bit millisecond field; reject pre-epoch */
+ 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 supports timestamps from 1970-01-01 to 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 28706d77abc..32b44fa578a 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -268,6 +268,14 @@ 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: 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 01bbc8558fc..eb1a12949c5 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -144,6 +144,12 @@ 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: 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] v3-0001-Reject-infinite-intervals-in-uuidv7-interval.patch (3.3K, ../../CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com/4-v3-0001-Reject-infinite-intervals-in-uuidv7-interval.patch)
download | inline diff:
From 71c2b1176f2211f864562a56480ade885fa0227e Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Thu, 25 Jun 2026 08:37:15 -0500
Subject: [PATCH v3 1/3] 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: https://postgr.es/m/18F007D6-1A33-48C8-BA51-E7A858DE0C89%40thebuild.com
---
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 6ee3752ac78..1197cb6c027 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -672,6 +672,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 9c5dda9e9ab..28706d77abc 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -261,6 +261,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 8cc2ad40614..01bbc8558fc 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -140,6 +140,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)
[application/octet-stream] v3-0003-Reject-timestamps-beyond-48-bit-limit-in-uuidv7-i.patch (3.0K, ../../CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com/5-v3-0003-Reject-timestamps-beyond-48-bit-limit-in-uuidv7-i.patch)
download | inline diff:
From 44b966e94a5909088c5cb9052c5be3d854a4bd63 Mon Sep 17 00:00:00 2001
From: Baji Shaik <[email protected]>
Date: Thu, 25 Jun 2026 08:40:39 -0500
Subject: [PATCH v3 3/3] Reject timestamps beyond 48-bit limit in
uuidv7(interval)
uuidv7() with a large positive interval silently produced UUIDs whose
timestamp overflowed the 48-bit field, wrapping around and producing
incorrect time-ordering. UUID version 7's timestamp field can
represent dates up to approximately year 10889.
Reject shifted timestamps that exceed this maximum.
Author: Baji Shaik <[email protected]>
Discussion: https://postgr.es/m/18F007D6-1A33-48C8-BA51-E7A858DE0C89%40thebuild.com
---
src/backend/utils/adt/uuid.c | 7 +++++++
src/test/regress/expected/uuid.out | 4 ++++
src/test/regress/sql/uuid.sql | 3 +++
3 files changed, 14 insertions(+)
diff --git a/src/backend/utils/adt/uuid.c b/src/backend/utils/adt/uuid.c
index 35f727a13ec..c083cc987c5 100644
--- a/src/backend/utils/adt/uuid.c
+++ b/src/backend/utils/adt/uuid.c
@@ -715,6 +715,13 @@ uuidv7_interval(PG_FUNCTION_ARGS)
errmsg("timestamp out of range for UUID version 7"),
errdetail("UUID version 7 supports timestamps from 1970-01-01 to approximately year 10889.")));
+ /* Reject timestamps beyond the 48-bit millisecond field maximum */
+ if (us / US_PER_MS > (INT64CONST(1) << 48) - 1)
+ 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.")));
+
/* 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 32b44fa578a..0930a3c73ed 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -276,6 +276,10 @@ DETAIL: UUID version 7 supports timestamps from 1970-01-01 to approximately yea
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.
+-- 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.
-- 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 eb1a12949c5..2bd2fc51620 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -150,6 +150,9 @@ SELECT uuidv7('-1000 years'::interval);
-- uuidv7: large future intervals that overflow epoch conversion are rejected
SELECT uuidv7('292230 years'::interval);
+-- uuidv7: timestamps beyond 48-bit ms field (~year 10889) are rejected
+SELECT uuidv7('9000 years'::interval);
+
-- extract functions
-- version
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-25 02:19 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
2026-06-25 13:47 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-26 04:57 ` Kyotaro Horiguchi <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Kyotaro Horiguchi @ 2026-06-26 04:57 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; [email protected]
Thank you for the new version.
At Thu, 25 Jun 2026 08:47:27 -0500, Baji Shaik <[email protected]> wrote in
> I prefer keeping them as 3 patches since each addresses a distinct
> failure mode and is easier to review/bisect independently. That said,
> since 0002 and 0003 now share the same errdetail and are logically
> the same validation (timestamp outside valid range), I'm happy to
> merge them into one patch for v4 if preferred.
Personally, I still feel that providing the UUIDv7 timestamp domain in
an errdetail is a bit excessive. Even for infinity, I tend to think of
the issue as simply being outside the representable range, so I'm not
sure that a separate message is necessary.
That said, this feels like a matter of taste, so it would probably be
useful to hear other opinions.
Regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-25 02:19 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
2026-06-25 13:47 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-06-29 23:18 ` Masahiko Sawada <[email protected]>
2026-07-03 16:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-06-29 23:18 UTC (permalink / raw)
To: Baji Shaik <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected]; [email protected]
On Thu, Jun 25, 2026 at 6:47 AM Baji Shaik <[email protected]> wrote:
>
> On Wed, Jun 24, 2026 at 9:19 PM Kyotaro Horiguchi <[email protected]> wrote:
>>
>> From a user's perspective, it seems sufficient to know that the
>> shifted timestamp falls outside the range supported by UUID v7. As a
>> translator, I'm not particularly enthusiastic about adding more
>> message variants when the distinction is not particularly useful to
>> users.
>
>
> Thanks for the feedback, Kyotaro. Good point. Attached v3 with all boundary checks
> using a single shared errdetail:
>
> "UUID version 7 supports timestamps from 1970-01-01 to approximately year 10889."
>
> 0001 - Reject infinite intervals
> 0002 - Reject pre-epoch timestamps (with overflow-safe epoch conversion)
> 0003 - Reject timestamps beyond the 48-bit limit
>
> I prefer keeping them as 3 patches since each addresses a distinct
> failure mode and is easier to review/bisect independently. That said,
> since 0002 and 0003 now share the same errdetail and are logically
> the same validation (timestamp outside valid range), I'm happy to
> merge them into one patch for v4 if preferred.
>
Thank you for updating the patch! I have one comment:
+ /*
+ * Convert a TimestampTz value back to a UNIX epoch timestamp. Use
+ * overflow-safe addition since large intervals can exceed int64 range.
+ */
+ if (pg_add_s64_overflow(ts,
+ (int64) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
+ SECS_PER_DAY * USECS_PER_SEC,
+ &us))
+ 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.")));
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.
---
+ /* UUID v7 uses an unsigned 48-bit millisecond field; reject pre-epoch */
+ 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 supports timestamps from
1970-01-01 to approximately year 10889.")));
+
+ /* Reject timestamps beyond the 48-bit millisecond field maximum */
+ if (us / US_PER_MS > (INT64CONST(1) << 48) - 1)
+ 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.")));
Let's merge these two if statements as they use the same error message.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-25 02:19 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
2026-06-25 13:47 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-29 23:18 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
@ 2026-07-03 16:34 ` Baji Shaik <[email protected]>
2026-07-03 19:54 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Baji Shaik @ 2026-07-03 16:34 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected]; [email protected]
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)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-25 02:19 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
2026-06-25 13:47 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-29 23:18 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-07-03 16:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
@ 2026-07-03 19:54 ` Zsolt Parragi <[email protected]>
2026-07-03 23:54 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Zsolt Parragi @ 2026-07-03 19:54 UTC (permalink / raw)
To: [email protected]
v4 looks good to me, I only have one completely optional nitpick comment:
+-- uuidv7: large future intervals that overflow epoch conversion are rejected
+SELECT uuidv7('292230 years'::interval);
+
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"?
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: uuidv7 improperly accepts dates before 1970-01-01
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-11 19:20 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-24 20:31 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-25 02:19 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Kyotaro Horiguchi <[email protected]>
2026-06-25 13:47 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-06-29 23:18 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Masahiko Sawada <[email protected]>
2026-07-03 16:34 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Baji Shaik <[email protected]>
2026-07-03 19:54 ` Re: uuidv7 improperly accepts dates before 1970-01-01 Zsolt Parragi <[email protected]>
@ 2026-07-03 23:54 ` Baji Shaik <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Baji Shaik @ 2026-07-03 23:54 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: [email protected]
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)
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-07-03 23:54 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-27 22:51 Re: uuidv7 improperly accepts dates before 1970-01-01 Christophe Pettus <[email protected]>
2026-05-28 00:02 ` Masahiko Sawada <[email protected]>
2026-05-28 01:00 ` Baji Shaik <[email protected]>
2026-06-11 19:20 ` Masahiko Sawada <[email protected]>
2026-06-12 22:34 ` Baji Shaik <[email protected]>
2026-06-24 15:25 ` Baji Shaik <[email protected]>
2026-06-24 17:46 ` Masahiko Sawada <[email protected]>
2026-06-24 20:02 ` Christophe Pettus <[email protected]>
2026-06-24 17:58 ` Tristan Partin <[email protected]>
2026-06-24 20:31 ` Zsolt Parragi <[email protected]>
2026-06-24 23:22 ` Baji Shaik <[email protected]>
2026-06-25 02:19 ` Kyotaro Horiguchi <[email protected]>
2026-06-25 13:47 ` Baji Shaik <[email protected]>
2026-06-26 04:57 ` Kyotaro Horiguchi <[email protected]>
2026-06-29 23:18 ` Masahiko Sawada <[email protected]>
2026-07-03 16:34 ` Baji Shaik <[email protected]>
2026-07-03 19:54 ` Zsolt Parragi <[email protected]>
2026-07-03 23:54 ` Baji Shaik <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox