public inbox for [email protected]
help / color / mirror / Atom feedFrom: Baji Shaik <[email protected]>
To: Kyotaro Horiguchi <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
Date: Thu, 25 Jun 2026 08:47:27 -0500
Message-ID: <CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com>
<CAN4CZFP9XsTXijqxtK7v9Hgad3jQN-m+wBve0azdi3QkheJv7Q@mail.gmail.com>
<CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@mail.gmail.com>
<[email protected]>
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)
view thread (18+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
In-Reply-To: <CA+fm-RN9UgAG2ew7T0Gum8RtZ4ZbyYvOLyyqAiuTWhsGEn3_zw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox