MIME-Version: 1.0
References: <CAFCRh-8FOZiycyfX4uPB8MTHQTxqNVuW0pdKBuFNQneEZy1PwQ@mail.gmail.com>
 <7f90e1f3-7e0b-4b87-8cb6-f2862755fd3c@aklaver.com> <CANzqJaAWF7hr0OjMHBDpsO6O-AtPLu1RwopBEVYqgBT=Rmwrbg@mail.gmail.com>
 <CAFCRh-8chpiFs1oOGnOS=wTRd9y0t4cojv2iMwr0Zgo+j1RYTg@mail.gmail.com>
In-Reply-To: <CAFCRh-8chpiFs1oOGnOS=wTRd9y0t4cojv2iMwr0Zgo+j1RYTg@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Fri, 11 Jul 2025 14:11:44 +0200
Message-ID: <CAFCRh-9dMQC99F22VreuOF9sv7kNjqVzXvaHZQerk0aBHUyhTA@mail.gmail.com>
Subject: Re: Aggregate versions of hashing functions (md5, sha1, etc...)
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-9dMQC99F22VreuOF9sv7kNjqVzXvaHZQerk0aBHUyhTA%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 11, 2025 at 11:00=E2=80=AFAM Dominique Devienne <ddevienne@gmai=
l.com> wrote:
> The current md5() and pgcrypto.digest() functions roll the x1
> init, xN process, and x1 finish into a single call, processing a
> single bytea (or perhaps more intelligently for TOAST'ed values, the
> 2K "rows" of those in streaming-fashion, hopefully. Can a dev confirm?)

FWIW, I've [asked ChatGPT about that][1], and assuming it's right (md5
and pgcrypto.digest not leveraging the "substring-optimization" on
TOASTED bytea), that's an unfortunate lost opportunity, especially for
byteas reaching close to the 1GB limit. And again (sorry to lay it on
thick...), when required to manually chunk for sizes > 1GB, the lack
of aggregate is a bit crippling, I'm afraid.

So again, can a dev confirm what ChatGPT blurted out?

And if true, any interest in improving that for better TOAST support
for true streaming hashing for current scalar digests?

And of course, the main point of this thread, add (true streaming)
aggregate support in a future version?

Thanks, --DD

[1]: https://chatgpt.com/share/6870fe03-416c-800e-8633-a76e478a794a