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

Вначале надо разобраться, как считать хеш-сумму для одной строки. В PostgreSQL есть функция md5(), которая возвращает хеш значения в виде текста. Получить хеши для всех строк можно командой:

SELECT md5(id || title || usb_data || is_delete) FROM our_table;

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

Этот код можно изменить, чтобы хеши строк возвращались не в виде текста, а в виде байтовых масивов:

SELECT md5(id || title || usb_data || is_delete)::bytea FROM our_table;

Теперь надо решить две задачи.

Первая - надо сделать так, чтобы пользователь не перечислял все столбцы в таблице, и это делалось автоматически.

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

Для первой задачи подойдет такая функция:

CREATE OR REPLACE FUNCTION get_rows_hash(our_table_name TEXT)

RETURNS TABLE(hash BYTEA) AS $$

DECLARE

col_list TEXT;

query_text TEXT;

BEGIN

-- Генерируем список колонок, кроме служебных (например, OID)

SELECT string_agg(quote_ident(column_name), ' || ')

INTO col_list

FROM information_schema.columns

WHERE table_schema = 'public' AND table_name = our_table_name;

-- Если в таблице нет колонок, вернуть NULL

IF col_list IS NULL THEN

RETURN;

END IF;

query_text := format('SELECT md5(%s)::bytea FROM %I', col_list, our_table_name);

RETURN QUERY EXECUTE query_text;

END $$ LANGUAGE plpgsql;

SELECT get_rows_hash('our_table');

Далее, чтобы можно было применять XOR-функцию к хешам, надо, чтобы было установлено расширение intagg. Вначале ставится пакет postgresql-contrib-9.6 или типа такого, а потом выполняется SQL-команда о postgres-суперпользователя:

CREATE EXTENSION IF NOT EXISTS "intagg";

----- 8< -----

Далее необработанный текст:

Теперь создадим функцию, которая будет принимать набор хешей, полученных в результате выполнения вашего запроса, и возвращать итоговый хэш, полученный путем применения операции XOR ко всем входящим хешам.

CREATE OR REPLACE FUNCTION aggregate_hashes(hash_values bytea[])

RETURNS bytea

LANGUAGE plpgsql

AS

$$

DECLARE

result_bytea bytea;

BEGIN

-- Приведение массива хешей к типу integer[]

WITH hashes_int AS (

SELECT bitwise_xor_agg(encode(hv, 'hex')::bigint) as xor_result

FROM unnest(hash_values) hv

)

-- Получение результата и приведение обратно к типу bytea

SELECT decode(to_hex(xor_result), 'hex')

INTO result_bytea

FROM hashes_int;

RETURN result_bytea;

END;

$$;

Пояснения к функции:

• Функция принимает массив hash_values типа bytea[], содержащий хеши.
• Внутри функции используется конструкция WITH для выполнения агрегации с помощью функции bitwise_xor_agg.
• Функция unnest разбивает массив на отдельные элементы.
• Хеши сначала конвертируются в тип bigint с помощью функции encode(hv, 'hex')::bigint.
• После выполнения операции XOR результат снова конвертируется в тип bytea с помощью функций to_hex и decode.

Использование функции

Теперь можно использовать эту функцию следующим образом:

WITH hashed_rows AS (

SELECT md5(id || name || is_delete)::bytea AS hash_value

FROM our_table

)

SELECT aggregate_hashes(array_agg(hash_value)) AS final_hash

FROM hashed_rows;

Еще пример:

CREATE TABLE test_xor (

id SERIAL PRIMARY KEY,

data1 BYTEA,

data2 BYTEA

);

INSERT INTO test_xor (data1, data2) VALUES ('\x01020304', '\x05060708');

-- Выполнение XOR между двумя столбцами

SELECT id, data1 # data2 AS xor_result

FROM test_xor;

Агрегатная функция. Обработка нескольких значений.

Если вам нужно выполнить XOR для нескольких значений в одном запросе, можно использовать агрегатную функцию bitwise_xor_agg, которая доступна после установки расширения intagg:

CREATE EXTENSION intagg;

SELECT bitwise_xor_agg(data1::bit(32))

FROM test_xor;

Это позволит выполнить побитовое XOR для всех значений в столбце data1 и вернуть итоговый результат.

----- 8< -----

Вычисление хеша можно сделать командой:

SELECT xor_agg(digest(id || column1 || column2, 'sha256')) FROM my_table;

Здесь через функцию digest() вычисляется хеш строки в виде SHA256, а через функцию xor_agg() все хеши строк "ксорятся" в одно число. Это итоговое число и является хешем талицы.

Особенность данного решения в том, что функция digest() не существует в стандарной поставке PostgreSQL. Необходимо, чтобы было установлено расширение pgcrypto. В Astra Linux 1.6 это расширение лежит в пакете postgresql-contrib-9.6.

И вторая главная особенность - функции xor_agg() нет как понятия. Ее нужно придумать.

Если нужно автоматически использовать все столбцы в таблице, можно применить следующий код:

DO $$

DECLARE

col_list TEXT;

BEGIN

-- Получаем список всех колонок в виде строки: "col1 || col2 || col3"

SELECT string_agg(column_name, ' || ') INTO col_list

FROM information_schema.columns

WHERE table_name = 'my_table';

-- Динамически выполняем SQL с этим списком колонок

EXECUTE format(

'SELECT xor_agg(digest(%s, ''sha256'')) FROM my_table',

col_list

);

END $$;

Если нет возможности использовать функцию digest(), можно использолвать функцию md5(), у которой один параметр, без указания алгоритма хеширования.

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

CREATE OR REPLACE FUNCTION get_table_hash(our_table_name TEXT) RETURNS TEXT AS $$

DECLARE

col_list TEXT;

query TEXT;

result TEXT;

BEGIN

-- Генерируем список колонок, кроме служебных (например, OID)

SELECT string_agg(quote_ident(column_name), ' || ')

INTO col_list

FROM information_schema.columns

WHERE table_schema = 'public' AND table_name = our_table_name;

-- Если в таблице нет колонок, вернуть NULL

IF col_list IS NULL THEN

RETURN NULL;

END IF;

-- Формируем SQL-запрос

query := format('SELECT xor_agg(md5(%s)) FROM %I', col_list, our_table_name);

-- Выполняем запрос и получаем хеш

EXECUTE query INTO result;

RETURN result;

END $$ LANGUAGE plpgsql;

Эта функция вызывается так:

SELECT get_table_hash('my_table');

Переработать, дописать.