public inbox for [email protected]
help / color / mirror / Atom feedFrom: Baji Shaik <[email protected]>
To: Masahiko Sawada <[email protected]>
Cc: Christophe Pettus <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
Date: Wed, 27 May 2026 20:00:50 -0500
Message-ID: <CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@mail.gmail.com> (raw)
In-Reply-To: <CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
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, 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)
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
In-Reply-To: <CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@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