Как я могу получить хэш всей таблицы в postgresql?

Ответ 1

просто сделайте так, чтобы создать функцию агрегации хеш-таблицы.

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

Ответ 2

Я знаю, что это старый вопрос, однако это мое решение:

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; 

Ответ 3

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

Ответ 4

Что касается алгоритма, вы можете XOR все отдельные хеши MD5 или объединить их и хеш-конкатенацию.

Если вы хотите сделать это полностью на стороне сервера, вы, вероятно, должны создать свою собственную функцию агрегации, которую вы могли бы вызвать.

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

В качестве промежуточного шага вместо копирования всей таблицы клиенту вы можете просто выбрать результаты MD5 для всех строк и запустить их через md5sum.

В любом случае вам нужно установить фиксированный порядок сортировки, иначе вы можете получить разные контрольные суммы даже для тех же данных.

Ответ 5

У меня было аналогичное требование, которое можно было использовать при тестировании решения для репликации специализированных таблиц.

@Революционное решение 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;

Для полноты (в случае, если последующее редактирование должно удалить его), вот определение zz_hashagg из вопроса @Ben:

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 = '');

Ответ 6

Отличные ответы.

В случае, если кто-то не хочет использовать функции агрегации, но поддерживает поддержку таблиц размером в несколько GiB, вы можете использовать это, у которого мало шансов на производительность при наилучших ответах в случае больших таблиц.

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 
      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'
);