From: | Marko Kreen <markokr(at)gmail(dot)com> |
---|---|
To: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: MD5 aggregate |
Date: | 2013-06-14 12:00:29 |
Message-ID: | CACMqXCKZJmPPsFm2G8wp-0BZ93ua06uz9m+1txMHrM1yEcmSZw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Jun 13, 2013 at 12:35 PM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> 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.
It's more efficient to calculate per-row md5, and then sum() them.
This avoids the need for ORDER BY.
--
marko
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2013-06-14 12:03:47 | Issue with PGC_BACKEND parameters |
Previous Message | Greg Stark | 2013-06-14 11:29:59 | Re: Patch for fail-back without fresh backup |