Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPmqG-00E8fb-Vg for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Jan 2024 17:03:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rPmqG-005N2C-2a for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Jan 2024 17:03:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPmdf-005EF2-Bt for pgsql-hackers@lists.postgresql.org; Tue, 16 Jan 2024 16:50:11 +0000 Received: from sonic301-22.consmr.mail.gq1.yahoo.com ([98.137.64.148]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rPmda-001qJ6-UT for pgsql-hackers@postgresql.org; Tue, 16 Jan 2024 16:50:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.au; s=s2048; t=1705423804; bh=3dskPTIlr3vCOD7aE15iG67yXmgvM+7Yw4De1HBOAZQ=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=JQA9STSMqj4fGYFumD0qW12T1DpyVRUnw8bzXijCXTcs8NGTehXzc+2XlaU0XaSlXuBgkgapm2BtYYFTso9uaH1jP9vkGxFGWVidaKtRKC8FL0RRcWYOByBsAzcYam5aMcW17Qp30oC8256v9KAn1T4ZyTaYulCo9ODArLmh0261LHJPXxZ5Jio619dBR9EBbJWbOyoxJqhF2UxGS90ta1aPAH1cNbqlHRj9hclZvnz5vOIJZRt/xKAZ3I+vCMv9YrJSnRpv27q4q4Wa9EgkplGxTJk73ExxTT8I6Lc6glaawpN1QlFBEBAxTMoZuVIvJT1oqiFrWOiYJ5RrNhKyvQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1705423804; bh=wRFvufcuZzOPu4ysvJAC8Qvqx6sBzo4xdyNaDocsXFX=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=k755xPO6LpdrcO2pOggj1vRp42vdXr408lx0Mb2Bl8r53h+KOMazLkEaRhjRpGyzz3R9pTbmN9BO3HG2175X1Q3Oj0O0TeG5F4lyQw1mwEiFRBDXvPIYkn/Wlo9zR8leadWWf1cXnJFGotEtEiqTFs/L/SdWpY7G/8937MijwZZbfJjsru88FyMSpS0zhY1Wi9dQOGMeg+jERa3AsJdevaXTe4GpPaZDlIDQtWP/ijygvXK85qwSUzXPIwmkNv257/k4DqDUo/7HfCtmBDkIhi2GS40j1AR7f8NeOelVa/0jc+r8cNJ1/86FHq5+RNBsWbzjRrE9yD9YSvkIRouGaQ== X-YMail-OSG: mM6xFu8VM1lbB64cSs7TiaFCfkcS6S_ZsjpXlGItpTfZ76QIRmbk.38MKVqie__ nmAvOVS.PTIKvR5ozxxZoWXDxECKLABLIsmCVwwv9w1bfaEXYQLpmEjAXIV3qaPHlgz3QBtffhw9 vx9_OJcta9ez7s.x7uGU86aJtBXXZxvGkhyh3GIRq63tgfcngZHH0HT.3_uXexeJu_SaRXhOIuZf mY1glZ6jFwcTtPvznJLIZKBVY6n_zsLbZ40p0XG80tv2AxQhw40yxOBh9RVeRtzVxYb0_zaJbsva asNHMXy6rm74IEOerYXztfZWeHTh9dQ8170XjbC0Ec_J_nJXsKEtes.2lvjKQH_RyZ45Ibb.kVFE A2WDLKHxhzSHFR01YX43j_9qF8zL1JGmynyB1_KOzHiy5Z_z7G7LeMdxClZSFFj9CmNOyoPNAXH_ QKJLoKZII60K5rx800EA3XfhU2LdP0SFBGgwSeESdsQGkj_mzoEA9YhzpHHy_3m1mas8TMwfQkYK DpOCLbHP4xXRBuRPpxEIPsNN45vQwlorBVSoFaBsuvWgpHFS921RBmTEibMFG2bZX5ouJ_7ga_zD zHoB3.9Q7NXYjHEuf2rrKNR.0d5fGLHruaCu_kYasN796xmBDMUULlKElnA0RnGiu0ZTkmLkhOFc gLDxV2diMP_7kAGai0dYevHONMuPXMjS_z2naN0pI4OYPYr7FmjjEagT0CVE9mmDmPQx2g_z8Oix 8zXvIQ1a7KgIQryfqEHOZ9GmVg.WQOdsvuZcM6DFijGVvijCCPleiVQraJykVz3TQfddWfdT0IwY nL0Mn_HdOfvHwUGQDpwgvUVsGnKwaGuztX1Q9IL07n1qui3tfij.J4DVIsi_AUD_.DNh1MYlKZVt N7LCsZH9anrmsiq9LtfkQGTK38LwAqPwP.UVonf3W7vqBMAmocwSk8_LJX_tb6OptdZ.PSdQdagq f3KKdmVZ02vQoeB_x47pd40mS._h8pn4.3FEgcHAVZyy9NIgtY1FF7Ot7YBtBx9gHlX.E4.80T0n kh7iRUjhSmGhlQQi8PiK1coAV55SQidXm7MA6SNccRZmenkdpOSjgV0qmmooqZ_I15cn7QIWCooO 6GK7Zt2EEtIXNS_7PYgCUFVKnYWXqNc53oX0xkBlFlVNoVd4WSclKTjLVaQ0ct7zDFuZxBomrqkM I9pZMa6JV50Uw8RPNvTfzT6j_.j.SbUr1sjgDlI_kFZGZVSstuZuN9KfUjq5y5h3RQHKkoP8loY. ncRlz5HKb8WZvi2S_lAKC4nnBT4obrWJQRcmY71976fBdYDZA9M4_rKzr6MUmP9HTP.SyExTgFsp egOI9xsO._OH88EAwuHTF9GxH1tAYV9QBY5QsorXXByLo3Ru5G0O3Z_GOKm2Jf436gDvuFnfN_kq qVZim7hjn7uz4sIVYtNPImyiqLnO4Dt.OPd6jG60FaVLymyT0LJ_KCECrv6HgQJXkT66iNH0f8lI xCUuC2Cmiu8D.64VC8.bj2ojziHGfFL.xz38nzCB4ST2KY2QtCS81iNuMdn0xMA2wbf6FV8INeoQ y0_MZtraHdyi7mxvhSnnqNR8yVFyt63ZGyLHLBFlT89NioCkmLZNsOkxlUDwGdxUycrKYm5KzEdM OuHI1S5TABD8NG3W_ohqHvHN8lpUZZSio3PlWZdak1MM3h0J9tbqMoPo6yDrrgvXJLhciWFiAlwW uc19ErfwweqyGTqbyI4i.cpt3Ayf3jt0HFz2up.qZoFP6VcbKsHN40DupVwRMHKPSWwkZscfpi_6 60cCrTQ1loyTVkE8F50ASxEWkoO4QF.Oz7nCeTk.IO_.jB6V3BXwuoZQQCs_lJq7HjA8KrKT51wh mzc7ctJFjK3KamvC_gr5sBJoLuyoZXz.oxqMFGyMv6SditiicPhmQ8316l_hwRdZI5smqzmRYHYX HoFENCT7R7Fj0wfKKqLX.UFPZxvrzAdn2wdYMyMqUPTDt36DbeWXti.F6LGDhGD2dk9bSKwEJev8 Rlja6ShLjXDP7q5PRwLOBtQEgwmJrx8uXWW5X8JblwJflkW3QjPd8VpkZgo8Wy7XkVyfTlZHmIbT f7YktJA_gnEay9rvELVJklXdINbZq10rMji_CG2dOfWOAIBJAZo.z9L2kZFj6xG5uONtchmvVgvz 6ibeRjTUciXQgpO.Ff7s93M3qSjIi3UfLF2KF2dMyumLS31NZGMBWf1XZfYPrzQhvWAEDGJaXu2q a25zv.FjzsWBxPpwdnQAO20mEnIPHroRnunE6yUTIOIhTGz4FgumDMpei7odpXX81rKdS1Qz5Y0D 2 X-Sonic-MF: X-Sonic-ID: 56243fcd-17c9-4e9f-8680-65abf2564bd0 Received: from sonic.gate.mail.ne1.yahoo.com by sonic301.consmr.mail.gq1.yahoo.com with HTTP; Tue, 16 Jan 2024 16:50:04 +0000 Date: Tue, 16 Jan 2024 16:49:13 +0000 (UTC) From: Sergey Prokhorenko To: Aleksander Alekseev , "Andrey M. Borodin" Cc: pgsql-hackers mailing list , Jelte Fennema-Nio , =?UTF-8?Q?Przemys=C5=82aw_Sztoch?= , Nick Babadzhanian , Mat Arye , Peter Eisentraut , Tom Lane , Daniel Gustafsson , Matthias van de Meent , Nikolay Samokhvalov , "Kyzer Davis (kydavis)" , Andres Freund , "brad@peabody.io" , Kirk Wolak Message-ID: <1199536369.1612345.1705423753699@mail.yahoo.com> In-Reply-To: <2ECBCFF4-57A5-4484-B6D3-872596E40654@yandex-team.ru> References: <5dc6fdaa-5896-ebc4-7094-eac45a818846@enterprisedb.com> <84D20D0F-B5FF-41CD-9F48-E282CE9FEC1D@yandex-team.ru> <3BDCCF0B-9C96-416C-87F6-071ABD7AED8B@yandex-team.ru> <3E7F8826-A275-44B6-A374-6E35B3B60AD5@yandex-team.ru> <6941A45B-C7D4-414B-A3A9-ACED172DFBD5@yandex-team.ru> <8DB43887-CA09-4845-83C0-33E8A79D0711@yandex-team.ru> <1003561938.6914482.1704452267331@mail.yahoo.com> <80E8F728-FC9C-461E-9B61-332E3AAEC066@yandex-team.ru> <2ECBCFF4-57A5-4484-B6D3-872596E40654@yandex-team.ru> Subject: Re: UUID v7 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1612344_1145443644.1705423753697" X-Mailer: WebService/1.1.22010 YMailNorrin Content-Length: 11369 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_1612344_1145443644.1705423753697 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Andrey, It is not clear how to interpret uuid_v7_time(): =20 - uuid_v7 to time() (extracting the timestamp) - time() to uuid_v7=C2=A0=C2=A0(generation of the uuid_v7) It is worth improving the naming, for example, adding prepositions. Sergey Prokhorenkosergeyprokhorenko@yahoo.com.au=20 On Tuesday, 16 January 2024 at 05:44:51 pm GMT+3, Andrey M. Borodin wrote: =20 =20 Thanks for your review, Aleksander! > On 16 Jan 2024, at 18:00, Aleksander Alekseev = wrote: >=20 >=20 > ``` > +Datum > +pg_node_tree_in(PG_FUNCTION_ARGS) > +{ > +=C2=A0 =C2=A0 if (!IsBootstrapProcessingMode()) > +=C2=A0 =C2=A0 =C2=A0 =C2=A0 elog(ERROR, "cannot accept a value of type p= g_node_tree_in"); > +=C2=A0 =C2=A0 return textin(fcinfo); > +} > ``` >=20 > Not 100% sure what this is for. Any chance this could be part of another = patch? Nope, it=E2=80=99s necessary there. Without these changes catalog functions= cannot have defaults for arguments. These defaults have type pg_node_tree = which has no-op in function. > One thing I don't particularly like about the tests is the fact that > they don't check if a correct UUID was actually generated. I realize > that's not quite trivial due to the random nature of the function, but > maybe we could use some substring/regex magic here? Something like: >=20 > ``` > select gen_uuid_v7() :: text ~ '^[0-9a-f]{8}-([0-9a-f]{4}-){3}[0-9a-f]{12= }$'; > ?column? > ---------- > t >=20 > select regexp_replace(gen_uuid_v7('2024-01-16 15:45:33 MSK') :: text, > '[0-9a-f]{4}-[0-9a-f]{12}$', 'XXXX-' || repeat('X', 12)); >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 regexp_replace > -------------------------------------- > 018d124e-39c8-74c7-XXXX-XXXXXXXXXXXX > ``` Any 8 bytes which have ver and var bits (6 bits total) are correct UUID. This is checked by tests when uuid_var() and uuid_ver() functions are exerc= ised. > ``` > +=C2=A0 proname =3D> 'uuid_v7_time', proleakproof =3D> 't', provolatile = =3D> 'i', > ``` >=20 > I don't think we conventionally specify IMMUTABLE volatility, it's the > default. Other values also are worth checking. Makes sense, I=E2=80=99ll drop this values in next version. BTW I=E2=80=99m in doubt if provided functions are leakproof. They ERROR-ou= t with messages that can give a clue about several bits of UUID. Does this = break leakproofness? I think yest, but I=E2=80=99m not sure. gen_uuid_v7() seems leakproof to me. > Another question: how did you choose between using TimestampTz and > Timestamp types? I realize that internally it's all the same. Maybe > Timestamp will be slightly better since the way it is displayed > doesn't depend on the session settings. Many people I talked to find > this part of TimestampTz confusing. I mean, this argument is expected to be used to implement K-way sorted iden= tifiers. In this context, it seems to me, it=E2=80=99s good to remember to = developer that time shift also depend on timezones. But this is too vague. Do you have any reasons that apply to UUID generation? > Also I would like to point out that part of the documentation is > missing, but I guess at this stage of the game it's OK. >=20 > Last but not least: maybe we should support casting Timestamp[Tz] to > UUIDv7 and vice versa? Shouldn't be difficult to implement and I > suspect somebody will request this eventually. During the cast to UUID > we will always get the same value for the given Timestamp[Tz], which > probably can be useful in certain applications. It can't be done with > gen_uuid_v7() and its volatility doesn't permit it. I=E2=80=99m strongly opposed to doing this cast. I was not adding this func= tion to extract timestamp from UUID, because standard does not recommend it= . But a lot of people asked for this. But supporting easy way to do unrecommended thing seem bad. Best regards, Andrey Borodin. =20 ------=_Part_1612344_1145443644.1705423753697 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Andrey,

=
It is not clear how to interpret uuid_v7_time():
  • uuid_v7 to time() (extra= cting the timestamp)
  • time() to uuid_v7  (generation of the uuid_v7)
= It is worth improving the naming, for example, adding prepositions.


Sergey Prokhorenko
sergeyprokhorenko@yahoo.com.au
<= /div>


=20
=20
On Tuesday, 16 January 2024 at 05:44:51 pm GMT+3, Andre= y M. Borodin <x4mmm@yandex-team.ru> wrote:


Thanks for your review, Aleksander!
> On 16 Jan 2024, at 18:00, Aleksand= er Alekseev <aleksander@timescale.com> wrote:<= br clear=3D"none">>
>
> ```<= br clear=3D"none">> +Datum
> +pg_node_tree_in(PG_FU= NCTION_ARGS)
> +{
> +   = ; if (!IsBootstrapProcessingMode())
> +    &= nbsp;   elog(ERROR, "cannot accept a value of type pg_node_tree_in");<= br clear=3D"none">> +    return textin(fcinfo);
> +}
> ```
>
> Not 100% sure what this is for. Any chance this could be part of= another patch?
Nope, it=E2=80=99s necessary there. Witho= ut these changes catalog functions cannot have defaults for arguments. Thes= e defaults have type pg_node_tree which has no-op in function.

> One thing I don't particularly like about the= tests is the fact that
> they don't check if a correc= t UUID was actually generated. I realize
> that's not = quite trivial due to the random nature of the function, but
> maybe we could use some substring/regex magic here? Something like:=
>
> ```
> = select gen_uuid_v7() :: text ~ '^[0-9a-f]{8}-([0-9a-f]{4}-){3}[0-9a-f]{12}$= ';
> ?column?
> ----------
> t
>
> select re= gexp_replace(gen_uuid_v7('2024-01-16 15:45:33 MSK') :: text,
> '[0-9a-f]{4}-[0-9a-f]{12}$', 'XXXX-' || repeat('X', 12));
>            regexp_replace
> --------------------------------------
> 018d124e-39c8-74c7-XXXX-XXXXXXXXXXXX
> ```
Any 8 bytes which have ver and var bits (6 bits total) are cor= rect UUID.
This is checked by tests when uuid_var() and u= uid_ver() functions are exercised.

>= ; ```
> +  proname =3D> 'uuid_v7_time', prolea= kproof =3D> 't', provolatile =3D> 'i',
> ```
>
> I don't think we conventionally= specify IMMUTABLE volatility, it's the
> default. Oth= er values also are worth checking.
Makes sense, I=E2=80= =99ll drop this values in next version.
BTW I=E2=80=99m i= n doubt if provided functions are leakproof. They ERROR-out with messages t= hat can give a clue about several bits of UUID. Does this break leakproofne= ss? I think yest, but I=E2=80=99m not sure.
gen_uuid_v7()= seems leakproof to me.

> Another q= uestion: how did you choose between using TimestampTz and
> Timestamp types? I realize that internally it's all the same. Maybe> Timestamp will be slightly better since the way it is= displayed
> doesn't depend on the session settings. M= any people I talked to find
> this part of TimestampTz= confusing.

I mean, this argument is e= xpected to be used to implement K-way sorted identifiers. In this context, = it seems to me, it=E2=80=99s good to remember to developer that time shift = also depend on timezones.
But this is too vague.
Do you have any reasons that apply to UUID generation?


> Also I would like to point out that part of the = documentation is
> missing, but I guess at this stage = of the game it's OK.
>
> Last bu= t not least: maybe we should support casting Timestamp[Tz] to
> UUIDv7 and vice versa? Shouldn't be difficult to implement and I<= br clear=3D"none">> suspect somebody will request this eventually. Durin= g the cast to UUID
> we will always get the same value= for the given Timestamp[Tz], which
> probably can be = useful in certain applications. It can't be done with
>= ; gen_uuid_v7() and its volatility doesn't permit it.

I=E2=80=99m strongly opposed to doing this cast. I was not adding this f= unction to extract timestamp from UUID, because standard does not recommend= it. But a lot of people asked for this.
But supporting e= asy way to do unrecommended thing seem bad.


Best regards, Andrey Borodin.
------=_Part_1612344_1145443644.1705423753697--