public inbox for [email protected]
help / color / mirror / Atom feedAggregate versions of hashing functions (md5, sha1, etc...)
2+ messages / 2 participants
[nested] [flat]
* Aggregate versions of hashing functions (md5, sha1, etc...)
@ 2025-07-10 11:48 Dominique Devienne <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Dominique Devienne @ 2025-07-10 11:48 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Aggregate versions of hashing functions (md5, sha1, etc...)
@ 2025-07-10 19:02 Merlin Moncure <[email protected]>
parent: Dominique Devienne <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Merlin Moncure @ 2025-07-10 19:02 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; +Cc: [email protected]
On Thu, Jul 10, 2025 at 5:48 AM Dominique Devienne <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-07-10 19:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-10 11:48 Aggregate versions of hashing functions (md5, sha1, etc...) Dominique Devienne <[email protected]>
2025-07-10 19:02 ` Merlin Moncure <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox