Feedback-ID: i1ff147bf:Fastmail
From: =?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org>
To: Jelte Fennema-Nio <postgres@jeltef.nl>
Cc: Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>,  Andrey Borodin
 <x4mmm@yandex-team.ru>,  Masahiko Sawada <sawada.mshk@gmail.com>,
  pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in
 functions
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>
Date: Tue, 28 Oct 2025 11:53:51 +0000
In-Reply-To: 
 <CAGECzQTb9pg2Qw0XmOEKaJivLJ8kdGq3Cq38OqSgwiFVD=9f8A@mail.gmail.com>
	(Jelte Fennema-Nio's message of "Tue, 28 Oct 2025 10:30:04 +0100")
Message-ID: <87ldkv8cog.fsf@wibble.ilmari.org>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: 
 <https://www.postgresql.org/message-id/87ldkv8cog.fsf%40wibble.ilmari.org>
Precedence: bulk

Jelte Fennema-Nio <postgres@jeltef.nl> writes:

> First of all, I'm definitely a proponent of being able to encode UUIDs
> using base32hex in Postgres.
>
> On Mon, 27 Oct 2025 at 23:37, Sergey Prokhorenko
> <sergeyprokhorenko@yahoo.com.au> wrote:
>>
>> Regarding the proposal to couple UUID encoding with the bytea type
>> through encode()/decode() functions: I understand the appeal of
>> reusing existing infrastructure, but this creates a conceptual
>> mismatch. UUID is a distinct semantic type in PostgreSQL, not merely
>> binary data. The bytea type has existed for decades without base32hex
>> encoding, and that's worked fine, because bytea represents arbitrary
>> binary data, not universally unique identifiers with specific
>> structural properties and needs.
>
> I think by far the first step is to make the encoding of UUIDs in
> different formats possible in Postgres. The way to do so with the
> least API impact (and thus as you noticed, least pushback), would be
> to add base32hex to the list of encoding formats in the encode/decode
> functions. Then combining that with UUID <-> bytea casting (which also
> seems totally reasonable functionality to me), would give you the
> functionality (but not the defaults you want).

+1 for adding casts.

> In a follow up patch I would personally be fine making the API to
> encode UUIDs a bit more friendly. In particular, adding an overload to
> the encode function that takes a UUID instead of a bytea seems
> reasonable to me, i.e. encode(id uuid, format text) -> text

Yeah, this works for encode, but not decode, since the argment types are
the same (text, text) in both cases.

> I'm currently less convinced about a decode_uuid function though. I
> think some perf argument (including some benchmarks) would need to be
> made to convince me of its usefulness. Because purely from an API
> friendliness lens, I feel like decode('...', 'base32hex)::uuid and
> decode_uuid('...', 'base32hex') rank basically the same.

Agreed.

> Once/if an accepted RFC actually defines a default shorter encoding
> for UUIDs we could I would definitely be in favor of adding a
> decode_uuid function with the default encoding configured as a default
> argument. As well as adding the default argument to the uuid encode
> overload function.

If it's just one new form, do we need a separate decode function?  Could
we not just make uuid_in() accept both forms (they're easily
distinguishable by length), like bytea_in accepts both the old escape
format and the new hex format?

And if the new format becomes the standard and want to change the
default output format, we would need a GUC like bytea_output anyway, to
let users control when to make the change.

- ilmari