MIME-Version: 1.0
References: <799A70FA-6E5C-4118-99EB-2FBBE1CBAC54@thebuild.com>
 <966B5430-8ECD-48FA-B56F-22452D9CDBBF@yandex-team.ru>
 <E3B0C0EF-E9F3-4DCE-9F36-0C048E1D46D6@thebuild.com>
 <CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
In-Reply-To: 
 <CAD21AoAJN-Yb1mP5W-95UWQNMPgcE6OzCyJLKJRqTRrU5WxH5Q@mail.gmail.com>
From: Baji Shaik <baji.pgdev@gmail.com>
Date: Wed, 27 May 2026 20:00:50 -0500
Message-ID: 
 <CA+fm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2+2t1qWSDw@mail.gmail.com>
Subject: Re: uuidv7 improperly accepts dates before 1970-01-01
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Christophe Pettus <xof@thebuild.com>,
 Andrey Borodin <x4mmm@yandex-team.ru>,
	pgsql-hackers@lists.postgresql.org
Content-Type: multipart/mixed; boundary="0000000000001194f70652d643b0"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bfm-ROd4cNKM524n6EdgtZ9xOzOHJDNv8J_9Mvr2%2B2t1qWSDw%40mail.gmail.com>
Precedence: bulk

--0000000000001194f70652d643b0
Content-Type: multipart/alternative; boundary="0000000000001194f60652d643ae"

--0000000000001194f60652d643ae
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello Masahiko,

On Wed, May 27, 2026 at 7:02=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmail.=
com>
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

--0000000000001194f60652d643ae
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hello Masahiko,</div><br><div class=3D"gm=
ail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On W=
ed, May 27, 2026 at 7:02=E2=80=AFPM Masahiko Sawada &lt;<a href=3D"mailto:s=
awada.mshk@gmail.com">sawada.mshk@gmail.com</a>&gt; wrote:</div><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex">I guess it would be safer to raise a=
n error in such cases rather than<br>
silently allowing wraparound. Otherwise, users might only realize that<br>
their UUIDv7 values are no longer sortable years down the road, which<br>
would be disastrous. Moreover, raising an error would be consistent<br>
with how PostgreSQL natively handles timestamp + interval overflows.<br></b=
lockquote><div><br></div>+1.=C2=A0 I ran into the same issue while testing,=
 specifically,<br>uuidv7(&#39;infinity&#39;::interval) overflows int64 duri=
ng the epoch<br>conversion and produces a UUID with an incorrect timestamp.=
 =C2=A0</div><div class=3D"gmail_quote gmail_quote_container">There&#39;s n=
o valid use case for infinity as a shift offset.<br><br>I have a small patc=
h that adds a TIMESTAMP_NOT_FINITE check after<br>timestamptz_pl_interval()=
, which catches both infinity and<br>-infinity.=C2=A0 Happy to extend it to=
 also cover the 48-bit upper/lower<br><div>bound checks if we agree on the =
direction.=C2=A0</div><div><br></div><div>Thanks,</div><div>Baji Shaik</div=
></div></div>

--0000000000001194f60652d643ae--

--0000000000001194f70652d643b0
Content-Type: application/octet-stream;
	name="0001-Fix-uuidv7-with-infinite-interval-causing-integer-ov.patch"
Content-Disposition: attachment;
	filename="0001-Fix-uuidv7-with-infinite-interval-causing-integer-ov.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mposclld0>
X-Attachment-Id: f_mposclld0

RnJvbSBkYzI4YTA4YjJmZTE3OTkwMDUxNDU0ZjRkYjkzNWEwZWY0ZDAyM2QzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBCYWppIFNoYWlrIDxiYWppLnBnZGV2QGdtYWlsLmNvbT4KRGF0
ZTogV2VkLCAyNyBNYXkgMjAyNiAxOTo0MzoxNSAtMDUwMApTdWJqZWN0OiBbUEFUQ0hdIEZpeCB1
dWlkdjcoKSB3aXRoIGluZmluaXRlIGludGVydmFsIGNhdXNpbmcgaW50ZWdlciBvdmVyZmxvdwoK
dXVpZHY3KCdpbmZpbml0eSc6OmludGVydmFsKSBhbmQgdXVpZHY3KCctaW5maW5pdHknOjppbnRl
cnZhbCkgY2F1c2UKaW50ZWdlciBvdmVyZmxvdyB3aGVuIGNvbnZlcnRpbmcgdGhlIGluZmluaXRl
IFRpbWVzdGFtcFR6IGJhY2sgdG8gYQpVbml4IGVwb2NoIG1pY3Jvc2Vjb25kIHZhbHVlLiAgdGlt
ZXN0YW1wdHpfcGxfaW50ZXJ2YWwoKSByZXR1cm5zIHRoZQpzcGVjaWFsIGluZmluaXR5L25lZ2F0
aXZlLWluZmluaXR5IFRpbWVzdGFtcFR6IHZhbHVlcywgYW5kIHRoZQpzdWJzZXF1ZW50IGFkZGl0
aW9uIG9mIHRoZSBlcG9jaCBvZmZzZXQgb3ZlcmZsb3dzIGludDY0LCBwcm9kdWNpbmcgYQpnYXJi
YWdlIHRpbWVzdGFtcC4KCkZpeCBieSBjaGVja2luZyBUSU1FU1RBTVBfTk9UX0ZJTklURSh0cykg
aW1tZWRpYXRlbHkgYWZ0ZXIgdGhlCmludGVydmFsIGFyaXRobWV0aWMgYW5kIHJlamVjdGluZyB3
aXRoIGEgY2xlYXIgZXJyb3IuCi0tLQogc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3V1aWQuYyAgICAg
ICB8IDcgKysrKysrKwogc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC91dWlkLm91dCB8IDUgKysr
KysKIHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3V1aWQuc3FsICAgICAgfCA0ICsrKysKIDMgZmlsZXMg
Y2hhbmdlZCwgMTYgaW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxz
L2FkdC91dWlkLmMgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvdXVpZC5jCmluZGV4IDZlZTM3NTJh
Yzc4Li4yYmJhMDdiYjA5YyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3V1aWQu
YworKysgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvdXVpZC5jCkBAIC02ODcsNiArNjg3LDEzIEBA
IHV1aWR2N19pbnRlcnZhbChQR19GVU5DVElPTl9BUkdTKQogCQkJCQkJCQkJCQkJIFRpbWVzdGFt
cFR6R2V0RGF0dW0odHMpLAogCQkJCQkJCQkJCQkJIEludGVydmFsUEdldERhdHVtKHNoaWZ0KSkp
OwogCisKKwkvKiBSZWplY3QgaW5maW5pdGUgdGltZXN0YW1wcyAqLworCWlmIChUSU1FU1RBTVBf
Tk9UX0ZJTklURSh0cykpCisJCWVyZXBvcnQoRVJST1IsCisJCQkJKGVycmNvZGUoRVJSQ09ERV9E
QVRFVElNRV9WQUxVRV9PVVRfT0ZfUkFOR0UpLAorCQkJCSBlcnJtc2coInRpbWVzdGFtcCBvdXQg
b2YgcmFuZ2UgZm9yIFVVSUQgdmVyc2lvbiA3IikpKTsKKwogCS8qIENvbnZlcnQgYSBUaW1lc3Rh
bXBUeiB2YWx1ZSBiYWNrIHRvIGFuIFVOSVggZXBvY2ggdGltZXN0YW1wICovCiAJdXMgPSB0cyAr
IChQT1NUR1JFU19FUE9DSF9KREFURSAtIFVOSVhfRVBPQ0hfSkRBVEUpICogU0VDU19QRVJfREFZ
ICogVVNFQ1NfUEVSX1NFQzsKIApkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3Rl
ZC91dWlkLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvdXVpZC5vdXQKaW5kZXggOWM1
ZGRhOWU5YWIuLjQzODg3MzlmNDYyIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVj
dGVkL3V1aWQub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvdXVpZC5vdXQKQEAg
LTI4MCw2ICsyODAsMTEgQEAgU0VMRUNUIHV1aWRfZXh0cmFjdF92ZXJzaW9uKCcxMTExMTExMS0x
MTExLTExMTEtMTExMS0xMTExMTExMTExMTEnKTsgIC0tIG51bGwKIC0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0KICAgICAgICAgICAgICAgICAgICAgIAogKDEgcm93KQorLS0gdXVpZHY3KGludGVydmFs
KSByZWplY3RzIGluZmluaXRlIGludGVydmFscworU0VMRUNUIHV1aWR2NygnaW5maW5pdHknOjpp
bnRlcnZhbCk7CitFUlJPUjogIHRpbWVzdGFtcCBvdXQgb2YgcmFuZ2UgZm9yIFVVSUQgdmVyc2lv
biA3CitTRUxFQ1QgdXVpZHY3KCctaW5maW5pdHknOjppbnRlcnZhbCk7CitFUlJPUjogIHRpbWVz
dGFtcCBvdXQgb2YgcmFuZ2UgZm9yIFVVSUQgdmVyc2lvbiA3CiAKIFNFTEVDVCB1dWlkX2V4dHJh
Y3RfdmVyc2lvbih1dWlkdjQoKSk7ICAtLSA0CiAgdXVpZF9leHRyYWN0X3ZlcnNpb24gCmRpZmYg
LS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC91dWlkLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mv
c3FsL3V1aWQuc3FsCmluZGV4IDhjYzJhZDQwNjE0Li5kOWYwNDAxODgwYiAxMDA2NDQKLS0tIGEv
c3JjL3Rlc3QvcmVncmVzcy9zcWwvdXVpZC5zcWwKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwv
dXVpZC5zcWwKQEAgLTE0Niw2ICsxNDYsMTAgQEAgU0VMRUNUIHksIHRzLCBwcmV2X3RzIEZST00g
dXVpZHRzIFdIRVJFIHRzIDwgcHJldl90czsKIFNFTEVDVCB1dWlkX2V4dHJhY3RfdmVyc2lvbign
MTExMTExMTEtMTExMS01MTExLTgxMTEtMTExMTExMTExMTExJyk7ICAtLSA1CiBTRUxFQ1QgdXVp
ZF9leHRyYWN0X3ZlcnNpb24oZ2VuX3JhbmRvbV91dWlkKCkpOyAgLS0gNAogU0VMRUNUIHV1aWRf
ZXh0cmFjdF92ZXJzaW9uKCcxMTExMTExMS0xMTExLTExMTEtMTExMS0xMTExMTExMTExMTEnKTsg
IC0tIG51bGwKKworLS0gdXVpZHY3KGludGVydmFsKSByZWplY3RzIGluZmluaXRlIGludGVydmFs
cworU0VMRUNUIHV1aWR2NygnaW5maW5pdHknOjppbnRlcnZhbCk7CitTRUxFQ1QgdXVpZHY3KCct
aW5maW5pdHknOjppbnRlcnZhbCk7CiBTRUxFQ1QgdXVpZF9leHRyYWN0X3ZlcnNpb24odXVpZHY0
KCkpOyAgLS0gNAogU0VMRUNUIHV1aWRfZXh0cmFjdF92ZXJzaW9uKHV1aWR2NygpKTsgIC0tIDcK
IAotLSAKMi41MC4xIChBcHBsZSBHaXQtMTU1KQoK
--0000000000001194f70652d643b0--