MIME-Version: 1.0
References: <CAFCRh-8FOZiycyfX4uPB8MTHQTxqNVuW0pdKBuFNQneEZy1PwQ@mail.gmail.com>
In-Reply-To: <CAFCRh-8FOZiycyfX4uPB8MTHQTxqNVuW0pdKBuFNQneEZy1PwQ@mail.gmail.com>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Thu, 10 Jul 2025 13:02:59 -0600
Message-ID: <CAHyXU0ztigAau2x5zut6tdjNVXENEGR00F-Mvx4X-20zFz-NnQ@mail.gmail.com>
Subject: Re: Aggregate versions of hashing functions (md5, sha1, etc...)
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000054ebaa063997d883"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0ztigAau2x5zut6tdjNVXENEGR00F-Mvx4X-20zFz-NnQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jul 10, 2025 at 5:48=E2=80=AFAM Dominique Devienne <ddevienne@gmail=
.com>
wrote:

> We store scientific information in PostgreSQL, and some of that is
> bytea and large, thus we must "chunk it" both for performance, and not
> be limited to 1GB (we do exceed that, in rare occasions).
>


> Recently I added md5/sha1 hashing support for such values (for various
> reasons, to track corruptions in our ETLs, now fixed, but also in the
> future for custom smart sync and such), and was surprised to discover
> there are no aggregate versions of those functions, neither the
> built-in md5 one (now with bytea overload), nor for pgcrypto. Did I
> miss something?
>
> Any chance this might be added in the future?
>
> 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?
>
> Thanks, --DD
>
> PS: The holly-grail IMHO, would be built-in support for hashing, with
> intelligent lazy-compute and persistence correctly reset on changes.
> Probably opt-in.
>
> PPS: Built-in Support for xxh64, or at least in official pgcrypto,
> would also be nice. With aggregate!
>
> PPPS: I remember Oracle LOBs supporting a built-in implicit mime-type
> attribute on them, which was used as the content-type with served over
> the built-in WebDAV extension for the DB. I wish PostgreSQL had
> something like that too.
>
> PPPPS: the lo extension is not viable for us, FWIW. All the above
> should be opt-in on bytea columns IMHO.
>

Does it have to match the md5 of the 'unchunked' variant exactly? If not,
maybe you can rig a custom aggregate that would just "hash amend" the
chunks using the n-1 chunk has as salt, this would be fast and easy, at the
cost of not matching the unchunked variant.

I had to do something very similar with compression, I had a need to be
able to compress bytea values with lz4 at SQL level due to limited support
for extensions in the cloud.  it works great...if a bit slow, and requires
the database to handle the decompression.

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jul 10, 2025 at 5:48=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">We store scientific infor=
mation in PostgreSQL, and some of that is<br>
bytea and large, thus we must &quot;chunk it&quot; both for performance, an=
d not<br>
be limited to 1GB (we do exceed that, in rare occasions).<br></blockquote><=
div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
Recently I added md5/sha1 hashing support for such values (for various<br>
reasons, to track corruptions in our ETLs, now fixed, but also in the<br>
future for custom smart sync and such), and was surprised to discover<br>
there are no aggregate versions of those functions, neither the<br>
built-in md5 one (now with bytea overload), nor for pgcrypto. Did I<br>
miss something?<br>
<br>
Any chance this might be added in the future?<br>
<br>
Seems so logical to me, that these hashing functions were available<br>
are aggregates, I can&#39;t be the first one to think of that, can it?<br>
<br>
Thanks, --DD<br>
<br>
PS: The holly-grail IMHO, would be built-in support for hashing, with<br>
intelligent lazy-compute and persistence correctly reset on changes.<br>
Probably opt-in.<br>
<br>
PPS: Built-in Support for xxh64, or at least in official pgcrypto,<br>
would also be nice. With aggregate!<br>
<br>
PPPS: I remember Oracle LOBs supporting a built-in implicit mime-type<br>
attribute on them, which was used as the content-type with served over<br>
the built-in WebDAV extension for the DB. I wish PostgreSQL had<br>
something like that too.<br>
<br>
PPPPS: the lo extension is not viable for us, FWIW. All the above<br>
should be opt-in on bytea columns IMHO.<br></blockquote><div><br></div><div=
>Does it have to match the md5 of the &#39;unchunked&#39; variant exactly? =
If not, maybe you can rig a custom aggregate that would just &quot;hash ame=
nd&quot; the chunks using the n-1 chunk has as salt, this would be fast and=
 easy, at the cost of not matching the unchunked variant.</div><div><br></d=
iv><div>I had to do something very similar with compression, I had a need t=
o be able to compress bytea values with lz4 at SQL level due to limited sup=
port for extensions in the cloud.=C2=A0 it works great...if a bit slow, and=
 requires the database to handle the decompression.</div><div><br></div><di=
v>merlin</div><div>=C2=A0</div></div></div>

--00000000000054ebaa063997d883--