如何在PostgreSQL中获取整个表的哈希值？

Question

我希望有一种相对高效的方法来将整个表压缩成哈希值。

我有一些生成整个数据表的工具，可以用来生成更多的表，依此类推。我正在尝试实现一个简单的构建系统来协调构建运行并避免重复工作。我想记录输入表的哈希值，以便稍后检查它们是否发生了变化。构建表需要几分钟或几小时，因此花费几秒钟构建哈希是可以接受的。

我使用的一个技巧是将pg_dump的输出直接传输到md5sum中，但这需要将整个表转储通过网络传输到本地计算机上进行哈希处理。理想情况下，我希望在数据库服务器上生成哈希值。 在postgresql中找到行的哈希值为我提供了一种逐行计算哈希值的方法，然后可以以某种方式组合起来。

任何提示都将不胜感激。 编辑发布我最终得到的内容： tinychen的答案对我没有直接有效，因为我显然不能使用'plpgsql'。当我改用SQL实现函数时，它有效，但对于大型表来说非常低效。因此，我不再将所有行哈希连接起来，然后对其进行哈希，而是改为使用“滚动哈希”，其中先前的哈希与行的文本表示连接，然后对其进行哈希以生成下一个哈希。这样做效果更好；显然，在数百万次的短字符串上运行md5比连接数百万次的短字符串要好。

create function zz_concat(text, text) returns text as 
    'select md5($1 || $2);' language 'sql';

create aggregate zz_hashagg(text) (
    sfunc = zz_concat,
    stype = text,
    initcond = '');

- Ben

---

我不知道有任何方法可以做到这一点。我的第一反应是记录表的创建并比较时间戳。 - mikerobi

我猜你不能直接在服务器上运行pg_dump命令，对吗？ - Joey Adams

@Joey：+1。非常实用，可能是最快的。请把它作为答案。 - Thilo

（诚然，您需要手动复制输出，但看起来它比当前的解决方案稍微好一些。） - cwallenpoole

这真的帮了我很多。你需要使用上面的函数的最终SQL是：select md5(zz_hashagg(md5(CAST((example.*)AS text)))) from example。 - subelsky

Answer 1

我知道这是一个老问题，不过这是我的解决方案：

SELECT        
    md5(CAST((array_agg(f.* order by id))AS text)) /* id is a primary key of table (to avoid random sorting) */
FROM
    foo f; 

- Tomas Greif

---

这是我看到的最简单的解决方案，对我很有效。感谢Tomas! - infiniteloop

Answer 2

SELECT md5(array_agg(md5((t.*)::varchar))::varchar)
  FROM (
        SELECT *
          FROM my_table
         ORDER BY 1
       ) AS t

- nick_olya

Answer 3

只需要按照以下步骤创建哈希表聚合函数。

create function pg_concat( text, text ) returns text as '
begin
    if $1 isnull then
        return $2;
    else
       return $1 || $2;
    end if;
end;' language 'plpgsql';

create function pg_concat_fin(text) returns text as '
begin
    return $1;
end;' language 'plpgsql';

create aggregate pg_concat (
    basetype = text,
    sfunc = pg_concat,
    stype = text,
    finalfunc = pg_concat_fin);

然后您可以使用pg_concat函数来计算表的哈希值。

select md5(pg_concat(md5(CAST((f.*)AS text)))) from f order by id

- tinychen

---

不得不调整(postgres 11.7)以适应select md5(pg_concat(md5(CAST((f.*)AS text))order by id)) from f。此外，对于我的大表，@harmic/@Ben的解决方案运行1.5分钟，而这个解决方案在45分钟后仍在运行。 - DrD

Answer 4

我有一个类似的需求，需要在测试专业表复制解决方案时使用。

@Ben提供的滚动MD5解决方案（他附加在问题后面）似乎非常高效，但有一些陷阱使我失误了。

第一个陷阱（在其他答案中提到）是您需要确保聚合在您正在检查的表上以已知的顺序执行。其语法示例如下：

select zz_hashagg(CAST((example.*)AS text) order by id) from example;

注意order by在聚合函数内部。

其次，使用CAST((example.*)AS text)将不会为两个具有相同列内容的表提供相同的结果，除非这些列按相同顺序创建。在我的情况下，这并不保证，所以为了获得真正的比较，我必须单独列出这些列，例如:

select zz_hashagg(CAST((example.id, example.a, example.c)AS text) order by id) from example;

为了完整性（以防后续编辑删除它），这里是 @Ben 的问题中 zz_hashagg 的定义：

create function zz_concat(text, text) returns text as 
    'select md5($1 || $2);' language 'sql';

create aggregate zz_hashagg(text) (
    sfunc = zz_concat,
    stype = text,
    initcond = '');

- harmic

Answer 5

Tomas Greif的solution很好。但是对于足够大的表格，将会出现无效内存分配请求大小错误。因此，有两个选项可以克服这个问题。

选项1.不使用批处理

如果表格不够大，请使用string_agg和bytea数据类型。

select
    md5(string_agg(c.row_hash, '' order by c.row_hash)) table_hash
from
    foo f
    cross join lateral(select ('\x' || md5(f::text))::bytea row_hash) c
;

选项2：使用批处理

如果上一个选项中的查询以以下错误结束：

SQL Error [54000]: ERROR: out of memory Detail: Cannot enlarge string buffer containing 1073741808 bytes by 16 more bytes.

则行数限制为1073741808 / 16 = 67108863，并且应将表分成批次。

select
    md5(string_agg(t.batch_hash, '' order by t.batch_hash)) table_hash
from(
    select
        md5(string_agg(c.row_hash, '' order by c.row_hash)) batch_hash
    from
        foo f
        cross join lateral(select ('\x' || md5(f::text))::bytea row_hash) c
    group by substring(row_hash for 3)
    ) t
;

在“group by”子句中，数字3将行散列分成16777216个批次（2：65536，1：256）。此外，其他分批方法（如严格的“ntile”）也可以运行。

附言：如果您需要比较两个表格，请参考this post。

- iliyesku

Answer 6

很好的答案。

如果有人不想使用聚合函数但需要支持几个GB大小的表格，可以使用这个方法，它在处理最大表格时与最佳答案相比只有轻微的性能损失。

CREATE OR REPLACE FUNCTION table_md5(
  table_name CHARACTER VARYING
  , VARIADIC order_key_columns CHARACTER VARYING [])
RETURNS CHARACTER VARYING AS $$
DECLARE
  order_key_columns_list CHARACTER VARYING;
  query CHARACTER VARYING;
  first BOOLEAN;
  i SMALLINT;
  working_cursor REFCURSOR;
  working_row_md5 CHARACTER VARYING;
  partial_md5_so_far CHARACTER VARYING;
BEGIN
  order_key_columns_list := '';

  first := TRUE;
  FOR i IN 1..array_length(order_key_columns, 1) LOOP
    IF first THEN
      first := FALSE;
    ELSE
      order_key_columns_list := order_key_columns_list || ', ';
    END IF;
    order_key_columns_list := order_key_columns_list || order_key_columns[i];
  END LOOP;

  query := (
    'SELECT ' ||
      'md5(CAST(t.* AS TEXT)) ' ||
    'FROM (' ||
      'SELECT * FROM ' || table_name || ' ' ||
      'ORDER BY ' || order_key_columns_list ||
    ') t');

  OPEN working_cursor FOR EXECUTE (query);
  -- RAISE NOTICE 'opened cursor for query: ''%''', query;

  first := TRUE;
  LOOP
    FETCH working_cursor INTO working_row_md5;
    EXIT WHEN NOT FOUND;
    IF first THEN
      first := FALSE;
      SELECT working_row_md5 INTO partial_md5_so_far;
    ELSE 
      SELECT md5(working_row_md5 || partial_md5_so_far)
      INTO partial_md5_so_far;
    END IF;
    -- RAISE NOTICE 'partial md5 so far: %', partial_md5_so_far;
  END LOOP;

  -- RAISE NOTICE 'final md5: %', partial_md5_so_far;
  RETURN partial_md5_so_far :: CHARACTER VARYING;
END;
$$ LANGUAGE plpgsql;

用途:

SELECT table_md5(
  'table_name', 'sorting_col_0', 'sorting_col_1', ..., 'sorting_col_n'
);

- 1737973

Answer 7

关于算法，您可以对所有单独的MD5哈希值执行XOR操作，或者将它们连接起来并哈希连接。

如果您想完全在服务器端执行此操作，可能需要创建自己的聚合函数，然后调用它。

select my_table_hash(md5(CAST((f.*)AS text)) from f order by id 

作为一个中间步骤，您可以选择仅针对所有行选择MD5结果并将其通过md5sum运行，而不是将整个表复制到客户端。

无论哪种方式，您都需要建立一个固定的排序顺序，否则即使是相同的数据，您也可能会得到不同的校验和。

- Thilo

---

你需要建立一个固定的排序顺序，这样如果你想要重新计算哈希值就可以了。对于异或运算来说，这是不必要的。这让我觉得异或可能并不是一个好主意。 - Thilo

你是对的；XOR聚合哈希意味着如果你有两行相同的数据，并且它们都以相同的方式更改，最终的哈希值将与原始值相同。相同的数据行可能不应该存在，但我敢打赌，XOR还有其他增加碰撞几率的属性。 - Ben

谢谢你的指引，我会研究一下如何实现。不幸的是，我使用了很多不同的数据库（而且新的数据库也在不断创建），所以我必须将聚合函数的创建脚本作为构建系统的一部分来编写。如果没有其他更好的解决方案，我会回来接受这个答案的。 - Ben