pgsql-hackers
❮
uuidv7 improperly accepts dates before 1970-01-01
- Jump to comment-1Christophe Pettus<xof@thebuild.com>Apr 25, 2026, 12:19 AM UTCHii,
When playing around with UUIDv7s, I discovered that it accepts this:
xof=# SELECT uuidv7(INTERVAL '-1000 years');uuidv7
e4ea52a0-bda1-7121-8f1f-3d9bb3d9a76e--------------------------------------
(1 row)
But RFC 9562 defines the time field as an unsigned number of milliseconds since Unix epoch, so timestamps earlier than that should be rejected. "Don't do that" is one answer, but for good hygiene, here's a patch that adds a < 0 check and a regression test. Applies cleanly to HEAD, make check passes.- Jump to comment-1Andrey Borodin<x4mmm@yandex-team.ru>Apr 25, 2026, 12:27 PM UTC
On 25 Apr 2026, at 05:19, Christophe Pettus <xof@thebuild.com> wrote:
Hi Christophe!
"Don't do that" is one answer, but for good hygiene, here's a patch that adds a < 0 check and a regression test.
We intentionally left ability to overflow unixtsms bits. In some cases one might want to
intentionally break time locality by using construction like SELECT uuidv7(INTERVAL '1000 years' * shard_id);
This will give time locality for UUIDs generated on each shard. We consulted with RFC authors
about this feature, and they confirmed that shifting time is compliant with RFC wording.
We wrote the specific test that ensures vast space for shift, but not unlimited.
Time shifting would become a footgun if we throw an exception when overflown.
If you use SELECT uuidv7(INTERVAL '-1000 years'); for generating identifiers, they will still be unique and
time-local, and more over - they will be ascending for a single backend. So no documented guarantees
are broken.
Thank you!
Best regards, Andrey Borodin.- Jump to comment-1Christophe Pettus<xof@thebuild.com>Apr 27, 2026, 10:51 PM UTCHi, 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 <x4mmm@yandex-team.ru> wrote:
We consulted with RFC authors
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.
about this feature, and they confirmed that shifting time is compliant with RFC wording.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.)