From a5419bc72e22337a16655aa28e2e20ecb65f65c3 Mon Sep 17 00:00:00 2001 From: Masahiko Sawada Date: Fri, 28 Mar 2025 09:39:11 -0700 Subject: Fix timestamp overflow in UUIDv7 implementation. The uuidv7_interval() function previously converted a shifted microsecond-precision timestamp (64-bit integer) to another 64-bit integer representing a timestamp with nanosecond precision. This conversion caused overflow for dates beyond the year 2262. The millisecond and sub-millisecond parts were then extracted from this nanosecond-precision timestamp and stored in UUIDv7 values. With this commit, the millisecond and sub-millisecond parts are stored directly into the UUIDv7 value without being converted back to a nanosecond precision timestamp. Following RFC 9562, the timestamp is stored as an unsigned integer, enabling support for dates up to the year 10889. Reported and fixed by Andrey Borodin, with cosmetic changes and regression tests by me. Reported-by: Andrey Borodin Author: Andrey Borodin Discussion: https://postgr.es/m/96DEC2D9-659A-40E8-B7BA-AF5D162A9E21@yandex-team.ru --- src/test/regress/expected/uuid.out | 15 +++++++++++++++ src/test/regress/sql/uuid.sql | 12 ++++++++++++ 2 files changed, 27 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out index 798633ad51e..95392003b86 100644 --- a/src/test/regress/expected/uuid.out +++ b/src/test/regress/expected/uuid.out @@ -233,6 +233,21 @@ SELECT array_agg(id ORDER BY guid_field) FROM guid3; {1,2,3,4,5,6,7,8,9,10} (1 row) +-- Check the timestamp offsets for v7. +-- +-- generate UUIDv7 values with timestamps ranging from 1970 (the Unix epoch year) +-- to 10888 (one year before the maximum possible year), and then verify that +-- the extracted timestamps from these UUIDv7 values have not overflowed. +WITH uuidts AS ( + SELECT y, ts as ts, lag(ts) OVER (ORDER BY y) AS prev_ts + FROM (SELECT y, uuid_extract_timestamp(uuidv7((y || ' years')::interval)) AS ts + FROM generate_series(1970 - extract(year from now())::int, 10888 - extract(year from now())::int) y) +) +SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts; + y | ts | prev_ts +---+----+--------- +(0 rows) + -- 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 110188361d1..465153a0341 100644 --- a/src/test/regress/sql/uuid.sql +++ b/src/test/regress/sql/uuid.sql @@ -119,6 +119,18 @@ SELECT count(DISTINCT guid_field) FROM guid1; INSERT INTO guid3 (guid_field) SELECT uuidv7() FROM generate_series(1, 10); SELECT array_agg(id ORDER BY guid_field) FROM guid3; +-- Check the timestamp offsets for v7. +-- +-- generate UUIDv7 values with timestamps ranging from 1970 (the Unix epoch year) +-- to 10888 (one year before the maximum possible year), and then verify that +-- the extracted timestamps from these UUIDv7 values have not overflowed. +WITH uuidts AS ( + SELECT y, ts as ts, lag(ts) OVER (ORDER BY y) AS prev_ts + FROM (SELECT y, uuid_extract_timestamp(uuidv7((y || ' years')::interval)) AS ts + FROM generate_series(1970 - extract(year from now())::int, 10888 - extract(year from now())::int) y) +) +SELECT y, ts, prev_ts FROM uuidts WHERE ts < prev_ts; + -- extract functions -- version -- cgit v1.2.3