MIME-Version: 1.0
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>
 <CAD21AoCKvYCccndY4CRcwk1bOuWxJionOidEtKQWoJTqS7wL1g@mail.gmail.com>
 <1154454839.957923.1761604611424@mail.yahoo.com>
 <CAGECzQTb9pg2Qw0XmOEKaJivLJ8kdGq3Cq38OqSgwiFVD=9f8A@mail.gmail.com>
 <87ldkv8cog.fsf@wibble.ilmari.org> <87ikfz7zcu.fsf@wibble.ilmari.org>
 <CAD21AoCf-0LdE=Js3ViXmKfSATJOjSNym1+80O+=-Y3X6LDMyA@mail.gmail.com>
 <9CB824CC-70DC-4165-AC6A-9664F47209EE@yandex-team.ru>
In-Reply-To: <9CB824CC-70DC-4165-AC6A-9664F47209EE@yandex-team.ru>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Thu, 30 Oct 2025 12:10:42 -0700
Message-ID: 
 <CAD21AoAd8smQkJWWoghBZUAQT5xk=C6mXoN99sOWo4wEdg+uLQ@mail.gmail.com>
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in
 functions
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: =?UTF-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org>,
	Jelte Fennema-Nio <postgres@jeltef.nl>,
 Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoAd8smQkJWWoghBZUAQT5xk%3DC6mXoN99sOWo4wEdg%2BuLQ%40mail.gmail.com>
Precedence: bulk

On Wed, Oct 29, 2025 at 5:19=E2=80=AFAM Andrey Borodin <x4mmm@yandex-team.r=
u> wrote:
>
>
>
> > On 28 Oct 2025, at 22:44, Masahiko Sawada <sawada.mshk@gmail.com> wrote=
:
> >
> > Andrey has shared his patch for base32hex support before[1]. While it
> > needs to be updated, it seems to implement sufficient function.
>
> I'd propose something like attached patch. It's on top of Ilmari's v2 pat=
ch with small suggestions as a step 2.
>

I've reviewed the v3 patches, and here are some review comments.

v3-0001 and v3-0002:

-                               errcode(ERRCODE_INVALID_BINARY_REPRESENTATI=
ON),
-                               errmsg("invalid uuid length"));
+                               (errcode(ERRCODE_INVALID_BINARY_REPRESENTAT=
ION),
+                                errmsg("invalid length for UUID"),
+                                errdetail("Expected %d bytes, got
%d.", UUID_LEN, len)));

How about the error message like "invalid input length for type uuid"?
I think "uuid" should be lower case as it indicates PostgreSQL uuid
data type, and it's better to use %s format instead of directly
writing "uuid" (see string_to_uuid() for example).

As for the errdetail message, should we add "bytea" also after "got %d"?

---
+-- casts
+SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
+SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
+SELECT '\x1234567890abcdef'::bytea::uuid; -- error

We already have tests for casting bytes to integer data types in
strings.sql. I suggest moving the casting tests from bytea to uuid
into therel. For the uuid.sql file, we could add a test to verify that
a UUID value remains unchanged when it's cast to bytea and back to
UUID. For example,

SELECT v =3D v::bytea::uuid as matched FROM gen_random_uuid() v;

---
I think we should update the documentation in the uuid section about
casting data between bytea and uuid. For references, we have a similar
description for bytea and integer[1].

v3-0003:

base32hex_encode() doesn't seem to add '=3D' paddings, but is it
intentional? I don't see any description in RFC 4648 that we can omit
'=3D' paddings.

---
I think the patch should add tests not only for uuid data type but
also for general cases like other encodings.

---
In uuid.sql tests, how about adding some tests to check if base32hex
maintains the sortability of UUIDv7 data?

---
I would suggest registering the patches to the next commit fest if not yet.

Regards,

[1] https://www.postgresql.org/docs/devel/functions-binarystring.html

--=20
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com