Date: Mon, 27 Oct 2025 14:03:09 +0000 (UTC)
From: Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>
To: pgsql-hackers <pgsql-hackers@postgresql.org>,
	Aleksander Alekseev <aleksander@tigerdata.com>
Cc: Andrey Borodin <x4mmm@yandex-team.ru>,
	Masahiko Sawada <sawada.mshk@gmail.com>
Message-ID: <1228470163.787783.1761573789233@mail.yahoo.com>
In-Reply-To: 
 <CAJ7c6TOqZxNoXVEY31BZqfAxKoE37Y4Ruq_+830z_yYRJiCahA@mail.gmail.com>
References: <1791665551.452444.1761209220211.ref@mail.yahoo.com>
 <1791665551.452444.1761209220211@mail.yahoo.com>
 <CAJ7c6TOramr1UTLcyB128LWMqita1Y7=arq3KHaU=qikf5yKOQ@mail.gmail.com>
 <18022523-0F8F-4C07-AFF5-57DC9086D78E@yandex-team.ru>
 <1895971769.8343.1761240853939@mail.yahoo.com>
 <CAD21AoCxy_3VW2_z5Rxc-tFsuqeyGsA-F_kD-tx6XXBC56nTCg@mail.gmail.com>
 <574624399.175025.1761290201491@mail.yahoo.com>
 <CAD21AoAXQcZ2mMkxX6NPdFpdC-D3AhE--qyH9Se3XTrDX6x-bg@mail.gmail.com>
 <953203149.383019.1761345585325@mail.yahoo.com>
 <CAD21AoCzEDdwpyPwA0d-QmCRe5rMz3m160SJgxMwKke85e8n0w@mail.gmail.com>
 <6F76FA61-E2DC-44EF-9504-889D9BDB4EBD@yandex-team.ru>
 <CAJ7c6TOqZxNoXVEY31BZqfAxKoE37Y4Ruq_+830z_yYRJiCahA@mail.gmail.com>
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in
 functions
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_787782_415956227.1761573789232"
Content-Length: 6381
Archived-At: 
 <https://www.postgresql.org/message-id/1228470163.787783.1761573789233%40mail.yahoo.com>
Precedence: bulk

------=_Part_787782_415956227.1761573789232
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi,
> > Or providing
> > 'uuid_encode(uuid, format text) -> text' and 'uuid_decode(text, format
> > text) -> uuid' might make sense too, but I'm not sure.
>
> I like the idea, so I drafted a prototype for discussion.
> Though I do not see what else methods should be provided along with added=
 one...

I see no reason why we should forbid the use of base32 encoding with
bytea. Or have different functions for this e.g. uuid_encode() and
encode(). To me it looks like a poor API design.

--=20
Best regards,
Aleksander Alekseev

____________________________________________

It seems that bytea is your personal interest, since you continue to impose=
 your bytea when a better solution has already been found with uuid_encode(=
) and uuid_decode().


The bytea proposal has a lot of drawbacks:

1. It requires unnecessary casting in addition to encoding/decoding. This c=
omplicates the interface and creates unnecessary cognitive load on develope=
rs. It also creates additional CPU load, although perhaps only a small amou=
nt.

2. The encoding function encourages developers to use the slightly more com=
pact base64 encoding (see=C2=A0https://www.postgresql.org/docs/current/func=
tions-binarystring.html), which doesn't preserve sort order, isn't URL-safe=
, is case-sensitive, and requires specifying the case of letters when dicta=
ting. This also creates a serious problem of incompatibility between UUID e=
ncodings.

3. The hashing functions used by bytea create a temptation to implement pop=
ular, idiotic ideas for hashing UUIDs to obscure their creation date and to=
 hide internal keys from clients.

4. Other various functions for bytea allow the construction of Frankenstein=
=C2=A0identifiers that compete with UUIDv7, which could negatively impact t=
he reputation of UUIDs.

The bytea type has nothing in common with the uuid type other than the bina=
ry encoding. Therefore, the bytea <-> uuid cast can only encourage abuse an=
d errors, creating the illusion of unlimited developer power.


The bytea proposal has no merit whatsoever. It's the worst, most insafe, an=
d most harmful design, undermining efforts to widely adopt UUIDv7 and impro=
ve PostgreSQL.


 =20
------=_Part_787782_415956227.1761573789232
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><div class=3D"ydpa6d6a63fyahoo-style-wrap" style=
=3D"font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px=
;"><div><div><span style=3D"color: rgb(38, 40, 42); font-size: 13px;">Hi,</=
span></div></div></div><div id=3D"ydp8608592eyahoo_quoted_2308772491" class=
=3D"ydp8608592eyahoo_quoted"><div style=3D"font-family:'Helvetica Neue', He=
lvetica, Arial, sans-serif;font-size:13px;color:#26282a;"><div><div dir=3D"=
ltr"><br clear=3D"none">&gt; &gt; Or providing<br clear=3D"none">&gt; &gt; =
'uuid_encode(uuid, format text) -&gt; text' and 'uuid_decode(text, format<b=
r clear=3D"none">&gt; &gt; text) -&gt; uuid' might make sense too, but I'm =
not sure.<br clear=3D"none">&gt;<br clear=3D"none">&gt; I like the idea, so=
 I drafted a prototype for discussion.<br clear=3D"none">&gt; Though I do n=
ot see what else methods should be provided along with added one...<br clea=
r=3D"none"><br clear=3D"none">I see no reason why we should forbid the use =
of base32 encoding with<br clear=3D"none">bytea. Or have different function=
s for this e.g. uuid_encode() and<br clear=3D"none">encode(). To me it look=
s like a poor API design.<div class=3D"ydp8608592eyqt4643250381" id=3D"ydp8=
608592eyqtfd37919"><br clear=3D"none"><br clear=3D"none">-- <br clear=3D"no=
ne">Best regards,</div><br clear=3D"none">Aleksander Alekseev<br><br>______=
______________________________________</div><div dir=3D"ltr"><br></div><div=
 dir=3D"ltr" data-setdir=3D"false"><span><br>It seems that bytea is your pe=
rsonal interest, since you continue to impose your bytea when a better solu=
tion has already been found with uuid_encode() and uuid_decode().<br></span=
><br><br><div><div>The bytea proposal has a lot of drawbacks:</div><div><br=
></div><div><br></div><div>1. It requires unnecessary casting in addition t=
o encoding/decoding. This complicates the interface and creates unnecessary=
 cognitive load on developers. It also creates additional CPU load, althoug=
h perhaps only a small amount.</div><div><br></div><div><br></div><div dir=
=3D"ltr" data-setdir=3D"false">2. The encoding function encourages develope=
rs to use the slightly more compact base64 encoding (see&nbsp;<a href=3D"ht=
tps://www.postgresql.org/docs/current/functions-binarystring.html" rel=3D"n=
ofollow" target=3D"_blank">https://www.postgresql.org/docs/current/function=
s-binarystring.html</a>), which doesn't preserve sort order, isn't URL-safe=
, is case-sensitive, and requires specifying the case of letters when dicta=
ting. This also creates a serious problem of incompatibility between UUID e=
ncodings.</div><div><br></div><div><br></div><div dir=3D"ltr" data-setdir=
=3D"false">3. <span>The hashing functions used by bytea create a temptation=
 to implement popular, idiotic ideas for hashing UUIDs to obscure their cre=
ation date and to hide internal keys from clients.</span></div><div dir=3D"=
ltr" data-setdir=3D"false"><span><br></span></div><div dir=3D"ltr" data-set=
dir=3D"false"><span><br></span></div><div dir=3D"ltr" data-setdir=3D"false"=
>4. Other various functions for bytea allow the construction of <span>Frank=
enstein&nbsp;</span>identifiers that compete with UUIDv7, which could negat=
ively impact the reputation of UUIDs.</div></div><br><br><span>The bytea ty=
pe has nothing in common with the uuid type other than the binary encoding.=
 Therefore, the bytea &lt;-&gt; uuid cast can only encourage abuse and erro=
rs, creating the illusion of unlimited developer power.<br></span><br><br><=
span>The bytea proposal has no merit whatsoever. It's the worst, most insaf=
e, and most harmful design, undermining efforts to widely adopt UUIDv7 and =
improve PostgreSQL.</span><br><br><br><div class=3D"ydp8608592eyqt464325038=
1" id=3D"ydp8608592eyqtfd37149"><br clear=3D"none"></div></div></div>
            </div>
        </div></body></html>
------=_Part_787782_415956227.1761573789232--