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 1rjLj2-0052Bv-1x for pgsql-hackers@arkaria.postgresql.org; Sun, 10 Mar 2024 16:08:37 +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 1rjLj0-004wRv-3d for pgsql-hackers@arkaria.postgresql.org; Sun, 10 Mar 2024 16:08:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjLiz-004wRn-Iz for pgsql-hackers@lists.postgresql.org; Sun, 10 Mar 2024 16:08:34 +0000 Received: from forwardcorp1b.mail.yandex.net ([178.154.239.136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjLiv-003mef-K5 for pgsql-hackers@postgresql.org; Sun, 10 Mar 2024 16:08:33 +0000 Received: from mail-nwsmtp-smtp-corp-main-26.myt.yp-c.yandex.net (mail-nwsmtp-smtp-corp-main-26.myt.yp-c.yandex.net [IPv6:2a02:6b8:c12:30ad:0:640:87bc:0]) by forwardcorp1b.mail.yandex.net (Yandex) with ESMTPS id 41BA760ACC; Sun, 10 Mar 2024 19:08:27 +0300 (MSK) Received: from smtpclient.apple (unknown [2a02:6b8:b081:6412::1:4]) by mail-nwsmtp-smtp-corp-main-26.myt.yp-c.yandex.net (smtpcorp/Yandex) with ESMTPSA id O8nBBW0IZiE0-786dS35J; Sun, 10 Mar 2024 19:08:26 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex-team.ru; s=default; t=1710086906; bh=TIHFPHDCDRVmqi/LOxmkwHFDtYXAcBoKmuKeK8+xNSA=; h=References:To:Cc:In-Reply-To:Date:From:Message-Id:Subject; b=pxQ3SPq1OLqyA72hawpuYCrehI4Rd/SDEffMxzugzULd4v39Ofi1zRJ9dzkfSX6Bd 7J6F5V1ThSGB38OSBVVRaygKUPT9WlPZh72e3dj33Np5no9Lekxdg4FPRoxxeV4t5E 0M3PN81gqG1vnJ1rpNM1b2zH+44NaMVdwzhR8VMs= Authentication-Results: mail-nwsmtp-smtp-corp-main-26.myt.yp-c.yandex.net; dkim=pass header.i=@yandex-team.ru From: "Andrey M. Borodin" Message-Id: <5D22C2BF-5DED-43CA-A3D7-B724EE0A159D@yandex-team.ru> Content-Type: multipart/mixed; boundary="Apple-Mail=_0EE87801-357B-4130-BC14-339C05A5E643" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.4\)) Subject: Re: UUID v7 Date: Sun, 10 Mar 2024 21:08:24 +0500 In-Reply-To: Cc: Sergey Prokhorenko , Jelte Fennema-Nio , pgsql-hackers mailing list , Aleksander Alekseev , =?utf-8?Q?Przemys=C5=82aw_Sztoch?= , "David G. Johnston" , Mat Arye , Matthias van de Meent , Nikolay Samokhvalov , Junwang Zhao To: Peter Eisentraut References: <0A0D7507-C434-46AC-BCC4-1166C5A2742A@yandex-team.ru> <1459150585.359059.1706132985954@mail.yahoo.com> <00B784AB-5B52-4CC7-8A0D-B1C0C53B52DC@yandex-team.ru> <1014147035.642629.1706202245918@mail.yahoo.com> <5932FFF8-4E5F-44DC-8FEA-22E8302759C7@yandex-team.ru> <8AA22330-7D3A-46CF-B311-2FF874D7C713@yandex-team.ru> <1971811009.2132347.1706599708209@mail.yahoo.com> <72C1E7CB-E2EC-46CF-AD2C-B3180CED885B@yandex-team.ru> <0906F8BA-CA52-4956-AA68-E9193E50DFDF@yandex-team.ru> <10553e4c-6b66-44b9-86a7-80bb1958a767@eisentraut.org> X-Mailer: Apple Mail (2.3696.120.41.1.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_0EE87801-357B-4130-BC14-339C05A5E643 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On 10 Mar 2024, at 17:59, Andrey M. Borodin = wrote: >=20 > I tried to "make docs", but it gives me gazilion of errors... Is there = an easy way to see resulting HTML? Oops, CFbot expectedly found a problem... Sorry for the noise, this version, I hope, will pass all the tests. Thanks! Best regards, Andrey Borodin. --Apple-Mail=_0EE87801-357B-4130-BC14-339C05A5E643 Content-Disposition: attachment; filename=v19-0001-Implement-UUID-v7.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v19-0001-Implement-UUID-v7.patch" Content-Transfer-Encoding: quoted-printable =46rom=2053c1799cc731b1c254cd3fe185c12532367e61c1=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Andrey=20M.=20Borodin"=20=0A= Date:=20Sun,=2020=20Aug=202023=2023:55:31=20+0300=0ASubject:=20[PATCH=20= v19]=20Implement=20UUID=20v7=0AMIME-Version:=201.0=0AContent-Type:=20= text/plain;=20charset=3DUTF-8=0AContent-Transfer-Encoding:=208bit=0A=0A= This=20commit=20adds=20function=20for=20UUID=20generation.=20Most=20= important=20function=20here=0Ais=20uuidv7()=20which=20generates=20new=20= UUID=20according=20to=20the=20new=20standard.=0AFor=20code=20readability=20= this=20commit=20adds=20alias=20uuidv4()=20to=20function=20= gen_random_uuid().=0A=0AAlso=20we=20add=20a=20function=20to=20extract=20= timestamp=20from=20UUID=20v1,=20v6=20and=20v7.=0ATo=20allow=20user=20to=20= distinguish=20various=20UUID=20versions=20and=20variants=0Awe=20add=20= functions=20uuid_extract_version()=20and=20uuid_extract_variant().=0A=0A= Author:=20Andrey=20Borodin=0AReviewers:=20Sergey=20Prokhorenko,=20Kirk=20= Wolak,=20Przemys=C5=82aw=20Sztoch=0AReviewers:=20Nikolay=20Samokhvalov,=20= Jelte=20Fennema-Nio,=20Aleksander=20Alekseev=0AReviewers:=20Peter=20= Eisentraut,=20Chris=20Travers,=20Lukas=20Fittl=0ADiscussion:=20= https://postgr.es/m/CAAhFRxitJv%3DyoGnXUgeLB_O%2BM7J2BJAmb5jqAT9gZ3bij3uLD= A%40mail.gmail.com=0A---=0A=20doc/src/sgml/func.sgml=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20|=20=2054=20++++-=0A=20= src/backend/utils/adt/uuid.c=20=20=20=20=20=20=20=20=20=20=20=20=20|=20= 278=20+++++++++++++++++++++++=0A=20src/include/catalog/pg_proc.dat=20=20=20= =20=20=20=20=20=20=20|=20=2015=20++=0A=20= src/include/datatype/timestamp.h=20=20=20=20=20=20=20=20=20|=20=20=203=20= +-=0A=20src/test/regress/expected/opr_sanity.out=20|=20=20=205=20+=0A=20= src/test/regress/expected/uuid.out=20=20=20=20=20=20=20|=20=2071=20= ++++++=0A=20src/test/regress/sql/uuid.sql=20=20=20=20=20=20=20=20=20=20=20= =20|=20=2026=20+++=0A=207=20files=20changed,=20448=20insertions(+),=204=20= deletions(-)=0A=0Adiff=20--git=20a/doc/src/sgml/func.sgml=20= b/doc/src/sgml/func.sgml=0Aindex=205030a1045f..34977163cf=20100644=0A---=20= a/doc/src/sgml/func.sgml=0A+++=20b/doc/src/sgml/func.sgml=0A@@=20= -14130,13=20+14130,61=20@@=20CREATE=20TYPE=20rainbow=20AS=20ENUM=20= ('red',=20'orange',=20'yellow',=20'green',=20'blue',=20'purple=0A=20=20=20= =20gen_random_uuid=0A=20=20=20=0A=20=0A+=20= =20=0A+=20=20=20uuidv4=0A+=20=20= =0A+=0A+=20=20=0A+=20=20=20= uuidv7=0A+=20=20=0A+=0A+=20=20=0A= +=20=20=20uuid_extract_timestamp=0A+=20=20= =0A+=0A+=20=20=0A+=20=20=20= uuid_extract_version=0A+=20=20=0A+=0A+=20=20= =0A+=20=20=20uuid_extract_variant=0A+=20=20= =0A+=0A=20=20=20=0A-=20=20=20= PostgreSQL=20includes=20one=20function=20to=20= generate=20a=20UUID:=0A+=20=20=20PostgreSQL=20= includes=20several=20functions=20to=20generate=20a=20UUID.=0A=20= =0A=20gen_random_uuid=20()=20= uuid=0A+uuidv4=20()=20= uuid=0A+=0A+=20=20=20These=20= functions=20return=20a=20version=204=20(random)=20UUID.=0A+=0A= +uuidv7=20()=20uuid=0A= +=0A+=20=20=20This=20function=20returns=20a=20version=207=20= UUID=20(UNIX=20timestamp=20with=201ms=20precision=20+=0A+=20=20=20= randomly=20seeded=20counter=20+=20random).=0A+=20=20=0A+=0A+=20=20= =0A+=0A+uuid_extract_timestamp=20= (uuid)=20timestamptz=0A+=0A+=20=20=20= This=20function=20extracts=20a=20timestamp=20with=20time=20= zone=20from=20UUID=20versions=201,=206=20and=207.=0A+=20=20=20For=20= other=20versions=20and=20variants=20this=20function=20returns=20NULL.=20= The=20extracted=20timestamp=0A+=20=20=20does=20not=20necessarily=20= equate=20to=20the=20time=20of=20UUID=20generation.=20How=20close=20it=20= is=0A+=20=20=20to=20the=20actual=20time=20depends=20on=20the=20= implementation=20that=20generated=20to=20UUID.=0A+=20=20=20The=20= uuidv7()=20function=20provided=20by=20PostgreSQL=20will=20normally=20= store=20the=20actual=20time,=0A+=20=20=20with=20some=20exceptions:=20= prevention=20of=20time=20leaps=20backwards=20and=20counter=20overflow=0A= +=20=20=20being=20carried=20to=20time=20step.=0A+=0A= +uuid_extract_version=20(uuid)=20= smallint=0A+=0A+=20=20=20This=20= function=20extracts=20a=20version=20bits=20from=20UUID=20of=20variant=20= described=20by=0A+=20=20=20IETF=20standard=20draft=0A+=20=20=20(b10xx=20variant).=20For=20= other=20variants=20this=20function=20returns=20NULL.=0A+=0A= +uuid_extract_variant=20(uuid)=20= smallint=0A=20=0A-=20=20=20This=20= function=20returns=20a=20version=204=20(random)=20UUID.=20=20This=20is=20= the=20most=20commonly=0A-=20=20=20used=20type=20of=20UUID=20and=20is=20= appropriate=20for=20most=20applications.=0A+=20=20=20This=20function=20= extracts=20a=20vartiant=20bits=20from=20UUID.=0A=20=20=20=0A=20=0A= =20=20=20=0Adiff=20--git=20a/src/backend/utils/adt/uuid.c=20= b/src/backend/utils/adt/uuid.c=0Aindex=2073dfd711c7..72faec3e66=20100644=0A= ---=20a/src/backend/utils/adt/uuid.c=0A+++=20= b/src/backend/utils/adt/uuid.c=0A@@=20-13,13=20+13,18=20@@=0A=20=0A=20= #include=20"postgres.h"=0A=20=0A+#include=20=0A+=0A+#include=20= "access/xlog.h"=0A=20#include=20"common/hashfn.h"=0A=20#include=20= "lib/hyperloglog.h"=0A=20#include=20"libpq/pqformat.h"=0A=20#include=20= "port/pg_bswap.h"=0A=20#include=20"utils/builtins.h"=0A+#include=20= "utils/datetime.h"=0A=20#include=20"utils/guc.h"=0A=20#include=20= "utils/sortsupport.h"=0A+#include=20"utils/timestamp.h"=0A=20#include=20= "utils/uuid.h"=0A=20=0A=20/*=20sortsupport=20for=20uuid=20*/=0A@@=20= -402,6=20+407,11=20@@=20uuid_hash_extended(PG_FUNCTION_ARGS)=0A=20=09= return=20hash_any_extended(key->data,=20UUID_LEN,=20PG_GETARG_INT64(1));=0A= =20}=0A=20=0A+/*=0A+=20*=20Routine=20to=20generate=20UUID=20version=204.=0A= +=20*=20All=20UUID=20bytes=20are=20filled=20with=20strong=20random=20= numbers=20except=20version=20and=0A+=20*=20variant=200b10=20bits.=0A+=20= */=0A=20Datum=0A=20gen_random_uuid(PG_FUNCTION_ARGS)=0A=20{=0A@@=20= -421,3=20+431,271=20@@=20gen_random_uuid(PG_FUNCTION_ARGS)=0A=20=0A=20=09= PG_RETURN_UUID_P(uuid);=0A=20}=0A+=0A+static=20uint32_t=20= sequence_counter;=0A+static=20uint64_t=20previous_timestamp=20=3D=200;=0A= +=0A+/*=0A+=20*=20Routine=20to=20generate=20UUID=20version=207.=0A+=20*=20= Following=20description=20is=20taken=20from=20RFC=20draft=20and=20= slightly=20extended=20to=0A+=20*=20reflect=20implementation=20specific=20= choices.=0A+=20*=0A+=20*=20UUIDv7=20Field=20and=20Bit=20Layout:=0A+=20*=20= ----------=0A+=20*=20=200=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=201=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=202=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=203=0A+=200=201=202=203=20= 4=205=206=207=208=209=200=201=202=203=204=205=206=207=208=209=200=201=20= 2=203=204=205=206=207=208=209=200=201=0A+=20*=20= +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+=0A+=20= *=20|=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20unix_ts_ms=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20|=0A+=20*=20= +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+=0A+=20= *=20|=20=20=20=20=20=20=20=20=20=20unix_ts_ms=20=20=20=20=20=20=20=20=20=20= =20|=20=20ver=20=20|=20=20=20=20=20=20=20rand_a=20=20=20=20=20=20=20=20=20= =20|=0A+=20*=20= +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+=0A+=20= *=20|var|=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20rand_b=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20|=0A+=20*=20= +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+=0A+=20= *=20|=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20rand_b=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20|=0A+=20*=20= +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+=0A+=20= *=0A+=20*=20unix_ts_ms:=0A+=20*=20=2048=20bit=20big-endian=20unsigned=20= number=20of=20Unix=20epoch=20timestamp=20in=20milliseconds=0A+=20*=20=20= as=20per=20Section=206.1.=20Occupies=20bits=200=20through=2047=20(octets=20= 0-5).=0A+=20*=0A+=20*=20ver:=0A+=20*=20=20The=204=20bit=20version=20= field=20as=20defined=20by=20Section=204.2,=20set=20to=200b0111=20(7).=0A= +=20*=20=20Occupies=20bits=2048=20through=2051=20of=20octet=206.=0A+=20*=0A= +=20*=20rand_a:=0A+=20*=20=20Most=20significant=2012=20bits=20of=20= 18-bit=20counter.=20This=20counter=20is=20designed=20to=0A+=20*=20=20= guarantee=20additional=20monotonicity=20as=20per=20Section=206.2=20= (Method=202).=20rand_a=0A+=20*=20=20occupies=20bits=2052=20through=2063=20= (octets=206-7).=0A+=20*=0A+=20*=20var:=0A+=20*=20=20The=202=20bit=20= variant=20field=20as=20defined=20by=20Section=204.1,=20set=20to=200b10.=20= Occupies=0A+=20*=20=20bits=2064=20and=2065=20of=20octet=208.=0A+=20*=0A+=20= *=20rand_b:=0A+=20*=20=20Starting=206=20bits=20are=20least=20significant=20= 6=20bits=20of=20a=20counter.=20The=20final=2056=0A+=20*=20=20bits=20= filled=20with=20pseudo-random=20data=20to=20provide=20uniqueness=20as=20= per=0A+=20*=20=20Section=206.9.=20rand_b=20Occupies=20bits=2066=20= through=20127=20(octets=208-15).=0A+=20*=20----------=0A+=20*=20=0A+=20*=20= Monotonic=20Random=20(Method=202)=20can=20be=20implemented=20with=20= arbitrary=20size=20of=20a=0A+=20*=20counter.=20We=20choose=20size=2018=20= to=20reuse=20all=20space=20of=20bytes=20that=20are=20touched=20by=0A+=20= *=20ver=20and=20var=20fields=20+=20rand_a=20bytes=20between=20them.=0A+=20= *=20Whenever=20timestamp=20unix_ts_ms=20is=20moving=20forward,=20this=20= counter=20bits=20are=0A+=20*=20reinitialized.=20Rinilialization=20always=20= sets=20most=20significant=20bit=20to=200,=20other=0A+=20*=20bits=20are=20= initialized=20with=20random=20numbers.=20This=20gives=20as=20= approximately=20192K=0A+=20*=20UUIDs=20within=20one=20millisecond=20= without=20overflow.=20Outh=20to=20be=20enough.=0A+=20*=20Whenever=20= counter=20overflow=20happens,=20this=20overflow=20is=20translated=20to=20= increment=0A+=20*=20of=20unix_ts_ms.=20So=20generation=20of=20UUIDs=20= ate=20rate=20higher=20than=20128MHz=20might=20lead=0A+=20*=20to=20using=20= timestamps=20ahead=20of=20time.=0A+=20*=0A+=20*=20All=20UUID=20generator=20= state=20is=20backend-local.=20For=20UUIDs=20generated=20in=20one=0A+=20*=20= backend=20we=20guarantee=20monotonicity.=20UUIDs=20generated=20on=20= different=20backends=0A+=20*=20will=20be=20mostly=20monotonic=20if=20= they=20are=20generated=20at=20frequences=20less=20than=201KHz,=0A+=20*=20= but=20this=20monotonicity=20is=20not=20strictly=20guaranteed.=20UUIDs=20= generated=20on=0A+=20*=20different=20nodes=20are=20mostly=20monotonic=20= with=20regards=20to=20possible=20clock=20drift.=0A+=20*/=0A+Datum=0A= +uuidv7(PG_FUNCTION_ARGS)=0A+{=0A+=09pg_uuid_t=20=20*uuid=20=3D=20= palloc(UUID_LEN);=0A+=09uint64_t=20tms;=0A+=09struct=20timeval=20tp;=0A+=09= bool=20increment_counter;=0A+=0A+=09gettimeofday(&tp,=20NULL);=0A+=09tms=20= =3D=20((uint64_t)tp.tv_sec)=20*=201000=20+=20(tp.tv_usec)=20/=201000;=0A= +=09/*=20time=20from=20clock=20is=20protected=20from=20backward=20leaps=20= */=0A+=09increment_counter=20=3D=20(tms=20<=3D=20previous_timestamp);=0A= +=0A+=09if=20(increment_counter)=0A+=09{=0A+=09=09/*=20Time=20did=20not=20= advance=20from=20the=20previous=20generation,=20we=20must=20increment=20= counter=20*/=0A+=09=09++sequence_counter;=0A+=09=09if=20= (sequence_counter=20>=200x3ffff)=0A+=09=09{=0A+=09=09=09/*=20We=20only=20= have=2018-bit=20counter=20*/=0A+=09=09=09sequence_counter=20=3D=200;=0A+=09= =09=09previous_timestamp++;=0A+=09=09}=0A+=0A+=09=09/*=20protection=20= from=20leap=20backward=20*/=0A+=09=09tms=20=3D=20previous_timestamp;=0A+=0A= +=09=09/*=20fill=20everything=20after=20the=20timestamp=20and=20counter=20= with=20random=20bytes=20*/=0A+=09=09if=20= (!pg_strong_random(&uuid->data[8],=20UUID_LEN=20-=208))=0A+=09=09=09= ereport(ERROR,=0A+=09=09=09=09=09(errcode(ERRCODE_INTERNAL_ERROR),=0A+=09= =09=09=09=09errmsg("could=20not=20generate=20random=20values")));=0A+=0A= +=09=09/*=20most=20significant=204=20bits=20of=2018-bit=20counter=20*/=0A= +=09=09uuid->data[6]=20=3D=20(unsigned=20char)(sequence_counter=20>>=20= 14);=0A+=09=09/*=20next=208=20bits=20*/=0A+=09=09uuid->data[7]=20=3D=20= (unsigned=20char)(sequence_counter=20>>=206);=0A+=09=09/*=20least=20= significant=206=20bits=20*/=0A+=09=09uuid->data[8]=20=3D=20(unsigned=20= char)(sequence_counter);=0A+=09}=0A+=09else=0A+=09{=0A+=09=09/*=20fill=20= everything=20after=20the=20timestamp=20with=20random=20bytes=20*/=0A+=09=09= if=20(!pg_strong_random(&uuid->data[6],=20UUID_LEN=20-=206))=0A+=09=09=09= ereport(ERROR,=0A+=09=09=09=09=09(errcode(ERRCODE_INTERNAL_ERROR),=0A+=09= =09=09=09=09errmsg("could=20not=20generate=20random=20values")));=0A+=0A= +=09=09/*=0A+=09=09=20*=20Left-most=20counter=20bits=20are=20initialized=20= as=20zero=20for=20the=20sole=20purpose=0A+=09=09=20*=20of=20guarding=20= against=20counter=20rollovers.=0A+=09=09=20*=20See=20section=20= "Fixed-Length=20Dedicated=20Counter=20Seeding"=0A+=09=09=20*=20= https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis-09#mon= otonicity_counters=0A+=09=09=20*/=0A+=09=09uuid->data[6]=20=3D=20= (uuid->data[6]=20&=200xf7);=0A+=0A+=09=09/*=20read=20randomly=20= initialized=20bits=20of=20counter=20*/=0A+=09=09sequence_counter=20=3D=20= ((uint32_t)uuid->data[8]=20&=200x3f)=20+=0A+=09=09=09=09=09=09=09= (((uint32_t)uuid->data[7])=20<<=206)=20+=0A+=09=09=09=09=09=09=09= (((uint32_t)uuid->data[6]=20&=200x0f)=20<<=2014);=0A+=0A+=09=09= previous_timestamp=20=3D=20tms;=0A+=09}=0A+=0A+=09/*=20Fill=20in=20time=20= part=20*/=0A+=09uuid->data[0]=20=3D=20(unsigned=20char)(tms=20>>=2040);=0A= +=09uuid->data[1]=20=3D=20(unsigned=20char)(tms=20>>=2032);=0A+=09= uuid->data[2]=20=3D=20(unsigned=20char)(tms=20>>=2024);=0A+=09= uuid->data[3]=20=3D=20(unsigned=20char)(tms=20>>=2016);=0A+=09= uuid->data[4]=20=3D=20(unsigned=20char)(tms=20>>=208);=0A+=09= uuid->data[5]=20=3D=20(unsigned=20char)tms;=0A+=0A+=09/*=0A+=09=20*=20= Set=20magic=20numbers=20for=20a=20"version=207"=20(pseudorandom)=20UUID,=20= see=0A+=09=20*=20= https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc4122bis=0A+=09= =20*/=0A+=09/*=20set=20version=20field,=20top=20four=20bits=20are=200,=20= 1,=201,=201=20*/=0A+=09uuid->data[6]=20=3D=20(uuid->data[6]=20&=200x0f)=20= |=200x70;=0A+=09/*=20set=20variant=20field,=20top=20two=20bits=20are=20= 1,=200=20*/=0A+=09uuid->data[8]=20=3D=20(uuid->data[8]=20&=200x3f)=20|=20= 0x80;=0A+=0A+=09PG_RETURN_UUID_P(uuid);=0A+}=0A+=0A+/*=0A+=20*=20Routine=20= to=20extract=20UUID=20version=20from=20variant=200b10=0A+=20*=20Returns=20= NULL=20if=20UUID=20is=20not=200b10=20or=20version=20is=20not=201,6=20= or7.=0A+=20*/=0A+Datum=0A+uuid_extract_timestamp(PG_FUNCTION_ARGS)=0A+{=0A= +=09pg_uuid_t=20=20*uuid=20=3D=20PG_GETARG_UUID_P(0);=0A+=09TimestampTz=20= ts;=0A+=09uint64_t=20tms;=0A+=0A+=09if=20((uuid->data[8]=20&=200xc0)=20= !=3D=200x80)=0A+=09=09PG_RETURN_NULL();=0A+=0A+=09if=20((uuid->data[6]=20= &=200xf0)=20=3D=3D=200x70)=0A+=09{=0A+=09=09tms=20=3D=09=09=09=20=20= uuid->data[5];=0A+=09=09tms=20+=3D=20((uint64_t)uuid->data[4])=20<<=208;=0A= +=09=09tms=20+=3D=20((uint64_t)uuid->data[3])=20<<=2016;=0A+=09=09tms=20= +=3D=20((uint64_t)uuid->data[2])=20<<=2024;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[1])=20<<=2032;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[0])=20<<=2040;=0A+=0A+=09=09ts=20=3D=20= (TimestampTz)=20(tms=20*=201000)=20-=20/*=20convert=20ms=20to=20us,=20= than=20adjust=20*/=0A+=09=09=09(POSTGRES_EPOCH_JDATE=20-=20= UNIX_EPOCH_JDATE)=20*=20SECS_PER_DAY=20*=20USECS_PER_SEC;=0A+=0A+=09=09= PG_RETURN_TIMESTAMPTZ(ts);=0A+=09}=0A+=0A+=09if=20((uuid->data[6]=20&=20= 0xf0)=20=3D=3D=200x10)=0A+=09{=0A+=09=09tms=20=3D=20=20= ((uint64_t)uuid->data[0])=20<<=2024;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[1])=20<<=2016;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[2])=20<<=208;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[3]);=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[4])=20<<=2040;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[5])=20<<=2032;=0A+=09=09tms=20+=3D=20= (((uint64_t)uuid->data[6])&0xf)=20<<=2056;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[7])=20<<=2048;=0A+=0A+=09=09ts=20=3D=20= (TimestampTz)=20(tms=20/=2010)=20-=20/*=20convert=20100-ns=20intervals=20= to=20us,=20than=20adjust=20*/=0A+=09=09=09= ((uint64_t)POSTGRES_EPOCH_JDATE=20-=20GREGORIAN_EPOCH_JDATE)=20*=20= SECS_PER_DAY=20*=20USECS_PER_SEC;=0A+=0A+=09=09= PG_RETURN_TIMESTAMPTZ(ts);=0A+=09}=0A+=0A+=09if=20((uuid->data[6]=20&=20= 0xf0)=20=3D=3D=200x60)=0A+=09{=0A+=09=09tms=20=3D=20=20= ((uint64_t)uuid->data[0])=20<<=2052;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[1])=20<<=2044;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[2])=20<<=2036;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[3])=20<<=2028;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[4])=20<<=2020;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[5])=20<<=2012;=0A+=09=09tms=20+=3D=20= (((uint64_t)uuid->data[6])&0xf)=20<<=208;=0A+=09=09tms=20+=3D=20= ((uint64_t)uuid->data[7]);=0A+=0A+=09=09ts=20=3D=20(TimestampTz)=20(tms=20= /=2010)=20-=20/*=20convert=20100-ns=20intervals=20to=20us,=20than=20= adjust=20*/=0A+=09=09=09((uint64_t)POSTGRES_EPOCH_JDATE=20-=20= GREGORIAN_EPOCH_JDATE)=20*=20SECS_PER_DAY=20*=20USECS_PER_SEC;=0A+=0A+=09= =09PG_RETURN_TIMESTAMPTZ(ts);=0A+=09}=0A+=0A+=09PG_RETURN_NULL();=0A+}=0A= +=0A+/*=0A+=20*=20Routine=20to=20extract=20UUID=20version=20from=20= variant=200b10=0A+=20*=20Returns=20NULL=20if=20UUID=20is=20not=200b10=0A= +=20*/=0A+Datum=0A+uuid_extract_version(PG_FUNCTION_ARGS)=0A+{=0A+=09= pg_uuid_t=20=20*uuid=20=3D=20PG_GETARG_UUID_P(0);=0A+=09uint16_t=20= result;=0A+=0A+=09if=20((uuid->data[8]=20&=200xc0)=20!=3D=200x80)=0A+=09=09= PG_RETURN_NULL();=0A+=09result=20=3D=20uuid->data[6]=20>>=204;=0A+=0A+=09= PG_RETURN_UINT16(result);=0A+}=0A+=0A+/*=0A+=20*=20Routine=20to=20= extract=20UUID=20variant.=20Can=20return=20only=200,=200b10,=200b110=20= and=200b111.=0A+=20*/=0A+Datum=0A+uuid_extract_variant(PG_FUNCTION_ARGS)=0A= +{=0A+=09pg_uuid_t=20=20*uuid=20=3D=20PG_GETARG_UUID_P(0);=0A+=09= uint16_t=20result;=0A+=0A+=09/*=0A+=09=20*=20The=20contents=20of=20the=20= variant=20field,=20where=20the=20letter=20"x"=20indicates=20a=0A+=09=20*=20= "don't-care"=20value.=0A+=09=20*=20----------=0A+=09=20*=20Msb0=09=09= Msb1=09Msb2=09Msb3=09Variant=09Description=0A+=09=20*=200=09=09x=09=09x=09= =09x=09=091-7=09=09Reserved,=20NCS=20backward=0A+=09=20*=20=09=09=09=09=09= =09=09=09=09=09=09compatibility=20and=20includes=20Nil=0A+=09=20*=20=09=09= =09=09=09=09=09=09=09=09=09UUID=20as=20per=20Section=205.9.=0A+=09=20*=20= 1=09=090=09=09x=09=09x=09=098-9,A-B=09The=20variant=20specified=20in=20= RFC.=0A+=09=20*=201=09=091=09=090=09=09x=09=09C-D=09=09Reserved,=20= Microsoft=20Corporation=0A+=09=20*=20=09=09=09=09=09=09=09=09=09=09=09= backward=20compatibility.=0A+=09=20*=201=09=091=09=091=09=09x=09=09E-F=09= =09Reserved=20for=20future=20definition=0A+=09=20*=20=09=09=09=09=09=09=09= =09=09=09=09and=20includes=20Max=20UUID=20as=20per=0A+=09=20*=20=09=09=09= =09=09=09=09=09=09=09=09Section=205.10=20of=20RFC.=0A+=09=20*=20= ----------=0A+=09=20*/=0A+=0A+=09uint8_t=20nibble=20=3D=20uuid->data[8]=20= >>=204;=0A+=09if=20(nibble=20<=208)=0A+=09=09result=20=3D=200;=0A+=09= else=20if=20(nibble=20<=200xC)=0A+=09=09result=20=3D=200b10;=0A+=09else=20= if=20(nibble=20<=200xE)=0A+=09=09result=20=3D=200b110;=0A+=09else=0A+=09=09= result=20=3D=200b111;=0A+=0A+=09PG_RETURN_UINT16(result);=0A+}=0Adiff=20= --git=20a/src/include/catalog/pg_proc.dat=20= b/src/include/catalog/pg_proc.dat=0Aindex=20e4115cd084..eb1d075595=20= 100644=0A---=20a/src/include/catalog/pg_proc.dat=0A+++=20= b/src/include/catalog/pg_proc.dat=0A@@=20-9174,6=20+9174,21=20@@=0A=20{=20= oid=20=3D>=20'3432',=20descr=20=3D>=20'generate=20random=20UUID',=0A=20=20= =20proname=20=3D>=20'gen_random_uuid',=20proleakproof=20=3D>=20't',=20= provolatile=20=3D>=20'v',=0A=20=20=20prorettype=20=3D>=20'uuid',=20= proargtypes=20=3D>=20'',=20prosrc=20=3D>=20'gen_random_uuid'=20},=0A+{=20= oid=20=3D>=20'9895',=20descr=20=3D>=20'generate=20UUID=20version=204',=0A= +=20=20proname=20=3D>=20'uuidv4',=20proleakproof=20=3D>=20't',=20= provolatile=20=3D>=20'v',=0A+=20=20prorettype=20=3D>=20'uuid',=20= proargtypes=20=3D>=20'',=20prosrc=20=3D>=20'gen_random_uuid'=20},=0A+{=20= oid=20=3D>=20'9896',=20descr=20=3D>=20'generate=20UUID=20version=207',=0A= +=20=20proname=20=3D>=20'uuidv7',=20proleakproof=20=3D>=20't',=20= provolatile=20=3D>=20'v',=0A+=20=20prorettype=20=3D>=20'uuid',=20= proargtypes=20=3D>=20'',=20prosrc=20=3D>=20'uuidv7'=20},=0A+{=20oid=20=3D>= =20'9897',=20descr=20=3D>=20'extract=20timestamp=20from=20UUID=20version=20= 1,=206=20or=207',=0A+=20=20proname=20=3D>=20'uuid_extract_timestamp',=20= proleakproof=20=3D>=20't',=0A+=20=20prorettype=20=3D>=20'timestamptz',=20= proargtypes=20=3D>=20'uuid',=20prosrc=20=3D>=20'uuid_extract_timestamp'=20= },=0A+{=20oid=20=3D>=20'9898',=20descr=20=3D>=20'extract=20version=20= from=20RFC=204122=20UUID',=0A+=20=20proname=20=3D>=20= 'uuid_extract_version',=20proleakproof=20=3D>=20't',=0A+=20=20prorettype=20= =3D>=20'int2',=20proargtypes=20=3D>=20'uuid',=20prosrc=20=3D>=20= 'uuid_extract_version'=20},=0A+{=20oid=20=3D>=20'9899',=20descr=20=3D>=20= 'extract=20variant=20from=20UUID',=0A+=20=20proname=20=3D>=20= 'uuid_extract_variant',=20proleakproof=20=3D>=20't',=0A+=20=20prorettype=20= =3D>=20'int2',=20proargtypes=20=3D>=20'uuid',=20prosrc=20=3D>=20= 'uuid_extract_variant'=20},=0A=20=0A=20#=20pg_lsn=0A=20{=20oid=20=3D>=20= '3229',=20descr=20=3D>=20'I/O',=0Adiff=20--git=20= a/src/include/datatype/timestamp.h=20b/src/include/datatype/timestamp.h=0A= index=203a37cb661e..652aeb428e=20100644=0A---=20= a/src/include/datatype/timestamp.h=0A+++=20= b/src/include/datatype/timestamp.h=0A@@=20-230,9=20+230,10=20@@=20struct=20= pg_itm_in=0A=20=09=20((y)=20<=20JULIAN_MAXYEAR=20||=20\=0A=20=09=20=20= ((y)=20=3D=3D=20JULIAN_MAXYEAR=20&&=20((m)=20<=20JULIAN_MAXMONTH))))=0A=20= =0A-/*=20Julian-date=20equivalents=20of=20Day=200=20in=20Unix=20and=20= Postgres=20reckoning=20*/=0A+/*=20Julian-date=20equivalents=20of=20Day=20= 0=20in=20Unix,=20Postgres=20and=20Gregorian=20epochs=20*/=0A=20#define=20= UNIX_EPOCH_JDATE=09=092440588=20/*=20=3D=3D=20date2j(1970,=201,=201)=20= */=0A=20#define=20POSTGRES_EPOCH_JDATE=092451545=20/*=20=3D=3D=20= date2j(2000,=201,=201)=20*/=0A+#define=20GREGORIAN_EPOCH_JDATE=092299161=20= /*=20=3D=3D=20date2j(1582,10,15)=20*/=0A=20=0A=20/*=0A=20=20*=20Range=20= limits=20for=20dates=20and=20timestamps.=0Adiff=20--git=20= a/src/test/regress/expected/opr_sanity.out=20= b/src/test/regress/expected/opr_sanity.out=0Aindex=20= 7610b011d6..44542c56ef=20100644=0A---=20= a/src/test/regress/expected/opr_sanity.out=0A+++=20= b/src/test/regress/expected/opr_sanity.out=0A@@=20-872,6=20+872,11=20@@=20= xid8ge(xid8,xid8)=0A=20xid8eq(xid8,xid8)=0A=20xid8ne(xid8,xid8)=0A=20= xid8cmp(xid8,xid8)=0A+uuidv4()=0A+uuidv7()=0A= +uuid_extract_timestamp(uuid)=0A+uuid_extract_version(uuid)=0A= +uuid_extract_variant(uuid)=0A=20--=20restore=20normal=20output=20mode=0A= =20\a\t=0A=20--=20List=20of=20functions=20used=20by=20libpq's=20= fe-lobj.c=0Adiff=20--git=20a/src/test/regress/expected/uuid.out=20= b/src/test/regress/expected/uuid.out=0Aindex=208e7f21910d..2930bcc7f0=20= 100644=0A---=20a/src/test/regress/expected/uuid.out=0A+++=20= b/src/test/regress/expected/uuid.out=0A@@=20-168,5=20+168,76=20@@=20= SELECT=20count(DISTINCT=20guid_field)=20FROM=20guid1;=0A=20=20=20=20=20=20= 2=0A=20(1=20row)=0A=20=0A+--=20test=20of=20uuidv4()=20alias=0A+TRUNCATE=20= guid1;=0A+INSERT=20INTO=20guid1=20(guid_field)=20VALUES=20(uuidv4());=0A= +INSERT=20INTO=20guid1=20(guid_field)=20VALUES=20(uuidv4());=0A+SELECT=20= count(DISTINCT=20guid_field)=20FROM=20guid1;=0A+=20count=20=0A+-------=0A= +=20=20=20=20=202=0A+(1=20row)=0A+=0A+--=20generation=20test=20for=20v7=0A= +TRUNCATE=20guid1;=0A+INSERT=20INTO=20guid1=20(guid_field)=20VALUES=20= (uuidv7());=0A+INSERT=20INTO=20guid1=20(guid_field)=20VALUES=20= (uuidv7());=0A+SELECT=20count(DISTINCT=20guid_field)=20FROM=20guid1;=0A+=20= count=20=0A+-------=0A+=20=20=20=20=202=0A+(1=20row)=0A+=0A+--=20support=20= functions=20for=20UUID=20versions=20and=20variants=0A+SELECT=20= uuid_extract_version(uuidv7());=0A+=20uuid_extract_version=20=0A= +----------------------=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=207=0A+(1=20row)=0A+=0A+SELECT=20= uuid_extract_version('11111111-1111-1111-1111-111111111111')=20IS=20= NULL;=0A+=20?column?=20=0A+----------=0A+=20t=0A+(1=20row)=0A+=0A+SELECT=20= uuid_extract_version('11111111-1111-5111-8111-111111111111');=0A+=20= uuid_extract_version=20=0A+----------------------=0A+=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=205=0A+(1=20row)=0A+=0A+SELECT=20= uuid_extract_variant(uuidv7());=0A+=20uuid_extract_variant=20=0A= +----------------------=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=202=0A+(1=20row)=0A+=0A+--=20uuid_extract_timestamp()=20must=20= refuse=20to=20accept=20non-UUIDv7=0A+SELECT=20= uuid_extract_timestamp(gen_random_uuid());=0A+=20uuid_extract_timestamp=20= =0A+------------------------=0A+=20=0A+(1=20row)=0A+=0A+--=20extract=20= UUID=20v1,=20v6=20and=20v7=20timestamp=0A+SELECT=20= uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846')=20=3D=20= 'Tuesday,=20February=2022,=202022=202:22:22.00=20PM=20GMT+05:00';=0A+=20= ?column?=20=0A+----------=0A+=20t=0A+(1=20row)=0A+=0A+SELECT=20= uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846')=20=3D=20= 'Tuesday,=20February=2022,=202022=202:22:22.00=20PM=20GMT+05:00';=0A+=20= ?column?=20=0A+----------=0A+=20t=0A+(1=20row)=0A+=0A+SELECT=20= uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F')=20=3D=20= 'Tuesday,=20February=2022,=202022=202:22:22.00=20PM=20GMT+05:00';=0A+=20= ?column?=20=0A+----------=0A+=20t=0A+(1=20row)=0A+=0A=20--=20clean=20up=0A= =20DROP=20TABLE=20guid1,=20guid2=20CASCADE;=0Adiff=20--git=20= a/src/test/regress/sql/uuid.sql=20b/src/test/regress/sql/uuid.sql=0A= index=209a8f437c7d..0f9bd1a661=20100644=0A---=20= a/src/test/regress/sql/uuid.sql=0A+++=20b/src/test/regress/sql/uuid.sql=0A= @@=20-85,5=20+85,31=20@@=20INSERT=20INTO=20guid1=20(guid_field)=20VALUES=20= (gen_random_uuid());=0A=20INSERT=20INTO=20guid1=20(guid_field)=20VALUES=20= (gen_random_uuid());=0A=20SELECT=20count(DISTINCT=20guid_field)=20FROM=20= guid1;=0A=20=0A+--=20test=20of=20uuidv4()=20alias=0A+TRUNCATE=20guid1;=0A= +INSERT=20INTO=20guid1=20(guid_field)=20VALUES=20(uuidv4());=0A+INSERT=20= INTO=20guid1=20(guid_field)=20VALUES=20(uuidv4());=0A+SELECT=20= count(DISTINCT=20guid_field)=20FROM=20guid1;=0A+=0A+--=20generation=20= test=20for=20v7=0A+TRUNCATE=20guid1;=0A+INSERT=20INTO=20guid1=20= (guid_field)=20VALUES=20(uuidv7());=0A+INSERT=20INTO=20guid1=20= (guid_field)=20VALUES=20(uuidv7());=0A+SELECT=20count(DISTINCT=20= guid_field)=20FROM=20guid1;=0A+=0A+--=20support=20functions=20for=20UUID=20= versions=20and=20variants=0A+SELECT=20uuid_extract_version(uuidv7());=0A= +SELECT=20uuid_extract_version('11111111-1111-1111-1111-111111111111')=20= IS=20NULL;=0A+SELECT=20= uuid_extract_version('11111111-1111-5111-8111-111111111111');=0A+SELECT=20= uuid_extract_variant(uuidv7());=0A+=0A+--=20uuid_extract_timestamp()=20= must=20refuse=20to=20accept=20non-UUIDv7=0A+SELECT=20= uuid_extract_timestamp(gen_random_uuid());=0A+=0A+--=20extract=20UUID=20= v1,=20v6=20and=20v7=20timestamp=0A+SELECT=20= uuid_extract_timestamp('C232AB00-9414-11EC-B3C8-9F6BDECED846')=20=3D=20= 'Tuesday,=20February=2022,=202022=202:22:22.00=20PM=20GMT+05:00';=0A= +SELECT=20uuid_extract_timestamp('1EC9414C-232A-6B00-B3C8-9F6BDECED846')=20= =3D=20'Tuesday,=20February=2022,=202022=202:22:22.00=20PM=20GMT+05:00';=0A= +SELECT=20uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F')=20= =3D=20'Tuesday,=20February=2022,=202022=202:22:22.00=20PM=20GMT+05:00';=0A= +=0A=20--=20clean=20up=0A=20DROP=20TABLE=20guid1,=20guid2=20CASCADE;=0A= --=20=0A2.37.1=20(Apple=20Git-137.1)=0A=0A= --Apple-Mail=_0EE87801-357B-4130-BC14-339C05A5E643--