public inbox for [email protected]
help / color / mirror / Atom feedFrom: Baji Shaik <[email protected]>
To: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
Date: Wed, 24 Jun 2026 18:22:20 -0500
Message-ID: <CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@mail.gmail.com> (raw)
In-Reply-To: <CAN4CZFP9XsTXijqxtK7v9Hgad3jQN-m+wBve0azdi3QkheJv7Q@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
<CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@mail.gmail.com>
<CAD21AoBpxjp3wjQ4dia0c76Bp8E=Q4UOgBTU4ZOn3Sj6k=-SsA@mail.gmail.com>
<CA+fm-RN4eMEr2tzZU_mAV-=WfdmPXJ4Ea_GpmSS8=yStSy8onQ@mail.gmail.com>
<CAN4CZFP9XsTXijqxtK7v9Hgad3jQN-m+wBve0azdi3QkheJv7Q@mail.gmail.com>
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)
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]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
In-Reply-To: <CA+fm-RPjp8fC-w50PApyhrfMqY4SA0zT5s1Nq=+03+ALcf+3+A@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