From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | MD5 aggregate |
Date: | 2013-06-13 09:35:43 |
Message-ID: | CAEZATCUq--+zYAQxYfyO+3oPjCY6DWS5scET8PPHA7mhn7WDxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
Attached is a patch implementing a new aggregate function md5_agg() to
compute the aggregate MD5 sum across a number of rows. This is
something I've wished for a number of times. I think the primary use
case is to do a quick check that 2 tables, possibly on different
servers, contain the same data, using a query like
SELECT md5_agg(foo.*::text) FROM (SELECT * FROM foo ORDER BY id) foo;
or
SELECT md5_agg(foo.*::text ORDER BY id) FROM foo;
these would be equivalent to
SELECT md5(string_agg(foo.*::text, '' ORDER BY id)) FROM foo;
but without the excessive memory consumption for the intermediate
concatenated string, and the resulting 1GB table size limit.
I've added 2 variants: md5_agg(text) and md5_agg(bytea) to match the 2
variants of md5(), so pure binary data can also be checksummed.
In passing, I've tidied up and optimised the code in md5.c a bit ---
specifically I've removed the malloc()/memcpy()/free() code that was
unnecessarily making a copy of the entire input data just to pad it
and append the bit count. This reduces the memory consumption of the
existing md5() functions for large inputs, and gives a modest
performance boost. As a result, the md5() function can no longer throw
an out-of-memory error.
Regards,
Dean
Attachment | Content-Type | Size |
---|---|---|
md5_agg.patch | application/octet-stream | 29.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Ants Aasma | 2013-06-13 10:12:54 | Re: Parallell Optimizer |
Previous Message | Dean Rasheed | 2013-06-13 08:46:16 | Re: WITH CHECK OPTION for auto-updatable views |