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: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 11 Jul 2025 07:46:45 -0400
Message-ID: <CANzqJaD9kq9E37oqv+NZ3JcGweOg7Eij2OCsoberEoNSdyAoxQ@mail.gmail.com>
Subject: Re: Aggregate versions of hashing functions (md5, sha1, etc...)
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ecf7140639a5dde8"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD9kq9E37oqv%2BNZ3JcGweOg7Eij2OCsoberEoNSdyAoxQ%40mail.gmail.com>
Precedence: bulk

--000000000000ecf7140639a5dde8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Jul 11, 2025 at 5:01=E2=80=AFAM Dominique Devienne <ddevienne@gmail=
.com>
wrote:

> On Thu, Jul 10, 2025 at 7:11=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.=
com>
> wrote:
> > On Thu, Jul 10, 2025 at 12:26=E2=80=AFPM Adrian Klaver <
> adrian.klaver@aklaver.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
> possibilities, 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.


So it "appends" all the fields into one (virtual) mega-structure and takes
the hash on that.

It's what I expected but wanted to verify.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000ecf7140639a5dde8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jul 11, 2025 at 5:01=E2=80=AFAM D=
ominique Devienne &lt;<a href=3D"mailto:ddevienne@gmail.com">ddevienne@gmai=
l.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container">=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, Jul 10, 2025 at 7=
:11=E2=80=AFPM Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com" t=
arget=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; wrote:<br>
&gt; On Thu, Jul 10, 2025 at 12:26=E2=80=AFPM Adrian Klaver &lt;<a href=3D"=
mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klaver@aklaver.c=
om</a>&gt; wrote:<br>
&gt;&gt; On 7/10/25 04:48, Dominique Devienne wrote:<br>
&gt;&gt; &gt; Seems so logical to me, that these hashing functions were ava=
ilable<br>
&gt;&gt; &gt; are aggregates, I can&#39;t be the first one to think of that=
, can it?<br>
&gt;&gt;<br>
&gt;&gt; I&#39;ve been on this list since late 2002 and I don&#39;t recall =
this ever<br>
&gt;&gt; being brought up. Now it is entirely possible that age has dimmed =
my<br>
&gt;&gt; recall abilities:) Though a quick search seems to confirm my memor=
y.<br>
&gt;<br>
&gt; What even is an aggregate hash function?=C2=A0 (I can imagine a few po=
ssibilities, but don&#39;t want to assume.)<br>
<br>
Well, it&#39;s so obvious to me, I wonder if you&#39;re baiting me :)<br>
<br>
Any hasher/digest inits some internal state, processes bytes,<br>
typically in &quot;streaming-fashion&quot; via successive byte spans (equiv=
alent<br>
to PostgreSQL&#39;s bytea), and yields a digest of various length at the<br=
>
end. The current md5() and pgcrypto.digest() functions roll the x1<br>
init, xN process, and x1 finish into a single call, processing a<br>
single bytea (or perhaps more intelligently for TOAST&#39;ed values, the<br=
>
2K &quot;rows&quot; of those in streaming-fashion, hopefully. Can a dev<br>
confirm?). As an aggregate, the processing is extended to all values<br>
aggregated.</blockquote><div><br></div><div>So it &quot;appends&quot; all t=
he fields into one (virtual) mega-structure and takes the hash on that.</di=
v><div><br></div><div>It&#39;s what I expected but wanted to verify.</div><=
/div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><d=
iv dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Reda=
cted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br>=
<div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000ecf7140639a5dde8--