MIME-Version: 1.0
References: <CAFCRh-8FOZiycyfX4uPB8MTHQTxqNVuW0pdKBuFNQneEZy1PwQ@mail.gmail.com>
 <7f90e1f3-7e0b-4b87-8cb6-f2862755fd3c@aklaver.com> <CANzqJaAWF7hr0OjMHBDpsO6O-AtPLu1RwopBEVYqgBT=Rmwrbg@mail.gmail.com>
In-Reply-To: <CANzqJaAWF7hr0OjMHBDpsO6O-AtPLu1RwopBEVYqgBT=Rmwrbg@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Fri, 11 Jul 2025 11:00:49 +0200
Message-ID: <CAFCRh-8chpiFs1oOGnOS=wTRd9y0t4cojv2iMwr0Zgo+j1RYTg@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-8chpiFs1oOGnOS%3DwTRd9y0t4cojv2iMwr0Zgo%2Bj1RYTg%40mail.gmail.com>
Precedence: bulk

On Thu, Jul 10, 2025 at 7:11=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.co=
m> wrote:
> On Thu, Jul 10, 2025 at 12:26=E2=80=AFPM Adrian Klaver <adrian.klaver@akl=
aver.com> wrote:
>> On 7/10/25 04:48, Dominique Devienne wrote:
>> > Seems so logical to me, that these hashing functions were available
>> > are aggregates, I can't be the first one to think of that, can it?
>>
>> I've been on this list since late 2002 and I don't recall this ever
>> being brought up. Now it is entirely possible that age has dimmed my
>> recall abilities:) Though a quick search seems to confirm my memory.
>
> What even is an aggregate hash function?  (I can imagine a few possibilit=
ies, but don't want to assume.)

Well, it's so obvious to me, I wonder if you're baiting me :)

Any hasher/digest inits some internal state, processes bytes,
typically in "streaming-fashion" via successive byte spans (equivalent
to PostgreSQL's bytea), and yields a digest of various length at the
end. 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?). As an aggregate, the processing is extended to all values
aggregated. That's it. Obviously order-sensitive, so an explicit ORDER
BY inside the aggregate call is DE RIGEUR, but that's normal. As I
mentioned already, SQLite supports sha3_agg() for almost a year, and
had sha(1|3)_query, which is conceptually similar (although hashes
value types too, since multi-column and dynamically typed), for years
(8+ for sha3, probably decades for sha1).

Basically anyone who knows hashing/digests and has ever written an
aggregate UDF (in SQLite or elsewhere), understands what I'm talking
about. --DD